WITH clause in SQL

The WITH clause in SQL, also known as a Common Table Expression (CTE), allows us to define a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. This can make complex queries easier to read and maintain by breaking them down into simpler, more manageable parts.

SQL Syntax

WITH cte_name AS (
    -- SQL query
)
SELECT column1, column2
FROM cte_name;

Example: Create the Employees table.

SQL

CREATE TABLE Employees (
   EmployeeID INT,
    Name VARCHAR(20),
    Age int,
    Salary VARCHAR(10),
    Department VARCHAR(25),
    Gender VARCHAR(1)
);

Load the data into the Employees table.

SQL

insert into Employees values
(1, "Ashish", 24, "25000", "Electrical", "M"), 
(2, "Kirshan", 32,"30000", "Electrical", "M"),
(3, "Anjali", 20,"25000", "Electronics", "F"),
(4, "Manjulika", 30, "15000", "Electrical", "F"),
(5, "Katrina", 37,"18000", "Computer", "F"),
(6, "Esha", 37,"20000", "Computer", "F"),
(7, "Ankita", 20,"9000", "Electronics", "M"), 
(8, "Meenakshi", 30, "15000", "Computer", "F"),
(9, "Alia", 37,"16000", "Electronics", "F");

To see the data in the Employees table, execute the following query:

SQL

SELECT * FROM Employees;

The above query returns the following result:

Table: Employees

EmployeeID	Name	Age	Salary	Department	Gender
1	Ashish	24	25000	Electrical	M
2	Kirshan	32	30000	Electrical	M
3	Anjali	20	25000	Electronics	F
4	Manjulika	30	15000	Electrical	F
5	Katrina	37	18000	Computer	F
6	Esha	37	20000	Computer	F
7	Ankita	20	9000	Electronics	M
8	Meenakshi	30	15000	Computer	F
9	Alia	37	16000	Electronics	F

By using the “with” clause, we are defining a temporary result set, and then use it in the following Select query.

SQL

with MyQuery as (
SELECT EmployeeID, Name, Age, LENGTH(Name) as Namelength FROM employees
)

SELECT EmployeeID, Name, Namelength FROM MyQuery

The above query returns the following result:

EmployeeID	Name	Namelength
1	Ashish	6
2	Kirshan	7
3	Anjali	6
4	Manjulika	9
5	Katrina	7
6	Esha	4
7	Ankita	6
8	Meenakshi	9
9	Alia	4

Previous Next