What are subqueries and CTEs in PostgreSQL and when to use them

Subqueries and CTEs

Subqueries and CTEs let you break complex queries into composable pieces. CTEs are usually easier to read and debug.

Scalar subquery

SELECT name,
       salary,
       (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

IN with subquery

SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'Germany'
);

EXISTS (more efficient for large sets)

SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

CTE (WITH clause)

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
),
dept_counts AS (
  SELECT department_id, COUNT(*) AS cnt
  FROM   high_earners
  GROUP  BY department_id
)
SELECT d.name, dc.cnt
FROM   departments d
JOIN   dept_counts dc ON d.id = dc.department_id;

CTEs are defined once, named, and reused in the main query. PostgreSQL 12+ inlines CTEs by default for better optimization. Use MATERIALIZED to force a CTE to execute as a separate step when you need stable intermediate results.