JOINs combine relations horizontally. Postgres supports INNER, LEFT, RIGHT, FULL, and CROSS joins—plus LATERAL for correlated subqueries in the FROM clause.
Inner and outer joins
SELECT c.name, o.id AS order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Practice: Seed customers/orders tables from earlier lessons, then run queries in psql or DB Fiddle (PostgreSQL).
LATERAL join preview
SELECT c.name, recent.total
FROM customers c
JOIN LATERAL (
SELECT total FROM orders o
WHERE o.customer_id = c.id
ORDER BY created_at DESC
LIMIT 1
) recent ON true;Join performance
Ensure join keys are indexed. EXPLAIN ANALYZE reveals nested loop vs hash join choices—covered in the advanced module.
Important interview questions and answers
- Q: LEFT JOIN orphan pattern?
A: LEFT JOIN plus WHERE right.id IS NULL finds parents without children. - Q: LATERAL use case?
A: Subquery in FROM referencing prior tables—top-N per group patterns.
Self-check
- How do you list customers with no orders?
- What does LATERAL allow in FROM?
Tip: LEFT JOIN + IS NULL finds orphans—memorize the pattern.
Interview prep
- LATERAL join?
Subquery in FROM referencing prior tables.
- Orphan customers?
LEFT JOIN orders WHERE orders.id IS NULL.