Advanced SQL Tutorial | CTE (Common Table Expression)

Build modular queries with WITH and nested selects.

8 minutesVideo LessonPDF notes

Watch Advanced SQL Tutorial | CTE (Common Table Expression) on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

Introduction to CTEs — Overview of CTE definitions, vendor support across major SQL engines, and alternative naming conventions.
Nested Subquery Baseline — Review of a classic subquery joined to an employee table before refactoring.
Writing a CTE — Step-by-step conversion of the inline subquery into a named WITH expression.
Multiple CTEs & Benefits — Explanation of readability improvements, code reuse, and comma-separated multiple CTE syntax.
CTEs vs Temporary Tables — Comparison of CTEs and temp tables regarding lifecycle, indexing capabilities, and recursive queries.

PDF notes

Bottom line: You will learn how to write, structure, and chain Common Table Expressions (CTEs) using the SQL WITH clause. You will refactor complex nested subqueries into readable, modular blocks and choose appropriately between CTEs, temporary tables, and database views.

This lesson on Advanced SQL Tutorial | CTE (Common Table Expression) is hands-on and example-driven. You will learn how to write, structure, and chain Common Table Expressions (CTEs) using the SQL WITH clause. You will refactor complex nested subqueries into readable, modular blocks and choose appropriately between CTEs, temporary tables, and database views.

What You'll Be Able To Do

Refactor inline subqueries in JOIN clauses into named CTEs using the WITH keyword.
Chain multiple CTE definitions within a single query using comma-separated syntax.
Reference CTE result sets as virtual tables in primary SELECT statements.
Evaluate architectural trade-offs between CTEs and temporary tables regarding indexing, session lifespan, and recursion.

Detailed Concept Walkthrough

1. CTE Fundamentals and Syntax

A Common Table Expression (CTE) is a named temporary result set defined before the main query using the WITH clause. It acts like an inline view or named subquery scoped strictly to the execution of a single statement.

Syntax Structure: Define the CTE using WITH cte_name AS (subquery) immediately before the main SELECT, INSERT, UPDATE, or DELETE statement.
Subquery Refactoring: The database engine extracts the nested logic out of the FROM or JOIN clause, assigning it a clear identifier that can be referenced multiple times downstream.
Execution Scope: The CTE exists only during the execution of that specific statement and consumes no persistent database catalog metadata or session storage.

-- Refactoring an inline subquery into a readable CTE
WITH department_count AS (
    SELECT department_id, COUNT(*) AS dept_count
    FROM employee
    GROUP BY department_id
)
SELECT e.first_name, e.last_name, e.department_id, d.dept_count
FROM employee e
INNER JOIN department_count d ON e.department_id = d.department_id;

Key Takeaway: CTEs replace unreadable nested subqueries with top-level, query-scoped named expressions.

2. Chaining Multiple CTEs

SQL allows chaining multiple CTEs sequentially under a single WITH clause to build multi-step data pipelines within one query.

Comma Separation: Declare the WITH keyword once at the start, and separate subsequent CTE definitions using commas after each closing bracket.
Forward Referencing: Downstream CTEs can reference previously defined CTEs in the same block, enabling step-by-step modular data transformations.
Main Query Transition: Do not place a comma after the final CTE bracket; transition immediately into the primary SELECT query.

-- Chaining multiple dependent CTEs in a single query
WITH department_count AS (
    SELECT department_id, COUNT(*) AS dept_count
    FROM employee
    GROUP BY department_id
),
large_departments AS (
    SELECT department_id, dept_count
    FROM department_count
    WHERE dept_count > 5
)
SELECT e.first_name, e.last_name, ld.dept_count
FROM employee e
INNER JOIN large_departments ld ON e.department_id = ld.department_id;

Key Takeaway: Use a single WITH keyword and comma-separate sequential CTEs to construct clean data pipelines.

3. CTE vs. Temporary Tables

While both CTEs and temporary tables store intermediate data, CTEs are query-bound and unindexed, whereas temp tables persist across sessions and support indexing.

Indexing & Constraints: Temporary tables allow custom indexes, primary keys, and statistics generation, whereas CTEs do not support explicit indexing.
Lifecycle Scope: A CTE expires the moment the query finishes, whereas a temporary table stays allocated throughout the database session or transaction.
Recursive Processing: CTEs natively support recursive query patterns to traverse hierarchies (such as parent-child relationships), which temp tables cannot do directly.

-- CTE approach: ephemeral, no DDL/indexing needed
WITH dept_summary AS (
    SELECT department_id, COUNT(*) AS total_staff
    FROM employee
    GROUP BY department_id
)
SELECT * FROM dept_summary WHERE total_staff >= 3;

Key Takeaway: Choose CTEs for one-off query readability and recursion; choose temp tables for large intermediate sets queried repeatedly.

Topics Covered in Advanced SQL Tutorial | CTE (Common Table Expression)

Introduction to CTEs (0:00 - 0:45) — Overview of CTE definitions, vendor support across major SQL engines, and alternative naming conventions.
Nested Subquery Baseline (0:45 - 1:40) — Review of a classic subquery joined to an employee table before refactoring.
Writing a CTE (1:40 - 2:40) — Step-by-step conversion of the inline subquery into a named WITH expression.
Multiple CTEs & Benefits (2:40 - 3:35) — Explanation of readability improvements, code reuse, and comma-separated multiple CTE syntax.
CTEs vs Temporary Tables (3:35 - 4:30) — Comparison of CTEs and temp tables regarding lifecycle, indexing capabilities, and recursive queries.

SQL Cheat Sheet

WITH cte_name AS (...) SELECT ... — Defines a single named CTE before a main query

WITH dept_cnt AS (SELECT department_id, COUNT(*) AS cnt FROM employee GROUP BY department_id) SELECT * FROM dept_cnt;

WITH cte1 AS (...), cte2 AS (...) SELECT ... — Chains multiple CTEs using comma separation

WITH c1 AS (SELECT id FROM t1), c2 AS (SELECT id FROM t2) SELECT * FROM c1 JOIN c2 ON c1.id = c2.id;

Subquery Refactoring — Oracle terminology for isolating subqueries using WITH

WITH refactored_data AS (SELECT * FROM employee) SELECT * FROM refactored_data;

WITH RECURSIVE — Declares recursive CTEs for hierarchical tree traversal

WITH RECURSIVE org_tree AS (SELECT emp_id, mgr_id FROM employee) SELECT * FROM org_tree;

Comparison Table

Feature	Common Table Expression (CTE)	Temporary Table
Lifetime Scope	Single query execution only	Database session or connection
Indexing & Keys	Not supported	Supports indexes and constraints
Recursive Queries	Supported natively	Not supported directly
Catalog Overhead	Zero metadata or DDL privileges needed	Requires temporary table creation permissions
Best Use Case	Readability, code refactoring, hierarchies	Heavy intermediate result caching across queries

Common Pitfalls

Mistake: Repeating the WITH keyword before every CTE in a chained list. Avoid: Declare WITH once and separate each subsequent CTE with a comma.
Mistake: Naming a CTE identically to an existing column in the query. Avoid: Give CTEs distinctive table-like names such as department_count to prevent syntax confusion.
Mistake: Trying to reference a CTE in a subsequent independent query. Avoid: Consolidate the dependent logic into the same statement or switch to a temp table.
Mistake: Placing a trailing comma after the final CTE bracket before SELECT. Avoid: Omit the comma after the last CTE closing bracket to start the SELECT.

FAQs

What is the difference between a CTE and a database View? A View is a persistent database object saved in the schema catalog, whereas a CTE is defined on the fly and exists only for a single query execution.
Can I index a CTE to improve query execution speed? No, CTEs cannot be indexed; if intermediate results are large and need custom indexing, use a temporary table instead.
What is subquery refactoring? Subquery refactoring is the Oracle SQL term for CTEs, referring to the practice of extracting complex nested subqueries into top-level named expressions.
Can one CTE reference another CTE defined in the same WITH block? Yes, any CTE can reference prior CTEs declared earlier within the same WITH statement.

Common pitfalls

Mistake: Repeating the WITH keyword before every CTE in a chained list. Avoid: Declare WITH once and separate each subsequent CTE with a comma.
Mistake: Naming a CTE identically to an existing column in the query. Avoid: Give CTEs distinctive table-like names such as department_count to prevent syntax confusion.
Mistake: Trying to reference a CTE in a subsequent independent query. Avoid: Consolidate the dependent logic into the same statement or switch to a temp table.
Mistake: Placing a trailing comma after the final CTE bracket before SELECT. Avoid: Omit the comma after the last CTE closing bracket to start the SELECT.

Common table expressions are a very useful feature in SQL, especially when your queries start getting complex. They're available for many different database vendors. In this video, you'll learn what a common table expression, or CTE, is, why you'd want to use one, see an example of using one in a query, and how to write one. Let's get into it. Welcome to the Database Star YouTube channel, the place for developers looking to improve their database and SQL skills. A common table expression, or a CTE, is a query that you can define within another query. It's like a subquery, but you give the subquery a name. You can then refer to this named query in other places within your query. A CTE is sometimes called a with clause, because the way you define one is by using the WITH keyword. It's also called subquery refactoring in the Oracle community. The CTE feature is available in many databases: Oracle, SQL Server, MySQL, Postgres, SQLite, and MariaDB, and probably many others. I'll demonstrate the examples on a MySQL database, and show any differences for other vendors as they are needed. I've connected to a MySQL database using MySQL Workbench. You can use any other IDE and database you like, if you want to follow along. We've got some sample data in a database here. We'll use an employee table and a department table. Yeah, it's a commonly used example, but hopefully it's easy to understand. There's a link in the description to the scripts to set up the sample data on my GitHub repository, if you want to follow along. We've got two tables. Here's the employee data with a few rows, and here's the department data. Now let's look at a SELECT query we can use to demonstrate the concept of a CTE. This query will show the employee names and the number of employees from the same department. It uses a subquery in the INNER JOIN clause. This query will first find the department_id and the count of each department in the subquery. It will then use this result and call it d, so it can be used in the rest of the query. We then join this d result to the employee table, which we've called e. Finally, we select the first_name, last_name, department_id, and dept_count fields. We can run this query and see the results here. We've got a few employee names, the department_id, and the number of employees in the department. Let's update this query by adding a common table expression. A common table expression will allow us to give a name to the subquery that we have used. The main reason to do that in this example, other than to demonstrate the concept, is to make the query more readable. I'll cover the benefits in more detail after we see the query. To update our query to use a CTE, first we add the WITH keyword to the very start of the query, before the SELECT keyword. We then provide a name for this subquery. This is the name that is used in the main query, and it helps to make it something that can be understood. Our subquery shows the department_id and count, so we can call this department_count. It's descriptive and doesn't cause confusion, which is why I didn't call it dept_count, as we have a column with that name already. After the name, we put the AS keyword. Then we open brackets and add a closing bracket. Within these brackets is where we add our query. Move the subquery from the main query into these brackets so it looks like this. After the brackets, we just start the SELECT query. There's no comma or any other keywords here. Next, we update the main query to refer to our new CTE instead of the subquery. We remove everything for the subquery, so from the opening bracket to the closing bracket. Add in the name of the CTE here, which is department_count. We'll leave the table alias of d, because we've used that in other places in the query. If you look at this JOIN clause here, we can see it's as though the department_count is a table or a view. That's what a CTE lets us do. We can use a subquery just like it's a view or a table. Now let's run the query. We can see the results on the screen here, which are the same as the original query without the CTE. You might be wondering, why would we go to all that trouble for such a small query? Well, it might not make much of a difference to the size and complexity of this query, but it's helpful for larger queries. Why would you use a common table expression? There are a few main benefits. Let's take a look at them. Using a common table expression can make your query more readable. This is especially true for larger queries and those that use complicated logic on columns. You can move the complicated logic to a CTE, which makes the overall query simpler and easier to read. If you've got complex logic or a subquery, you can give it a name, so when you refer to it in your query, you will know what that subquery means, instead of adding comments or trying to work it out every time you look at it. Also, if you have two parts of a query that use the same data or subquery, you can move it to a CTE and refer to the CTE in both places. If you need to use multiple CTEs within a query, you can simply add a comma after the brackets of the first query and add the second CTE. Here's what it could look like. We have the first query in the with clause, and after the brackets we have a comma. We then add the name of the second query and continue with the AS keyword and the details of the query. We don't need to add the WITH keyword again. Another benefit of using a common table expression is that you can use it just like a view in SQL. It has a name and columns. You can join to it and filter from it, which is helpful if you don't want to create a new view object or don't have the permission. You can also use common table expressions to write recursive queries or hierarchical queries. This is a type of query that refers to previous rows of the same query, such as finding the parent category name of a category, or a manager name for an employer. I'll create a whole video on hierarchical queries, as it can get complex and different databases handle it differently. If you're watching this video and thinking, hey, this sounds like a temp table, then you're right. It's very similar to a temp table. A temp table, in case you didn't know, is a type of object in the database that you can create, and it's automatically dropped when you no longer need it. It's great for simplifying your queries and improving a multi-step process. However, there are some differences between a common table expression and a temp table. A CTE cannot have indexes or constraints, but a temp table can have both indexes and constraints. A CTE only exists when a query is run, but a temp table can exist for either the connection or for all sessions, depending on how it's set up. A CTE can be recursive, but a temp table cannot. You would use a CTE when you want to improve the readability of a complex query, and possible for a recursive query. You would use a temp table if you have a large result set, or need to refer to the result set more than once. So that's what a CTE, or common table expression, is. Hopefully, this video has helped you understand what they are and given you some ideas on how to use them. If you're interested in learning more about subqueries, check out this video here on subqueries and when to use them. If you want to learn more about database design and SQL, visit my website at databasestar.com. If you liked this video, consider subscribing to my channel. Thanks for watching.

Frequently asked questions

What is the difference between a CTE and a database View?

A View is a persistent database object saved in the schema catalog, whereas a CTE is defined on the fly and exists only for a single query execution.

Can I index a CTE to improve query execution speed?

No, CTEs cannot be indexed; if intermediate results are large and need custom indexing, use a temporary table instead.

What is subquery refactoring?

Subquery refactoring is the Oracle SQL term for CTEs, referring to the practice of extracting complex nested subqueries into top-level named expressions.

Can one CTE reference another CTE defined in the same WITH block?

Yes, any CTE can reference prior CTEs declared earlier within the same WITH statement.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.