(SQL Example for Citizen Data Scientist & Business Analyst)
SQL | WITH clause
The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query.
- The clause is used for defining a temporary relation such that the output of this temporary relation is available and is used by the query that is associated with the WITH clause.
- Queries that have an associated WITH clause can also be written using nested sub-queries but doing so add more complexity to read/debug the SQL query.
- WITH clause is not supported by all database system.
- The name assigned to the sub-query is treated as though it was an inline view or table
- The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database.
Syntax:
WITH temporaryTable (averageValue) as (SELECT avg(Attr1) FROM Table), SELECT Attr1 FROM Table WHERE Table.Attr1 > temporaryTable.averageValue;
In this query, WITH clause is used to define a temporary relation temporaryTable that has only 1 attribute averageValue. averageValue holds the average value of column Attr1 described in relation Table. The SELECT statement that follows the WITH clause will produce only those tuples where the value of Attr1 in relation Table is greater than the average value obtained from the WITH clause statement.
Note: When a query with a WITH clause is executed, first the query mentioned within the clause is evaluated and the output of this evaluation is stored in a temporary relation. Following this, the main query associated with the WITH clause is finally executed that would use the temporary relation produced.
Example 1: Find all the employee whose salary is more than the average salary of all employees.
Name of the relation: Employee
EMPLOYEEID | NAME | SALARY |
---|---|---|
100011 | Smith | 50000 |
100022 | Bill | 94000 |
100027 | Sam | 70550 |
100845 | Walden | 80000 |
115585 | Erik | 60000 |
1100070 | Kate | 69000 |
SQL Query:
WITH temporaryTable(averageValue) as (SELECT avg(Salary) from Employee), SELECT EmployeeID,Name, Salary FROM Employee, temporaryTable WHERE Employee.Salary > temporaryTable.averageValue;
Output:
EMPLOYEEID | NAME | SALARY |
---|---|---|
100022 | Bill | 94000 |
100845 | Walden | 80000 |
Explanation: The average salary of all employees is 70591. Therefore, all employees whose salary is more than the obtained average lies in the output relation.
Example 2: Find all the airlines where the total salary of all pilots in that airline is more than the average of total salary of all pilots in the database.
Name of the relation: Pilot
EMPLOYEEID | AIRLINE | NAME | SALARY |
---|---|---|---|
70007 | Airbus 380 | Kim | 60000 |
70002 | Boeing | Laura | 20000 |
10027 | Airbus 380 | Will | 80050 |
10778 | Airbus 380 | Warren | 80780 |
115585 | Boeing | Smith | 25000 |
114070 | Airbus 380 | Katy | 78000 |
SQL Query:
WITH totalSalary(Airline, total) as (SELECT Airline, sum(Salary) FROM Pilot GROUP BY Airline), airlineAverage(avgSalary) as (SELECT avg(Salary) FROM Pilot ) SELECT Airline FROM totalSalary, airlineAverage WHERE totalSalary.total > airlineAverage.avgSalary;
Output:
AIRLINE |
---|
Airbus 380 |
Explanation: The total salary of all pilots of Airbus 380 = 298,830 and that of Boeing = 45000. Average salary of all pilots in the table Pilot = 57305. Since only the total salary of all pilots of Airbus 380 is greater than the average salary obtained, so Airbus 380 lies in the output relation.
Important Points:
- The SQL WITH clause is good when used with complex SQL statements rather than simple ones
- It also allows you to break down complex SQL queries into smaller ones which make it easy for debugging and processing the complex queries.
- The SQL WITH clause is basically a drop-in replacement to the normal sub-query.
Learn to Code SQL Example – SQL | WITH clause
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.