WITH Clause in SQL
FREE
Overview
Oracle introduced the SQL WITH Clause in the Oracle 9i release 2 database. It is now available in other major RDBMS. SQL WITH Clause is also known as Common Table Expressions (CTEs) or Subquery Refactoring as it refactors and improves the speed of complex subqueries. It allows you to give a subquery block a name, and this is called Subquery Refactoring.
SQL WITH Clause is used to create a temporary data set using a simple SQL Query, and this temporary dataset can be referenced in subsequent queries. This can be used within UPDATE, SELECT, INSERT, etc. statements.
What is the WITH Clause in SQL?
The WITH Clause in SQL, also known as CTE (Common Table Expression) or Subquery Refactoring, defines a temporary dataset, and the output of this dataset is available in several places within the main SQL Query. The SQL WITH Clause is considered temporary because the result is not permanently stored anywhere in the database schema. It is considered a temporary view because that is available only during the execution scope of INSERT, SELECT, UPDATE, etc. statements. The WITH Clause in SQL is only valid in the query to which it belongs.
How does the WITH Clause in SQL work?
The WITH Clause in SQL allows you to give a subquery block a name, and this acts as a table and can be used in multiple subqueries within the INSERT, DELETE, and SELECT statements.
When you need the same set of results within the SQL statements, then WITH Clause is very much useful. In such cases, you can define a CTE, i.e. Common Table Expression for this data, which can be reused repeatedly by referencing it by its name.
To understand more clearly, go ahead with the examples section wherein the WITH Clause in SQL is explained using the example in which you have to find all the teachers whose salary is more than the average salary of all the teachers.
Syntax of WITH Clause in SQL
The basic syntax of the WITH Clause in SQL is given below:
As shown in the above syntax, the usage of the WITH Clause is similar to creating tables. While creating a table, you give it a name; similarly, when you use WITH Clause, you must provide a name and this name essentially acts as a table name in SQL.
Parameter of WITH Clause in SQL
Let's consider the syntax of the WITH Clause in SQL with the parameters and see what those parameters mean in the SQL.
As you can see, there are various parameters mentioned in the syntax, and they are mentioned below:
| Parameters | Explanation |
|---|---|
| WITH | The WITH Clause is used for creating the common table expression or the temporary tables. |
| table_name | Here, the table_name is the name of the Common Table Expression i.e. CTE, and (column_name_1, column_name_2, ...) defines the column names of the CTE which is also used further in the subsequent queries. |
| AS (SELECT column_name_1, column_name_2 FROM new_table_name WHERE condition) | As you can see, this section in the above query is a SELECT statement, and the result set of this statement populates the CTE. |
| SELECT column_name_1, column_name_2 FROM table_name | This SELECT statement is the outer query, and it uses the columns from the resultant CTE that produces the final result. |
The above-given parameters are mandatory, and you can also use GROUP BY, ORDER BY, and HAVING clauses according to your requirement.
Examples of WITH Clause in SQL
Example:
Consider an example to understand the WITH Clause in SQL. Let's take a table of teachers, and you have to find all the teachers whose salary is more than the average salary of all the teachers.
Teachers
| teacher_id | teacher_name | teacher_subject | teacher_salary |
|---|---|---|---|
| 101 | Claira Jackson | Science | 35,000 |
| 102 | Paul Jack | Socials | 30,000 |
| 103 | Alex Peterson | Maths | 35,000 |
| 104 | Kreena Patel | Chemistry | 30,000 |
| 105 | Mitchell Lace | Maths | 40,000 |
The below SQL query is used to find the teachers whose salary is more than the average salary of all the teachers.
SQL:
Output:
As shown in the above example, the WITH statement is used, and the average salary of the teacher's table is fetched out and stored in the CTE or the temporary table, i.e. temp_table.
In the second part of the query, the result set of the temp_table is used to find out if the teacher's salary is greater than the average salary of the teachers.
Example:
Consider another example to understand WITH Clause in SQL. In this example, the below-given patient's table is used, and the objective in this example is to count the total number of patients of each doctor.
Patients
| patient_id | patient_name | gender | age | disease | doctor_id |
|---|---|---|---|---|---|
| 102 | Vaishali Sharan | F | 34 | Diabetes | 60 |
| 104 | Subodh Jatav | M | 39 | Heart Patient | 61 |
| 105 | Kapila Patel | F | 28 | Dyslexia | 62 |
| 108 | Monica Yadav | F | 30 | Cancer | 60 |
| 111 | Neelam Patel | F | 31 | Infection | 62 |
The below SQL query is used to find out each doctor's total number of patients.
SQL:
The output of the above SQL query is:
Output:
In the above SQL Query, the WITH statement is used in the first part of the query. This fetches the records of all the patients from the Patients' table. Also, a condition is checked that the doctor_id should not be NULL. The final result set is stored in the temporary table or CTE, namely temp_patient_table.
The second part of the query, i.e. the outer query, uses the result set of the SQL WITH statement, temp_patient_table, to count the total number of patients of each doctor.
Conclusion
- The SQL WITH Clause is used with Complex SQL Queries and not with the Simpler Queries.
- The SQL WITH Clause is mainly used to provide a subquery block a name that can be referenced in several places within the main SQL query i.e. SQL WITH Clause is generally used for creating temporary tables that can be used further in the main SQL query.
- WITH Clause in SQL helps reusing the code repeatedly by referencing.
- The SQL WITH Clause is used to create temporary tables, which are further used in the main SQL queries.
- The SQL WITH Clause is used to remove the complexity of reading and debugging nesting SQL subqueries.
- The usage of WITH Clause is similar to creating tables. You give a name to the table. Similarly, a name is provided when you use WITH Clause.
- The object associated with the WITH statement disappears after the main SQL statement is executed because the WITH does not create the table.