How to use WITH clause in SQL

If you are fairly familiar with SQL, you may already be comfortable working with the basic SELECT statement. However, when it comes to working with more complex queries, the WITH clause can be a powerful tool to have in your toolbox. The WITH clause allows you to create temporary named result sets, also known as Common Table Expressions (CTEs), that you can reference multiple times throughout a query.

Syntax

The basic syntax for the WITH clause is as follows:

WITH cte_name AS ( SELECT * FROM some_table WHERE some_condition ) 
SELECT * FROM cte_name

In this example, the WITH clause is used to define a CTE named cte_name. This CTE is defined as a SELECT statement that pulls data from some_table based on a specified condition. The SELECT statement that follows references cte_name to pull data from the CTE.

Benefits of Using WITH Clause

The WITH clause can make your SQL queries easier to read and understand, especially when working with large, complex queries. It can also help with performance optimization, as you can reference the results of a CTE multiple times without having to re-run the underlying query each time.

In addition, CTEs can be used recursively to build complex hierarchical queries, such as those involving tree structures or self-referencing tables.

Examples

Let’s take a look at some practical examples of how to use the WITH clause in SQL.

Suppose you have a database table of employees with the following columns:

• id
• name
• department
• salary

You might use a simple WITH clause to create a CTE named high_paid_employees that includes only employees with a salary greater than 100,000:

1. Simple WITH Clause

2. Multiple CTEs

You can define multiple CTEs within a single WITH clause, each separated by a comma. For example, suppose you want to filter for only high paid employees, and then perform some calculations on their salaries. You could create two CTEs, high_paid_employees and total_salary, as follows:

WITH high_paid_employees AS 
( SELECT * FROM employees WHERE salary > 100000 ), 
total_salary AS ( SELECT SUM(salary) 
FROM high_paid_employees ) SELECT * FROM total_salary;

3. Recursive CTE

Recursive CTEs can be used to handle situations where you have hierarchical data such as an organizational chart. Consider the following sample database table of employees:

• id
• name
• title
• manager_id

Manager id is a foreign key referencing to the id column. Here’s an example of a recursive CTE that lists an employee’s reporting hierarchy:

WITH RECURSIVE reporting_hierarchy AS ( SELECT id, name, title, manager_id, 
1 AS level FROM employees WHERE id = 12345 
UNION ALL SELECT e.id, e.name, e.title, e.manager_id, level + 1 
FROM employees e JOIN reporting_hierarchy rh ON e.id = rh.manager_id ) 
SELECT id, name, title, manager_id, level FROM reporting_hierarchy 
ORDER BY level, name;

In this example, the CTE is named reporting_hierarchy. The initial SELECT statement selects the data for the employee with an ID of 12345, setting the level to 1. The UNION ALL clause then recursively selects all employees who report to that initial employee, incrementing the level each time. The final SELECT statement simply returns the entire reporting_hierarchy table, ordered by level and name.

Frequently Asked Questions

Conclusion

In conclusion, the WITH clause can be an incredibly powerful tool for working with complex SQL queries. By defining and referencing named CTEs, you can make your queries more readable and better organized while also improving performance. With a little practice, you’ll be able to use the WITH clause to handle even the most challenging SQL queries.

📕 Related articles about MySQL

• MySQL Connecting to Server: A Comprehensive Guide
• Creating a Table: A Comprehensive Guide for Software Developers
• MySQL CREATE INDEX Statement: A Comprehensive Guide
• How to use Create Table Extension in SQL
• MySQL INSERT Statement: How to Insert Data into a Database Table
• How to use Arithmetic Operators in SQL