PostgreSQL: WITH Queries (Common Table Expressions)

PostgreSQL Common Table Expression (CTE) creates a temporary result set in query, which you can use in other SQL statements like SELECT, INSERT, UPDATE or DELETE.

CTEs are temporary and only exist during the execution of queries. Use WITH clause to implement CTE.

Here are some advantages of using CTE:

• CTEs simplify and organize complex queries into more readable formats.
• Use CTEs to generate the initial result set which can be used in another query multiple times for further processing.
• CTEs offer the ability to write recursive queries. Recursive queries are queries that reference themselves multiple times. It is useful when you want to query tree structure hierarchical data.

WITH <CTE_name>(<column_list>) AS  (
	<CTE_query>
)
<sql_statement>;

In above syntax,

• Specify WITH clause and give CTE a name, which is <CTE_name>.
• After that, specify list of columns in open and close bracket as (<column_list>). Specifying <column_list> is optional. If you do not specify column list after CTE name, the select list of <CTE_query> will be the column list of CTE query.
• Specify <CTE_Query> that is any DML SQL statement that can be SELECT, INSERT, UPDATE or DELETE.
• Specify <sql_statement> that can be any DML SQL statement

Consider we have a Department (parent table) and Employee (child table) as below

Let's use CTE WITH query to list down all employees with Gender as Female.

WITH cte_employee AS 
	(SELECT emp_id, first_name, last_name,
		(CASE
			WHEN gender = 'M' THEN 'Male'
			WHEN gender = 'F' THEN 'Female'
		 END) Gender
	FROM Employee)
SELECT * FROM cte_employee WHERE gender = 'Female';

In the above example, the common table expression cte_employee is defined using the WITH clause that is executed first and returns a result set. Then that common CTE cte_employee resultset is used in the SELECT statement to return Female employees.

The WITH clause can have a column list specified, where the column name can be changed to new names.

WITH cte_employee(id, firstName, lastName, Gender) AS 
	(SELECT emp_id, first_name, last_name,
		(CASE
			WHEN gender = 'M' THEN 'Male'
			WHEN gender = 'F' THEN 'Female'
		 END) Gender
	FROM Employee)
SELECT * FROM cte_employee WHERE gender = 'Female';

Now let's take join between CTE result set with other table and return data.

WITH cte_dept(dept_id, name) AS 
	(SELECT dept_id, dept_name
	FROM Department
	WHERE dept_name IN ('IT', 'FINANCE'))
SELECT emp_id, first_name, last_name, gender, name 
FROM Employee INNER JOIN cte_dept USING (dept_id);

In the above example, the first CTE query cte_dept is executed and returns a resultset that includes the department number of IT and FINANCE departments. Note that we specified the column list in CTE WITH clause and renamed the dept_name column to just name. Then it takes joins that resultset with the Employee table using dept_id and shows the Employee and Department information.

You can use DML statements like INSERT, UPDATE, and DELETE in WITH statement. That will allow you to perform several options in one query.

For example, you want to move all employees who belong to HR department to another table that is hr_employees. Let's create a table HR_EMPLOYEES first, as shown below.

CREATE TABLE IF NOT EXISTS HR_EMPLOYEE (
emp_id 	INT PRIMARY KEY,
first_name 	VARCHAR(50),
last_name 	VARCHAR(50),
gender 		CHAR(1),
email		VARCHAR (100),
salary		INT
);

Now, we will delete employees belonging to the HR department from the Employee table and insert them in the HR_Employee table using one CTE WITH statement.

WITH moved_employees AS (
    DELETE FROM Employee
    WHERE dept_id = 1
	RETURNING *
)
INSERT INTO HR_Employee
SELECT emp_id, first_name, last_name, gender, email, salary 
FROM moved_employees;

After executing the above statement, 2 employees with dept_id = 1 (Department as HR) were deleted from the Employee table and inserted into the HR_Employee table. Let's validate it by querying Employee and HR_Employee table.