Course contentsShow
← Data Science
Lesson 990 of 2,14525. SQL Subqueries and CTEsFree lesson

Basic CTE Syntax and Structure

Writing your first CTE using WITH clause, defining column names, and referencing the CTE in main query.

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:

  1. WITH keyword that introduces the CTE
  2. CTE name (like cte_name) that you choose
  3. 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.