Core SQL: Querying and Manipulating DataLesson 2.4

How to JOIN tables in PostgreSQL: INNER, LEFT, RIGHT, FULL

INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, join condition ON, USING clause, self-join, cross join

Joining Tables

Joins combine rows from two tables based on a condition. Choosing the right join type is critical — the wrong one silently drops or duplicates rows.

INNER JOIN

SELECT e.first_name, d.name AS department
FROM   employees e
INNER JOIN departments d ON e.department_id = d.id;

Returns only rows with a match in both tables. Employees without a department are excluded.

LEFT JOIN

SELECT e.first_name, d.name
FROM   employees e
LEFT JOIN departments d ON e.department_id = d.id;

Returns all employees. Employees with no department get NULL for department columns. Use LEFT JOIN when the left table row should always appear in results.

FULL OUTER JOIN

SELECT e.first_name, d.name
FROM   employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Returns all rows from both tables; unmatched rows get NULLs on the missing side.

Self-join

SELECT e.first_name AS employee,
       m.first_name AS manager
FROM   employees e
LEFT JOIN employees m ON e.manager_id = m.id;

A self-join joins a table to itself using aliases. Common for hierarchical data like org charts or category trees.