SQL Academy · Lesson

LEFT/RIGHT JOIN and OUTER Joins

Use LEFT JOIN to keep unmatched rows, understand RIGHT and FULL OUTER joins, and detect missing data with WHERE x IS NULL.

LEFT/RIGHT JOIN and OUTER Joins is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

LEFT JOIN: Keep All Left Rows

LEFT JOIN returns every row from the left table, with matched right-side columns or NULL when there's no match.

SELECT u.id, u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Users without orders still appear, with order_id = NULL.

RIGHT JOIN

The mirror of LEFT JOIN — keep all RIGHT rows. Most people just rewrite RIGHT joins as LEFT for readability:

SELECT u.id, u.email, o.id
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;

-- Equivalent to LEFT JOIN with sides swapped:
SELECT u.id, u.email, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

FULL OUTER JOIN

Keep rows from both sides. Unmatched columns are NULL:

SELECT u.email, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

Finding Missing Matches

A LEFT JOIN with WHERE right.id IS NULL finds rows in the left table that have no match on the right — the classic "anti-join":

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;        -- users who have never placed an order

LEFT JOIN with Aggregate

Useful for "0 if missing" reports:

SELECT u.id, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
ORDER BY order_count DESC;

Filter Position Matters

Filters on the right table in WHERE turn a LEFT JOIN back into an INNER JOIN. Put them in ON to preserve LEFT behaviour:

-- Drops users without paid orders entirely:
SELECT u.id, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.id;

-- Keeps users with no paid orders:
SELECT u.id, COUNT(o.id)
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id AND o.status = 'paid'
GROUP BY u.id;

Multiple LEFT JOINs

Chain them. Each LEFT preserves rows from the left of that operator:

SELECT u.email, o.id, p.name
FROM users u
LEFT JOIN orders o   ON o.user_id = u.id
LEFT JOIN products p ON p.id = o.product_id;

LEFT JOIN Performance

LEFT JOIN has a small overhead vs INNER (planner must keep unmatched rows). Often negligible — but if you don't need unmatched rows, INNER JOIN is slightly cheaper.

Outer Join and NULL Output

NULL output from a LEFT JOIN may surprise downstream code. Use COALESCE to provide defaults:

SELECT u.email, COALESCE(SUM(o.total), 0) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.email;

CROSS JOIN — When You Want All Pairs

Joins every row of A with every row of B. Use sparingly:

SELECT d.date, u.id
FROM users u
CROSS JOIN generate_series('2024-01-01', '2024-01-07', INTERVAL '1 day') AS d(date);

Anti-Join Patterns

Three idiomatic ways to find "left without match":

-- LEFT JOIN ... WHERE IS NULL
SELECT u.* FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- NOT EXISTS (often the planner's favourite)
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Recap

OUTER joins keep unmatched rows.

LEFT = all left, NULL right when unmatched
RIGHT = mirror; use LEFT for readability
FULL OUTER = both sides
Anti-join = LEFT JOIN ... WHERE right IS NULL

Quick Check

Which JOIN type returns users even if they have NO orders?

Frequently asked questions

Is the “LEFT/RIGHT JOIN and OUTER Joins” lesson free?

Yes — the full text of “LEFT/RIGHT JOIN and OUTER Joins” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “LEFT/RIGHT JOIN and OUTER Joins”?

Use LEFT JOIN to keep unmatched rows, understand RIGHT and FULL OUTER joins, and detect missing data with WHERE x IS NULL. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “LEFT/RIGHT JOIN and OUTER Joins” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy