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

RollNoNameMarks
201Aniket Rana65
202Aarushi Mehta60
203Pankaj Sinha72
204Hardik Sharma79
205Avi Singh82

Query:

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

Output:

RollNoNameMarks
203Pankaj Sinha72
204Hardik Sharma79
205Avi Singh82

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

ChefCodeHotelChefNameSalary
123The LalitAditya83250
234Shangri La'sVikas125000
345The LalitSanjeev 264000
567The LalitRanveer120000
545Shangri La'sKunal250000

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.





Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.





Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates


© https://www.includehelp.com some rights reserved.