Writing long queries in SQL can be very complex especially when dealing with JOINS and subqueries. Then in the Oracle 9i, Oracle introduced SQL WITH Clause which improves the speed of complex subqueries. In this tutorial, we will learn about the SQL WITH clause with some examples for better understanding.
What is WITH Clause?
The WITH Clause in SQL or CTE (Common Table Expression) defines a dataset that is temporary and whose output is available in multiple places within the main SQL Query. The result is not stored permanently anywhere in the database schema that’s why it is considered temporary. It is termed a temporary view since it is only available during the execution of INSERT, SELECT, UPDATE, and other statements. The WITH Clause in SQL is only valid for the query to which it belongs.
Working of WITH Clause in SQL
The WITH Clause in SQL allows users to name a subquery block, which functions as a table and can be used in numerous subqueries within the SELECT, INSERT and DELETE statements. When you require the same set of results in many SQL statements, the WITH Clause is quite beneficial. In such cases, we can define a CTE (Common Table Expression) for this data, which can then be reused by referring to it by name.
Syntax
-- CTE
WITH cte_name(column01, column02, ...) AS (
SELECT column01, column02, ...
FROM table01
WHERE conditions
)
SELECT*
FROM cte_name;
WITH cte_name(column01, column02, ...) AS (
SELECT column01, column02, ...
FROM table01
WHERE conditions
)
-- Define the main query
SELECT *
FROM cte_name;Code language: SQL (Structured Query Language) (sql)where,
- WITH: Used for creating the temporary tables.
- cte_name: Name given to the CTE.
- (column01, column02, …): Columns returned by the CTE.
- AS: Used for defining the start of the CTE query definition.
- FROM table01: Table from which the data is selected.
- WHERE conditions: Optional conditions to filter the data.
- SELECT * FROM cte_name: The main query references that the CTE defined above.
Examples of WITH Clause in SQL
Example 1
To understand better, we are going to use the cricketers table given below:
Let’s find out all the cricketers whose reward is more than the average reward of all the cricketers in the cricketers table. The query will be:
With temp_table(average_reward)
AS
(
SELECT avg(reward) FROM cricketers
)
SELECT cricketer_id, name, skills, reward
FROM cricketers, temp_table
WHERE cricketers.reward > temp_table.average_reward;Code language: SQL (Structured Query Language) (sql)
From the above example, the WITH statement is used, and the average reward of the cricketers table is returned and stored in the CTE (temporary table).
Example 2
For this example, we are going to use the students table given below:
Let’s find out the total number of students of each teacher in the students table. The query will be:
WITH temp_students
AS
(
SELECT id, name, teacher_id
FROM students
WHERE (teacher_id IS NOT NULL)
)
SELECT teacher_id, COUNT(id)
FROM temp_students GROUP BY teacher_id;Code language: SQL (Structured Query Language) (sql)
From the above example, the WITH statement is used in the first part of the SQL query which returns the records of all students from the students table. The condition is checked to ensure that the teacher_id is not NULL. The final result set is then stored in CTE (temporary table). The second half of the query counts the total number of students of each teacher using the result set of the SQL WITH statement, temp_students.
Conclusion
In this tutorial, we have learned about basic syntax and examples of when and how we can define and use the WITH clauses also known as CTE in SQL. The WITH clause enhances the query modularity and readability. By using CTEs efficiently, SQL developers can write clearer and more maintainable code for manipulating data in the databases. CTE is one of the most asked topics in SQL interviews. We hope you enjoyed this tutorial.