Home » SQL

SQL WITH Clause

SQL | WITH Clause: In this tutorial, we are going to learn about the WITH Clause with its usages, syntax and query examples in SQL (Structured Query Language).
Submitted by Abhishek Goel, on April 05, 2020

SQL | WITH Clause

The SQL WITH statement was presented by Oracle in the Oracle 9i discharge 2 database. The SQL WITH provision permits you to give a sub-question a name (a procedure additionally called sub-inquiry refactoring), which can be referenced in a few places inside the principle SQL inquiry.

• The statement is utilized for characterizing a transitory connection to such an extent that the yield of this impermanent connection is accessible and is utilized by the question that is related to the WITH provision.
• Questions that have a related WITH provision can likewise be composed utilizing settled sub-inquiries yet doing so adds greater unpredictability to peruse/investigate the SQL inquiry.
• WITH condition isn't upheld by all database framework.
• The name doled out to the sub-inquiry is treated just as it was an inline view or table
• The SQL WITH condition was presented by Oracle in the Oracle 9i discharge 2 database.

Syntax:

    WITH <tablename>(attribute) AS
    ( 	
	    SELECT <columnname(attribute)>
	    FROM <tablename>
	    SELECT <attribute>
	    FROM <tablename>
	    WHERE <condition>;
    )

NOTE: At the point when a question with a WITH condition is executed, first the inquiry referenced inside the proviso is assessed and the yield of this assessment is put away in an impermanent connection. Following this, the principle question related to the WITH provision is at last executed that would utilize the brief connection delivered.

Let's understand this concept with the help of examples:

Example1: Find all the students who have marks more than the marks of total students.

Solution: Name of table: Student

RollNo	Name	Marks
201	Aniket Rana	65
202	Aarushi Mehta	60
203	Pankaj Sinha	72
204	Hardik Sharma	79
205	Avi Singh	82

Query:

WITH temporaryTable(averagevalue) AS
(SELECT avg(marks)FROM Student),
SELECT rollno, name, marks 
FROM Student, temporaryTable 
WHERE Student.marks> temporaryTable.averagevalue;

Output:

RollNo	Name	Marks
203	Pankaj Sinha	72
204	Hardik Sharma	79
205	Avi Singh	82

The average of marks of students comes out to be 71.6. Therefore, marks greater than 71.6 are displayed in the output.

Example 2: Find all restaurants where the total salary of all chefs in that restaurant is more than the average salary of all chefs in the database.

Solution: Name of Table: Chefs

ChefCode	Hotel	ChefName	Salary
123	The Lalit	Aditya	83250
234	Shangri La's	Vikas	125000
345	The Lalit	Sanjeev	264000
567	The Lalit	Ranveer	120000
545	Shangri La's	Kunal	250000

Query:

WITH totalSalary(Hotel, total) AS
(
SELECT Hotel, sum(salary)
FROM Chefs
GROUP BY Hotel
),
hotelaverage(avgsalary) AS 
(
SELECT avg(salary)
FROM Chefs
)
SELECT Hotel
FROM totalsalary, hotelaverage
WHERE totalsalary.total > hotelaverage.avgsalary;

Output:

Hotel
Shangri La's
The Lalit

Points to Remember:

• The SQL WITH proviso is acceptable when utilized with complex SQL articulations instead of basic ones.
• It likewise permits you to separate complex SQL inquiries into littler ones which make it simple for troubleshooting and preparing the mind-boggling questions.
• The SQL WITH proviso is fundamentally a drop-in substitution to the typical sub-inquiry.