Joins: Inner, Outer, Self, Anti & LATERAL

Combining tables — including the patterns interviews ask about most: self joins, anti joins and top-N per group.

Relational data is split into tables to avoid duplication; joins put it back together for a query. Beyond inner and outer joins, PostgreSQL's LATERAL lets a subquery refer to the current row of the table before it — the cleanest way to get 'the top N of each group'.

Five joins

Inner join: only employees whose department exists and is in Bengaluru.

Left join: Sanjay Gupta (the intern) has no department, so the department side is NULL — coalesce shows '(none)'.

Self join: employees joined to the same table again to find their managers.

Anti join with NOT EXISTS: Test & Validation is the only department without an active project.

CROSS JOIN LATERAL runs the subquery once per project, with LIMIT 2 giving the top two people by hours.

Choosing the join

SELECT e.name FROM employees e LEFT JOIN projects p ON ... WHERE p.status = 'active';

SELECT e.name FROM employees e LEFT JOIN projects p ON ... AND p.status = 'active';

WHERE p.status = ... removes the rows where p is NULL, so the outer join loses its unmatched rows.

WHERE dept_id NOT IN (SELECT dept_id FROM employees) WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id) employees.dept_id contains a NULL (the intern), and x NOT IN (..., NULL) is never true, so the query returns nothing.

How do you find employees whose manager is in a different department?

Self join: SELECT e.name FROM employees e JOIN employees m ON m.emp_id = e.manager_id WHERE m.dept_id IS DISTINCT FROM e.dept_id;

What does LATERAL allow?

A subquery (or function) in FROM may refer to columns of tables that come before it in the same FROM clause, so it can be evaluated per row — e.g. top-N per group.