The Wayback Machine - https://web.archive.org/web/20240901125849/https://www.geeksforgeeks.org/sql-with-clause/

SQL | WITH Clause

Last Updated : 05 Jul, 2024

The SQL WITH clause, also known as Common Table Expressions (CTEs), 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.

What is the SQL WITH Clause?

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 adds more complexity to read/debug the SQL query.
WITH clause is not supported by all database systems.
The name assigned to the sub-query is treated as though it were an inline view or table
The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database.

Note: Not all database systems support the WITH clause.

Syntax:

WITH temporaryTable (averageValue) AS (
    SELECT AVG (Attr1)
    FROM Table
)
    SELECT Attr1
    FROM Table, temporaryTable
    WHERE Table.Attr1 > temporaryTable.averageValue;

SQL WITH Clause

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.

SQL WITH Clause Examples

Let us look at some of the examples of WITH Clause in SQL:

Example 1: Finding Employees with Above-Average Salary

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: Finding Airlines with High Pilot Salaries

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 About SQL | WITH Clause

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.

The SQL WITH clause can significantly improve query performance by allowing the query optimizer to reuse the temporary result set, reducing the need to re-evaluate complex sub-queries multiple times.

M

Mayank Kumar

News

Improve

Previous Article

SQL | WHERE Clause

SQL HAVING Clause with Examples

Similar Reads

Difference between Having clause and Group by clause

1. Having Clause : Having Clause is basically like the aggregate function with the GROUP BY clause. The HAVING clause is used instead of WHERE with aggregate functions. While the GROUP BY Clause groups rows that have the same values into summary rows. The having clause is used with the where clause in order to find rows with certain conditions. The

SQL | Intersect & Except clause

1. INTERSECT clause : As the name suggests, the intersect clause is used to provide the result of the intersection of two select statements. This implies the result contains all the rows which are common to both the SELECT statements. Syntax : SELECT column-1, column-2 …… FROM table 1 WHERE….. INTERSECT SELECT column-1, column-2 …… FROM table 2 WHE

SQL | USING Clause

If several columns have the same names but the datatypes do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an EQUIJOIN. USING Clause is used to match only one column when more than one column matches. NATURAL JOIN and USING Clause are mutually exclusive. It should not have a q

SQL | With Ties Clause

This post is a continuation of SQL Offset-Fetch Clause Now, we understand that how to use the Fetch Clause in Oracle Database, along with the Specified Offset and we also understand that Fetch clause is the newly added clause in the Oracle Database 12c or it is the new feature added in the Oracle database 12c. Now consider the below example: Suppos

SQL | Sub queries in From Clause

From clause can be used to specify a sub-query expression in SQL. The relation produced by the sub-query is then used as a new relation on which the outer query is applied. Sub queries in the from clause are supported by most of the SQL implementations.The correlation variables from the relations in from clause cannot be used in the sub-queries in

SQL | ON Clause

The join condition for the natural join is basically an EQUIJOIN of all columns with same name. To specify arbitrary conditions or specify columns to join, the ON Clause is used. The join condition is separated from other search conditions. The ON Clause makes code easy to understand. ON Clause can be used to join columns that have different names.

Combining aggregate and non-aggregate values in SQL using Joins and Over clause

Prerequisite - Aggregate functions in SQL, Joins in SQLAggregate functions perform a calculation on a set of values and return a single value. Now, consider an employee table EMP and a department table DEPT with following structure:Table - EMPLOYEE TABLE NameNullTypeEMPNONOT NULLNUMBER(4)ENAME VARCHAR2(10)JOB VARCHAR2(9)MGR NUMBER(4)HIREDATE DATESA

SQL Full Outer Join Using Left and Right Outer Join and Union Clause

An SQL join statement is used to combine rows or information from two or more than two tables on the basis of a common attribute or field. There are basically four types of JOINS in SQL. In this article, we will discuss FULL OUTER JOIN using LEFT OUTER Join, RIGHT OUTER JOIN, and UNION clause. Consider the two tables below: Sample Input Table 1: Pu

Difference between From and Where Clause in SQL

1. FROM Clause: It is used to select the dataset which will be manipulated using Select, Update or Delete command.It is used in conjunction with SQL statements to manipulate dataset from source table.We can use subqueries in FROM clause to retrieve dataset from table. Syntax of FROM clause: SELECT * FROM TABLE_NAME; 2. WHERE Clause: It is used to a

Distinct clause in MS SQL Server

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values and sometimes we only want to list the different (distinct) values. Consider a simple database and we will be discussing distinct clauses in MS SQL Server. Suppose a table has a maximum of 1000 rows constit

Where clause in MS SQL Server

In this article, where clause will be discussed alongside example. Introduction : To extract the data at times, we need a particular conditions to satisfy. 'where' is a clause used to write the condition in the query. Syntax : select select_list from table_name where condition A example is given below for better clarification - Example : Sample tab

Having clause in MS SQL Server

In this article, we will be discussing having clause in MS SQL Server. There are certain instances where the data to be extracted from the queries is done using certain conditions. To do this, having clause is used. Having clause extracts the rows based on the conditions given by the user in the query. Having clause has to be paired with the group

Group by clause in MS SQL Server

Group by clause will be discussed in detail in this article. There are tons of data present in the database system. Even though the data is arranged in form of a table in a proper order, the user at times wants the data in the query to be grouped for easier access. To arrange the data(columns) in form of groups, a clause named group by has to be us

SQL Full Outer Join Using Union Clause

In this article, we will discuss the overview of SQL, and our main focus will be on how to perform Full Outer Join Using Union Clause in SQL. Let's discuss it one by one. Overview :To manage a relational database, SQL is a Structured Query Language to perform operations like creating, maintaining database tables, retrieving information from the dat

SQL Full Outer Join Using Where Clause

A SQL join statement is used to combine rows or information from two or more than two tables on the basis of a common attribute or field. There are basically four types of JOINS in SQL. In this article, we will discuss about FULL OUTER JOIN using WHERE clause. Consider the two tables below: Sample Input Table 1 : PURCHASE INFORMATIONProduct_IDMobil

Difference Between JOIN, IN and EXISTS Clause in SQL

SEQUEL widely known as SQL, Structured Query Language is the most popular standard language to work on databases. We can perform tons of operations using SQL which includes creating a database, storing data in the form of tables, modify, extract and lot more. There are different versions of SQL like MYSQL, PostgreSQL, Oracle, SQL lite, etc. There a

SQL HAVING Clause with Examples

The HAVING clause was introduced in SQL to allow the filtering of query results based on aggregate functions and groupings, which cannot be achieved using the WHERE clause that is used to filter individual rows. In simpler terms MSSQL, the HAVING clause is used to apply a filter on the result of GROUP BY based on the specified condition. The condit

How to Use NULL Values Inside NOT IN Clause in SQL?

In this article, we will see how to use NULL values inside NOT IN Clause in SQL. NULL has a special status in SQL. It represents the absence of value so, it cannot be used for comparison. If you use it for comparison, it will always return NULL. In order to use NULL value in NOT IN Clause, we can make a separate subquery to include NULL values. Mak

Using CASE in ORDER BY Clause to Sort Records By Lowest Value of 2 Columns in SQL

In this article, we will see how to use CASE in the ORDER BY clause to sort records by the lowest value of 2 columns in SQL. CASE statement: This statement contains one or various conditions with their corresponding result. When a condition is met, it stops reading and the corresponding result gets returned (similar to the IF-ELSE statement). It re

How to Escape Square Brackets in a LIKE Clause in SQL Server?

Here we will see, how to escape square brackets in a LIKE clause. LIKE clause used for pattern matching in SQL using wildcard operators like %, ^, [], etc. If we try to filter the record using a LIKE clause with a string consisting of square brackets, we will not get the expected results. For example: For a string value Romy[R]kumari in a table. If

How to Custom Sort in SQL ORDER BY Clause?

By default SQL ORDER BY sort, the column in ascending order but when the descending order is needed ORDER BY DESC can be used. In case when we need a custom sort then we need to use a CASE statement where we have to mention the priorities to get the column sorted. In this article let us see how we can custom sort in a table using order by using MSS

Frame Clause in SQL

Prerequisites: Window functions in SQL FRAME clause is used with window/analytic functions in SQL. Whenever we use a window function, it creates a 'window' or a 'partition' depending upon the column mentioned after the 'partition by' clause in the 'over' clause. And then it applies that window function to each of those partitions and inside these p

SQL Server - OVER Clause

The OVER clause is used for defining the window frames of the table by using the sub-clause PARTITION, the PARTITION sub-clauses define in what column the table should be divided into the window frames. The most important part is that the window frames are then used for applying the window functions like Aggregate Functions, Ranking functions, and

Having vs Where Clause in SQL

The difference between the having and where clause in SQL is that the where clause cannot be used with aggregates, but the having clause can. The where clause works on row's data, not on aggregated data. Let us consider below table 'Marks'. Student Course Score a c1 40 a c2 50 b

SQL | INTERSECT Clause

The INTERSECT clause in SQL is used to combine two SELECT statements but the dataset returned by the INTERSECT statement will be the intersection of the data sets of the two SELECT statements. In simple words, the INTERSECT statement will return only those rows which will be common to both of the SELECT statements. Syntax: SELECT column1 , column2

SQL | OFFSET-FETCH Clause

OFFSET and FETCH Clause are used in conjunction with SELECT and ORDER BY clause to provide a means to retrieve a range of records. OFFSET The OFFSET argument is used to identify the starting point to return rows from a result set. Basically, it exclude the first set of records. Note: OFFSET can only be used with ORDER BY clause. It cannot be used o

Parameterize IN Clause PL/SQL

PL/SQL stands for Procedural Language/ Structured Query Language. It has block structure programming features.PL/SQL supports SQL queries. It also supports the declaration of the variables, control statements, Functions, Records, Cursor, Procedure, and Triggers. PL/SQL contains a declaration section, execution section, and exception-handling sectio

How to Parameterize an SQL Server IN clause

SQL Server IN Clause is used to filter data based on a set of values provided. The IN clause can be used instead of using multiple OR conditions to filter data from SELECT, UPDATE, or DELETE query. The IN clause with parameterized data mainly inside Stored Procedures helps filter dynamic data using SQL Queries efficiently. In this article, we will

How to Solve Must Appear in the GROUP BY Clause in SQL

SQL error “Must Appear in GROUP BY Clause” is one of the most common SQL errors encountered by database developers and analysts alike. This error occurs when we attempt to run queries that include grouping and aggregation without taking proper account of the structure of the SELECT statement. In this article, we’ll look at the source of the SQL GRO

How to Solve Must Appear in the GROUP BY Clause in SQL Server

In SQL when we work with a table many times we want to use the window functions and sometimes SQL Server throws an error like "Column 'Employee. Department' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." This error means that while selecting the columns you are aggregating the func

Article Tags :