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.

07

Chapter 7

JOINs (INNER, LEFT, RIGHT, FULL)

Combining tables. Inner, left, right, full, self, and the cross — knowing which one to reach for is most of the craft.

In this chapter

32

queries, easy → advanced

  1. 001INNER JOIN: employees with their department namesRead
  2. 002LEFT JOIN: all employees even if no departmentRead
  3. 003Get all products with their category namesRead
  4. 004Get all orders with customer namesRead
  5. 005Get order items with product names and order infoRead
  6. 006Self JOIN: find employee and their manager nameRead
  7. 007RIGHT JOIN: show all departments even without employeesRead
  8. 008FULL OUTER JOIN: all employees and departmentsRead
  9. 009CROSS JOIN: all employee-project combinationsRead
  10. 0104-table JOIN: order details with all infoRead
  11. 011Find employees assigned to projects with JOINRead
  12. 012Find departments with their total project budgetRead
  13. 013Employees NOT assigned to any project (anti-join)Read
  14. 014Find top customer per country using JOINRead
  15. 0155-table JOIN: full order reportRead
  16. 016Find employees who share same department AND managerRead
  17. 017LATERAL JOIN: employee with last salary changeRead
  18. 018NATURAL JOIN vs explicit JOIN (know the difference)Read
  19. 019Find products never ordered (LEFT anti-join)Read
  20. 020Recursive self-join: full org chart hierarchyRead
  21. 021Hierarchical query: find all subordinates of a managerRead
  22. 022Join employees with departments to get locationRead
  23. 023Get order total per customer with nameRead
  24. 024Find all items from a specific orderRead
  25. 025Get employee count and avg salary per locationRead
  26. 026Find customers who ordered product from specific categoryRead
  27. 027Employee project summary with total hoursRead
  28. 028Compare two periods: YoY revenue by categoryRead
  29. 029Employee who has worked on most projects in their departmentRead
  30. 030Sales report: customer, product, category, repRead
  31. 031Classic: consecutive login days per userRead
  32. 032USING clause in JOIN for cleaner syntaxRead