Can You Use Multiple WITH Statements in SQL?

A comprehensive guide to multiple WITH statements in SQL, perfect for beginners and experts alike.

The SQL WITH clause allows you to define a CTE (common table expression). A CTE is like a table that is populated during query execution. You can use multiple WITH statements in one SQL query to define multiple CTEs. In this article, we will explain how to define multiple CTEs in a single query. We’ll also show how this approach takes the expressive power of an SQL query to the next level.

Can You Use Multiple WITH Statements in SQL?

Yes! Below is a simple query using two WITH clauses:

WITH wine_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘wine’
  AND qty_exported > 0
),
car_exporting_country AS (
  SELECT country
  FROM product
  WHERE product_name = ‘car’
  AND qty_exported > 0
)
SELECT country
FROM wine_exporting_country 
INTERSECT car_exporting_country

In the above query, there are two CTEs. The first one (in red) is called wine_exporting_country and the second one (in blue) is car_exporting_country. The main query (in black) references both WITH statements as regular tables using INTERSECT. The intersection of the two tables will return only those countries present in both CTE results.

Note that you can see the keyword WITH only once time. After the WITH clause, you need to put the name of the CTE, then the AS keyword and finally between parentheses the SELECT query to define the CTE.

If you want to learn how to use multiple WITH statements in one query, I recommend our interactive Recursive Queries course. It teaches you the details of the WITH clause syntax in over 100 hands-on exercises. It covers the basic syntax, multiple WITH statements, and recursive queries – the most complex use of the WITH syntax.

The WITH Clause in SQL

Let’s first explain how the WITH clause works. It creates a kind of virtual table (the CTE) on the fly, which is created and populated during the execution of a single SQL statement; after the SQL execution, the CTE ‘table’ is automatically removed. For a better understanding of the WITH clause, I suggest the article  What is a CTE?.

In this article, we will use a database table called product that tracks countries and the most popular products they produce (e.g. olive oil, wine, cars). It records the quantity produced, imported, and exported by each country. The table also has the price of the product and the population of the country.

Let’s see a simple example of a query that uses a WITH clause to define a CTE. Suppose we want to obtain a list of the countries that export more olive oil than they import. We want the results ordered by the quantity produced by each country. Here’s the query:

WITH olive_oil_exporter AS (
  SELECT 
    country, 
    qty_produced
  FROM product
  WHERE product_name = ‘olive oil’
  AND qty_exported > qty_imported
)
SELECT country
FROM olive_oil_exporter
ORDER BY qty_produced DESC;

In this query, we have a CTE called olive_oil_exporter, which is like a table with two columns: country and qty_produced. The rows in the CTE only contain countries that export more olive oil than they import. After that, we use a regular SELECT to query the CTE olive_oil_exporter for the country names, which we order descendingly by the quantity of olive oil produced in that country.

Using Multiple WITH Statements in SQL

Let’s suppose we want to categorize the countries into food-producing countries and technology-producing countries. To be a food producer, a country must export more than 100 million dollars in food products. To be a high technology producer, a country must produce more than 1000 dollars in technology products per inhabitant.

We want a report with the names of all countries and two columns called is_a_food_producer and is_a_hightech_producer. The SQL query for this report is:

WITH food_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘food’
  GROUP BY country
  HAVING SUM( qty_exported * unit_price) > 100000000
)
hightech_producer AS (
  SELECT country
  FROM products
  WHERE product_family = ‘technology’
  GROUP BY country
  HAVING SUM( qty_produced / country_population) > 1000
)
SELECT DISTINCT 
  p.country,
  CASE 
     WHEN fp.country IS NULL THEN ‘No’ 
     ELSE ‘Yes’ END AS is_a_food_produced,
  CASE 
    WHEN htp.country IS NULL THEN ‘No’ 
    ELSE ‘Yes’ END AS is_a_hightech_produced,
FROM products p
LEFT JOIN food_producer fp 
ON fp.country = p.country
LEFT JOIN hightech_producer htp 
ON htp.country = p.country

In the above query, we can clearly identify three separate queries. The first two queries use the WITH clause to define two CTE tables: food_producer and hightech_producer. The third query is the main query, which consumes the two CTE tables created previously.

After the WITH clause, you can see the name of the CTE table (food_producer), then the subclause AS and finally (in parentheses) the query for that CTE. For the second CTE, the clause WITH is not needed; just put a comma, and then repeat the same syntax starting with the CTE name.

Both the CTEs food_producer and hightech_producer have only one column: country. The main query takes the names of all the countries from the table product, then a LEFT JOIN is done against each of the CTEs. When the LEFT JOIN doesn’t have a matching row, it means that the reply for this country is ‘No’; when there is a matching row, the value for this country is ‘Yes’.

Before going to the next section, I would like to suggest the article  How to Write Multiple CTEs. In it, you can find many examples and explanations about CTEs.

Using Multiple and Nested WITH Statements in SQL

In some cases, we need a second CTE that is based on the first CTE: a nested CTE. In other words, the query to define the second CTE must have a reference to the first CTE. Let’s see an example.

We want to obtain the total dollar amount exported by countries in food products. In the same report, we want to show the percentage that this amount represents in that country’s total exports for all kinds of products. The query is:

WITH country_export_by_product AS (
  SELECT country, 
  product_family, 
  SUM(qty_exported * unit_price) AS total_exports
  FROM product
  GROUP BY country, product_family
),
country_export_total AS (
  SELECT country, 
  SUM(total_exports) AS total_exports_country
  FROM country_export_by_product 
  GROUP BY country
)
SELECT 
  cp.country, 
  product_family, 
  cp.total_exports_food ,
  ROUND((cp.total_exports_food / ct.total_exports_country) * 100, 2) 
     AS percentage_of_total_exports
FROM country_export_by_product cp
JOIN country_export_total ct 
ON ct.country = cp.country
ORDER BY country, product_family;

In this query, we created a CTE called country_export_by_product which has the columns country, product_family and total_exports (which represents the total amount of this product exported by this country (in dollars)). Note the GROUP BY clause uses the columns country and product_family.

The next CTE is called country_export_total and is based on the previous CTE country_export_by_product. The idea of this CTE is to obtain the total amount exported by each country based on the previous CTE. Note in the second CTE we use a GROUP BY country clause. Needing different levels of GROUP BY clause is the reason we have two CTEs.

The main query references both CTEs, joining them by the country value. Then the expression

TRUNC((cp.total_exports_food / ct.total_exports_country) * 100, 2)

… is used to calculate the percentage that each product represents in the total exports of that country.

In terms of syntax, you can have a CTE reference another CTE in the same query. This is what we did in our query: when defining the CTE country_export_total, we referred to the CTE country_export_by_product defined previously.

Note that we can refer to the WITH statement defined before the current WITH statement, but not to the ones afterwards. You can refer to each WITH statement multiple times in another WITH statement or in a main query. In our example, we referenced the first defined WITH (the country_export_by_product CTE) in two places: In the second WITH (the country_export_total CTE) and in the main query.

Other limitations related to the syntax of the WITH clause are:

• You must use the WITH keyword only once, before the first CTE.
• All CTEs are separated by commas, but there is no comma before the main query. This follows the syntax pattern:

            WITH cte_name1 AS (query1), 
           cte_name2 AS (query2) 
      main_query

I suggest the article SQL CTEs Explained with Examples for several more sample WITH queries; they demonstrate how to improve the organization and readability of your SQL queries using CTEs.

Using the WITH Clause to Create Recursive Queries

You use the WITH clause in SQL to define recursive queries. Recursive queries help you query hierarchical structures (i.e. organizational charts, trees, or graphs). You can read more about querying hierarchical structures here.

Recursive queries are based on the WITH clause. To create a recursive query, you only need one WITH clause, but the query within WITH consists of two parts.

Recursive queries are useful when the database tables or data model has an implicit sort of hierarchy. Perhaps the most common example table to explain this topic is the typical table employee with the columns employee_id and manager_employee_id.

If we want a report to show all employees with the names of their managers and the employee’s hierarchy level, we can use the following recursive query:

WITH RECURSIVE company_hierarchy AS (
  SELECT 
    employee_id, 
    firstname, 
    lastname, 
    manager_employee_id, 
    0 AS hierarchy_level
  FROM employees
  WHERE manager_employee_id IS NULL 
  UNION ALL 
  SELECT 
    e.employee_id, 
    e.firstname, 
    e.lastname, 
    e.manager_employee_id,
    hierarchy_level + 1
  FROM employees e, company_hierarchy ch
  WHERE e.manager_employee_id = ch.employee_id
)
SELECT 
  ch.firstname AS employee_first_name, 
  ch.lastname AS employee_last_name,
  e.firstname AS boss_first_name, 
  e.lastname AS boss_last_name,
  hierarchy_level
FROM company_hierarchy ch
LEFT JOIN employees e 
ON ch.manager_employee_id = e.employee_id
ORDER BY ch.hierarchy_level, ch.manager_employee_id;

In this query, we can see the clause WITH RECURSIVE, which is used to create a recursive CTE called company_hierarchy. The CTE will have all the employees' names with their managers' names. Note the CTE has two SELECT statements connected by UNION ALL. The first SELECT is to obtain the first employee in the recursive query (CEO John Smith).

The second SELECT of the UNION is a query that is executed many times. On each execution, it returns the employee(s) in the next level in the hierarchy. For example, its first execution returns all employees reporting directly to John Smith.

Finally, there’s a third SELECT statement; it is outside of the CTE. It selects the names of the employees, the names of their bosses, and the hierarchy level. Data is taken from the CTE and joined with the table employees. We use a LEFT JOIN because we want all the data from the CTE (including John Smith, who has a NULL value in the column manager_id). The results are shown in ascending order: first by the hierarchy level, then by the boss’s employee_id. Below is the result of the query:

To learn more about recursive queries, check out the article What Is a Recursive CTE in SQL?

Continue Learning About Multiple WITH Statements in SQL

In this article, we covered the use of multiple WITH statements in a single SQL query. We also mentioned how to use the WITH clause in recursive queries. If you want to continue learning about the WITH clause, I recommend our Recursive Queries course, which offers an excellent opportunity to practice the most challenging type of SQL queries.

Multiple WITH statements are most useful when you write complex SQL reports. If that’s the case for you, I also recommend our free SQL for Data Analysis Cheat Sheet, which we designed specifically to help you write complex queries for data analysis.

If you want to practice SQL at an advanced level, check out our Advanced SQL Practice track. It contains over 200 exercises to help you practice advanced SQL concepts. Every other month, we publish a new advanced SQL practice course in our Monthly SQL Practice track. There are also many different ways to practice advanced SQL with our platform.

You can also get all of these courses and more in our All Forever plan. The plan gives you lifetime access to all our SQL courses at various levels of proficiency and in four SQL dialects. Sign up today!