SQL CTEs (Common Table Expressions)

What is a CTE?

A CTE is a temporary result set defined using the WITH keyword.

It exists only for the duration of the query.

Basic Syntax

WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

Basic Example

WITH high_salary AS (
  SELECT name, salary
  FROM employees
  WHERE salary > 50000
)
SELECT * FROM high_salary;

CTE vs Subquery

-- Subquery
SELECT name
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

-- CTE
WITH avg_salary AS (
  SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT name
FROM employees, avg_salary
WHERE employees.salary > avg_salary.avg_sal;

Multiple CTEs

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
),
high_salary AS (
  SELECT name, salary, department
  FROM employees
)
SELECT *
FROM high_salary h
JOIN dept_avg d
ON h.department = d.department
WHERE h.salary > d.avg_salary;

Using with Aggregates

WITH totals AS (
  SELECT department, COUNT(*) AS total
  FROM employees
  GROUP BY department
)
SELECT * FROM totals;

Full Example

WITH high_salary AS (
  SELECT name, salary
  FROM employees
  WHERE salary > 50000
)
SELECT name, salary
FROM high_salary;

Why CTEs are Important

Improve readability
Organize complex queries
Reusable within a query
Common in advanced SQL

Common Mistakes

Forgetting WITH keyword
Incorrect column references
Thinking CTEs are permanent tables

Practice

Create a CTE and use it in a query.

WITH my_cte AS (
  SELECT * FROM table_name
)
SELECT * FROM my_cte;

Need Help?

Ask the AI if you need help understanding or want to dive deeper in any topic

Menu