Basic CTE Syntax and Structure
You'll learn how to write your first Common Table Expression (CTE) using the WITH clause, define its structure, and reference it in your main query.
What is CTE Syntax?
Think of a CTE as giving a name to a temporary result set, like creating a labeled container for your subquery results. Instead of nesting subqueries within your main query, you define them before the main query using the WITH keyword, making your SQL much more readable.
The WITH Clause Structure
The basic syntax follows this pattern:
WITH cte_name AS (
-- Your subquery goes here
SELECT column1, column2
FROM table_name
WHERE condition
)
-- Now use the CTE in your main query
SELECT *
FROM cte_name;
The CTE has three key parts:
- WITH keyword that introduces the CTE
- CTE name (like
cte_name) that you choose - AS followed by parentheses containing a SELECT statement
Defining Column Names
You can optionally specify column names for your CTE:
WITH high_earners (employee_id, full_name, salary) AS (
SELECT id, name, salary
FROM employees
WHERE salary > 75000
)
SELECT full_name, salary
FROM high_earners
ORDER BY salary DESC;
Referencing the CTE
Once defined, treat the CTE name like any table in your FROM clause. You can join it, filter it, or aggregate it—it behaves exactly like a regular table within the scope of your query.
Key Takeaway: CTEs use WITH cte_name AS (subquery) syntax to create named temporary result sets that you can reference in your main query, making complex queries more readable than nested subqueries.