Multiple CTEs in one query in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does CTE stand for in SQL?

CTE stands for Common Table Expression. It is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

beginner

How do you define multiple CTEs in a single SQL query?

You define multiple CTEs by separating each CTE with a comma after the WITH keyword, like this: WITH cte1 AS (...), cte2 AS (...). Then you write the main query that uses these CTEs.

intermediate

Why use multiple CTEs instead of writing one big query?

Multiple CTEs help break down complex queries into smaller, easier-to-understand parts. Each CTE can represent a step in your data processing, making the query clearer and easier to maintain.

intermediate

Can CTEs refer to each other in a multiple CTE query?

Yes, later CTEs can refer to earlier CTEs defined before them. This allows you to build on previous results step-by-step within the same query.

beginner

Write a simple example of a SQL query with two CTEs.

WITH first_cte AS (SELECT 1 AS num), second_cte AS (SELECT num + 1 AS num_plus_one FROM first_cte) SELECT * FROM second_cte;

What keyword starts the definition of multiple CTEs in a SQL query?

A WITH

B SELECT

C FROM

D JOIN

The WITH keyword is used to start defining one or more CTEs in a SQL query.

How do you separate multiple CTEs in a single WITH clause?

A Using semicolons (;)

B Using commas (,)

C Using AND

D Using OR

Multiple CTEs are separated by commas within the WITH clause.

Can a CTE refer to another CTE defined after it in the same WITH clause?

A No, a CTE can only refer to CTEs defined before it

B Yes, order does not matter

C Only if you use RECURSIVE keyword

D Only in MySQL

A CTE can only refer to CTEs defined before it in the same WITH clause.

What is a main advantage of using multiple CTEs?

A They improve query speed always

B They allow queries without SELECT

C They make complex queries easier to read and maintain

D They replace indexes

Multiple CTEs help break down complex queries into simpler parts, improving readability and maintenance.

Which of the following is a valid way to write multiple CTEs?

A WITH cte1 AS (...) AND cte2 AS (...) SELECT * FROM cte2;

B WITH cte1 AS (...); WITH cte2 AS (...) SELECT * FROM cte2;

C SELECT * FROM cte1; SELECT * FROM cte2;

D WITH cte1 AS (...), cte2 AS (...) SELECT * FROM cte2;

Multiple CTEs are defined together in one WITH clause separated by commas.

Explain how to write a SQL query with multiple CTEs and why it is useful.

Think about breaking a big problem into smaller steps.

Describe the rules about referencing CTEs within multiple CTEs in one query.

Consider the order of CTEs in the WITH clause.