Outer Joins: LEFT, RIGHT, and FULL

INNER JOIN only keeps rows that match on both sides. Sometimes you specifically want to see the rows that don't have a match — that's what outer joins are for.

LEFT JOIN

Keeps every row from the left (first) table, matching in data from the right table where it exists, and filling in NULL where it doesn't:

SELECT c.customer_name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Every customer appears at least once — even customers who have never placed an order (their order_id shows as NULL).

Finding rows with no match

A classic use of LEFT JOIN — combine it with WHERE ... IS NULL to find "customers who never ordered anything":

SELECT c.customer_name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

RIGHT JOIN

The mirror image of LEFT JOIN — keeps every row from the right table instead. In practice, most people just flip the table order and use LEFT JOIN instead, since it reads more naturally — and SQLite doesn't support RIGHT JOIN at all.

-- These two queries return the same rows
SELECT * FROM orders o RIGHT JOIN customers c ON o.customer_id = c.id;
SELECT * FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;

FULL OUTER JOIN

Keeps every row from both tables, matched where possible, NULL-filled where not. Supported in PostgreSQL and SQL Server, but not in SQLite or MySQL (you'd emulate it with a UNION of a LEFT JOIN and a RIGHT JOIN).

-- PostgreSQL / SQL Server only
SELECT c.customer_name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

Quick reference

Join type	Keeps
INNER JOIN	Only matching rows on both sides
LEFT JOIN	All left rows, matched or not
RIGHT JOIN	All right rows, matched or not
FULL OUTER JOIN	All rows from both sides

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;