SQL JOIN Types — INNER, LEFT, RIGHT, FULL, CROSS

Understanding SQL JOINs

JOINs combine rows from two or more tables based on a related column. Choosing the right JOIN type is essential for getting the correct result set.

INNER JOIN

Returns only rows where there is a match in both tables.

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

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right. Non-matching right columns are NULL.

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

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, plus matching rows from the left.

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

FULL OUTER JOIN

Returns rows when there is a match in either table. Non-matching sides are NULL.

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

Note: SQLite does not support RIGHT JOIN or FULL OUTER JOIN.

CROSS JOIN

Returns the Cartesian product — every combination of rows from both tables.

SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;

Self JOIN

Join a table to itself, commonly used for hierarchical data:

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

JOIN Performance Tips

• Always join on indexed columns.
• Use explicit JOIN syntax (JOIN ... ON) rather than implicit joins in WHERE.
• Filter early with WHERE to reduce the join set.