INNER JOIN and LEFT JOIN — Combining Rows from Multiple Tables

Joins are the mechanism by which relational databases earn the word “relational” — they combine rows from two or more tables based on a shared value, typically a foreign key. Every FastAPI endpoint that returns a post with its author name, a comment with its user, or an order with its line items is using a join under the hood. Understanding the two most common join types — INNER JOIN (only matching rows) and LEFT JOIN (all left-table rows, NULLs for non-matching right rows) — unlocks the ability to write queries that return rich, structured data in a single database round-trip.

INNER JOIN — Only Matching Rows #

-- INNER JOIN returns rows where a match exists in BOTH tables
-- Rows with no match in either table are excluded

-- Get posts with their author's name
SELECT
    p.id          AS post_id,
    p.title,
    p.status,
    u.name        AS author_name,
    u.email       AS author_email
FROM posts p
INNER JOIN users u ON p.author_id = u.id
WHERE p.status = 'published'
ORDER BY p.created_at DESC;

-- "FROM posts p" — 'p' is an alias for posts (shorter to type)
-- "INNER JOIN users u ON p.author_id = u.id" — match condition
-- Only posts where a matching user exists are returned
-- (If a post has author_id=999 and user 999 doesn't exist, that post is excluded)

-- JOIN is shorthand for INNER JOIN
SELECT p.title, u.name
FROM posts p
JOIN users u ON p.author_id = u.id;   -- identical to INNER JOIN

LEFT JOIN — All Left-Table Rows #

-- LEFT JOIN returns ALL rows from the left table
-- For rows with no match in the right table, right-table columns are NULL

-- All users and their post count (including users with no posts)
SELECT
    u.id,
    u.name,
    COUNT(p.id) AS post_count   -- COUNT(p.id) = 0 for users with no posts
FROM users u
LEFT JOIN posts p ON u.id = p.author_id
GROUP BY u.id, u.name
ORDER BY post_count DESC;

-- Why LEFT JOIN here?
-- INNER JOIN would exclude users with no posts
-- LEFT JOIN keeps all users, NULLs appear in p.id for users without posts
-- COUNT(p.id) counts only non-NULL p.id values → gives 0 for users with no posts

-- All posts with their excerpt (or body preview if excerpt is NULL)
SELECT
    p.id,
    p.title,
    u.name                                          AS author_name,
    COALESCE(p.excerpt, LEFT(p.body, 200))          AS display_excerpt
FROM posts p
LEFT JOIN users u ON p.author_id = u.id
WHERE p.status = 'published'
ORDER BY p.published_at DESC NULLS LAST;

-- Multi-level LEFT JOIN: post → author → profile
SELECT
    p.title,
    u.name        AS author_name,
    up.bio        AS author_bio,        -- NULL if no profile row exists
    up.avatar_url AS author_avatar
FROM posts p
LEFT JOIN users u        ON p.author_id = u.id
LEFT JOIN user_profiles up ON u.id = up.user_id
WHERE p.status = 'published';

JOIN with Tags (Many-to-Many) #

-- Get a post with all its tags as an array
SELECT
    p.id,
    p.title,
    ARRAY_AGG(t.name ORDER BY t.name) AS tags
FROM posts p
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t       ON pt.tag_id = t.id
WHERE p.id = 1
GROUP BY p.id, p.title;
-- Returns: id=1, title="Hello World", tags={"fastapi","python"}

-- All published posts with their tag list
SELECT
    p.id,
    p.title,
    p.status,
    COALESCE(ARRAY_AGG(t.name ORDER BY t.name) FILTER (WHERE t.id IS NOT NULL), '{}')
        AS tags
FROM posts p
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t       ON pt.tag_id = t.id
WHERE p.status = 'published'
GROUP BY p.id, p.title, p.status
ORDER BY p.created_at DESC
LIMIT 10;

Common Mistakes #

Mistake 1 — Ambiguous column name in multi-table query #

❌ Wrong — ambiguous which table’s id:

SELECT id, title, name
FROM posts JOIN users ON posts.author_id = users.id;
-- ERROR: column reference "id" is ambiguous (both tables have id)

✅ Correct — qualify with table alias:

SELECT p.id, p.title, u.name
FROM posts p JOIN users u ON p.author_id = u.id;   -- ✓

Mistake 2 — Using COUNT(*) instead of COUNT(joined_column) with LEFT JOIN #

❌ Wrong — users with no posts show count of 1:

SELECT u.name, COUNT(*) FROM users u LEFT JOIN posts p ON u.id = p.author_id
GROUP BY u.id;   -- NULL post row still counted as 1!

✅ Correct:

SELECT u.name, COUNT(p.id) FROM users u LEFT JOIN posts p ON u.id = p.author_id
GROUP BY u.id;   -- ✓ NULL p.id not counted → 0 for users with no posts

Mistake 3 — Forgetting WHERE on one table after LEFT JOIN (acts like INNER JOIN) #

❌ Wrong — WHERE on right table filters out NULL rows (making it like INNER JOIN):

SELECT u.name, p.title
FROM users u LEFT JOIN posts p ON u.id = p.author_id
WHERE p.status = 'published';   -- users with no posts are excluded (p.status IS NULL)!

✅ Correct — move the filter into the JOIN condition:

SELECT u.name, p.title
FROM users u LEFT JOIN posts p ON u.id = p.author_id AND p.status = 'published';
-- ✓ Users with no published posts still appear (p.title will be NULL)

Quick Reference #