SQL WITH Clause Explained [Practical Examples]

Overview of SQL WITH Clause

The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 databases in standard SQL to simplify complex long queries of joins and subqueries, The SQL WITH clause allows you to give the name to the output of referenced in subsequent queries, this process is interchangeably called as common table expression (CTE) or sub-query refactoring, which can be referenced in several places within the main SQL query.

The SQL WITH clause provides a way of defining a temporary relation whose definition is available only to the query in which the WITH clause occurs, SQL WITH clauses is individually-evaluated SELECT statements for use in a larger container query.

Using SQL WITH clauses we can simplify complicated queries and reduce statement repetition

• The SQL WITH clause is reference is considered as the temporary because the result is not permanently stored in the database system,
• The SQL WITH dataset acts as the temporary view or table that is only available till the duration of the scope of execution of SELECT, INSERT, UPDATE, DELETE, or MERGE statements,

• It is used to remove the complexity of reading and debug of Nesting SQL sub-queries
• It is also useful for recursive queries

• It is not supported by all database management system
• It is very helpful when you need the same set of results data multiple times
• SQL WITH not a stand-alone command like create view is: it must be followed by select. This query (and subqueries it contains) can refer to the just defined query name in them from

 

SQL WITH Syntax

WITH <temp_table_CTE_name1> (column_1, column_2,…,column_n)
AS (
     SELECT ... (CTE quey 1)
     )
   , [<temp_table_CTE_name2> (column_1, column_2,…,column_n)
AS (
     SELECT ...
       FROM temp_table_expression_name1 (CTE quey 2)
        ...
     )]

--Outer Main query with temporary table CTE
SELECT expression_A, expression_B, ...
FROM temp_table_expression_name

• temp_table_CTE_name1(column_1, …, column_n): The name of the virtual temporary data set which will be used in the main query, and column_1 to column_n are the column names that can be used in subsequent query steps.
• AS (….): This section defines the SELECT query that will populate the CTE
• SELECT expression_A, expression_B FROM expression_name: This section specifies the main outer query where the SELECT statement (or INSERT, UPDATE, DELETE, or MERGE statements) is used on one or more of the generated CTEs to subsequently output the intended result

 

How SQL WITH Clause works?

• When a query with a SQL WITH clause is executed, first, the query mentioned within the WITH clause is evaluated and the output of this evaluation is stored within a temporary view/table,
• If the second CTE query is an optional part, we mentioned it will be executed with the first result set of expressions,
• Then, the main query associated with the WITH clause is finally executed using the temporary relation produced.
• A single SQL WITH clause can introduce multiple query names by separating them with a comma (the with keyword is not repeated). Each of these queries can refer to the query names previously defined within the same SQL WITH clause

 

SQL WITH Clause Examples

Consider the hospital database with four tables for performing practical examples
Patient Table

Doctor Table

Bill Table

Laboratory Table

 

Example-1

Simple query with one table patient to count patient of each doctor

WITH patient_CTE (patient_id, name, doctor_id) AS 
(SELECT patient_id, name, doctor_id FROM patient
WHERE (doctor_id IS NOT NULL))
SELECT doctor_id, COUNT(patient_id) AS Totalpatient FROM patient_CTE AS patient_CTE_1 GROUP BY doctor_id

• In above with query in SQL, the with statement is used in the first part of the query to fetch records of all patients from the patient table where doctor_id is not null, so the set of records are store in CTE named patient_CTE
• In the second part is the outer referencing query which is using the resulting set of SQL With statement patient_CTE to count total patient-doctor wise

OUTPUT:

 

Example-2

To find all patient details whose laboratory bill amount is more than the average bill amount of laboratory of all patients

WITH avg_CTE(averageValue) AS (SELECT AVG(amount) AS 'Average Amount'
FROM laboratory)
SELECT patient.patient_id, patient.name, patient.disease, laboratory_1.lab_no
FROM avg_CTE AS avg_CTE_1 
INNER JOIN  laboratory AS laboratory_1 ON avg_CTE_1.averageValue< laboratory_1.amount CROSS JOIN  patient
WHERE (laboratory_1.patient_id = laboratory_1.patient_id)

• In above with query in SQL, the query is divided into two parts, first is to make a dataset or temporary table using SQL WITH clause, find all average values of bill amount from laboratory table which will make the temporary table as avg_CTE
• And next query which is the main query used SQL WITH statement resulting table as the based table to retrieve patients who is having laboratory bill amount greater than the average amount

OUTPUT:

 

Example-3

To find the minimum bill amount of each patient who is admitted more than 2 days in the hospital and also not having NULL value in-room charges

WITH bill_CTE(bill_no, patient_id, doctor_charge, room_charge, no_of_days, billamount) 
AS (SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days, billamount FROM bill
WHERE (room_charge IS NOT NULL) AND (no_of_days> 2))
    
SELECT MIN(billamount) AS [minimum bill amount], patient_id
FROM bill_CTE AS bill_CTE_1
GROUP BY patient_id

• In the above SQL query of with clause, the temporary table of CTE generated using SQL WITH a clause to make a set of records from bill table with bill details of the patient where the patient is admitted for more than 2 days
• The outer referencing query is using bill_CTE table to find the minimum bill amount of each patient record from bill_CTE

OUTPUT:

 

Example 4

To fetch each patient details with minimum doctor charges and minimum room charges of the patient who are admitted more than 2 days in the hospital and also not having NULL value in-room charges field

WITH bill_CTE AS (SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days, billamount
FROM bill WHERE (room_charge IS NOT NULL) AND (no_of_days> 2))

SELECT MIN(doctor_charge) AS [minimum doctor charges], MIN(room_charge) AS [minimum room charges], patient_id FROM  bill_CTE AS bill_CTE_1
GROUP BY patient_id

• Here in above SQL WITH statement is used to make a temporary table with records from bill table having no_of_days more than 2 value, give name as bill_CTE to that temporary table
• After execution of SQL WITH clause, the bill_CTE table is used as base referential table for the main query and fetch minimum room and doctor charges for each patient record present in SQL WITH clause table bill_CTE

OUTPUT:

 

SQL WITH Clause with multiple CTE definitions in a single query

SQL WITH clause can be used to create more than on CTEs , that is more than on temporary tables, and that CTEs can be used in the outer main referential query to get resulting data from CTE tables, Notice that a comma is used to separate the CTE query definitions.

 

Example 5

To Display retrieve average bill amount and average doctor charges of each patient who is having bill amount of more than 200 and doctor charges not null, with patient name and id number

WITH bill_CTE AS (SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days, billamount
FROM bill
WHERE (doctor_charge IS NOT NULL) AND (billamount> 200)), patient_CTE AS
(SELECT patient_id, name FROM patient)
SELECT AVG(bill_CTE_1.doctor_charge) AS [Avarage doctor charges], AVG(bill_CTE_1.billamount) AS [Average Bill Amount ], bill_CTE_1.patient_id, patient_CTE_1.name
FROM  bill_CTE AS bill_CTE_1 LEFT OUTER JOIN patient_CTE AS patient_CTE_1 ON bill_CTE_1.patient_id = patient_CTE_1.patient_id
GROUP BY bill_CTE_1.patient_id, patient_CTE_1.name

• In the above SQL query of SQL WITH the statement, the main outer query is referring two CTE tables created using SQL WITH
• The first query fetches the data from the bill table where bill amount is more than 200 and doctor charges is not NULL
• The second CTE query retrieve patient details from the patient table
• The outer referential main query using both CTE tables of SQL WITH statements, to find average doctor charges of each patient and average bill amount paid by each patient

OUTPUT:

 

The Recursive SQL WITH clause

The Above examples are the using non-recursive SQL WITH clause, in recursive SQL WITH statement allow temporary table, CTEs to reference itself within same CTE, The Recursive SQL WITH clause is one of the advanced functionalities of SQL introduced by Oracle, make use of this recursive SQL WITH statement is to make traversal of hierarchical data structures easy and simple

 

Example 6

To Display retrieve average bill amount and average doctor charges of each patient who is having bill amount of more than 200 and doctor charges not null, with patient name and id number

WITH bill_CTE AS 
(SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days, billamount FROM  bill
WHERE(doctor_charge IS NOT NULL)
UNION ALL
SELECT bill_no, patient_id, doctor_charge, room_charge, no_of_days, billamount       
FROM bill_CTE AS bill_CTE_2), 
patient_CTE AS (SELECT patient_id, name FROM patient)
SELECT AVG(bill_CTE_1.doctor_charge) AS [Avarage doctor charges], AVG(bill_CTE_1.billamount) AS [Average Bill Amount ], bill_CTE_1.patient_id, patient_CTE_1.name
FROM bill_CTE AS bill_CTE_1 LEFT OUTER JOIN patient_CTE AS patient_CTE_1 
ON bill_CTE_1.patient_id = patient_CTE_1.patient_id
GROUP BY bill_CTE_1.patient_id, patient_CTE_1.name

• in the above SQL WITH clause query, bill_CTE is a temporary table that calls itself recursively to make the dataset of records from bill table where bill amount is more than 200, and doctor charge is not null, UNION ALL keyword is placed between to queries to make union operation, where the second query used bill_CTE as Datasource created using SQL WITH statement
• the outer main query is joining query of patient_CTE and bill_CTE to retrieve average doctor charges and average bill amount of each patient from joined record set of both patient_CTE and bill CTE

OUTPUT:

 

Summary

In this Article of SQL WITH Clause, the use of SQL WITH statement is explained in the first part of Article with some of the points to be noted while using SQL WITH clause is discussed, the syntax to write SQL WITH the statement and described each keyword use, then next part is covering various practical examples start with simple one table refereeing to the multiple tables and to crate multiple CTEs and make reference to the main outer query, in the end, the Recursive SQL WITH clause is explained

 

References

SQL GROUP BY
SQL UNION Operator
SQL INNER JOIN

 

Further Reading

SQL WITH common_table_expression