EasyCTEs & Recursive

CTE to simplify complex query readability

The query

SQL

WITH
active_emps AS (
  SELECT * FROM employees WHERE status = 'active'
),
eng_dept AS (
  SELECT dept_id FROM departments WHERE dept_name ILIKE '%engineering%'
)
SELECT
  ae.first_name,
  ae.last_name,
  ae.salary,
  ae.job_title
FROM active_emps ae
JOIN eng_dept ed ON ae.dept_id = ed.dept_id
ORDER BY ae.salary DESC;

Tested against PostgreSQL 16

Note

“CTEs improve readability. Each CTE is a named building block. Same as subqueries but more maintainable.

Tables referenced

employees10 cols
departments5 cols