Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

10

Chapter 10

Subqueries & EXISTS

Queries inside queries. Scalar, correlated, EXISTS, IN, ANY, ALL — and when a CTE would read better.

In this chapter

21

queries, easy → advanced

  1. 001Subquery in WHERE: employees earning above averageRead
  2. 002Subquery in FROM (derived table)Read
  3. 003EXISTS: find customers who have placed ordersRead
  4. 004NOT EXISTS: customers who never orderedRead
  5. 005IN with subquery: employees in engineering departmentsRead
  6. 006Correlated subquery: find department average for each employeeRead
  7. 007Subquery in SELECT: each order with its item countRead
  8. 008ANY / ALL: employees earning more than ANY managerRead
  9. 009Subquery with aggregation to find highest-earning deptRead
  10. 010Multi-level nested subqueryRead
  11. 011Subquery to find gaps in sequential IDsRead
  12. 012Recursive subquery: find all ancestors of a categoryRead
  13. 013Find products priced above averageRead
  14. 014Find customers who spent more than average customerRead
  15. 015Find departments with no active projectsRead
  16. 016Products never ordered this yearRead
  17. 017Subquery with LIMIT: second highest salary per departmentRead
  18. 018Find the employee who has the most project hours in each deptRead
  19. 019Find customers with orders in consecutive monthsRead
  20. 020Classic: employees earning more than their managerRead
  21. 021Classic: median salary without PERCENTILE_CONTRead