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. This is 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, and your progress syncs across the web and the CoddyKit app. The SQL Academy course includes 4 lessons in total.

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. 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. The SQL Academy course includes 4 lessons in total.

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, so you can start here or from the beginning and move at your own pace. This is lesson 3 of 4.

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