INNER JOIN and OUTER JOINs — Combining Related Tables

JOINs combine rows from two or more tables based on related columns. The join type determines which rows appear in the result when there is no matching row in one of the tables. INNER JOIN is the strictest — both sides must match. LEFT JOIN is the most common for optional relationships — all rows from the left table, matched right-side data where it exists and NULL where it does not. Understanding which join type to use for each relationship prevents the most common SQL query bugs: missing rows (wrong join type) and unexpected NULLs (forgotten outer join).

JOIN Types with BlogApp Examples #

-- ── INNER JOIN — only rows that match on BOTH sides ────────────────────────
-- Returns only posts that have an author (required FK — always matches)
SELECT p.Id, p.Title, u.DisplayName AS Author
FROM   dbo.Posts p
INNER  JOIN dbo.Users u ON u.Id = p.AuthorId;
-- If any post had an AuthorId pointing to a non-existent user,
-- that post would be EXCLUDED from the result.

-- ── LEFT (OUTER) JOIN — all left rows, NULLs for missing right matches ────
-- Returns ALL posts, including those with no category (CategoryId IS NULL)
SELECT
    p.Id,
    p.Title,
    c.Name AS CategoryName,   -- NULL if post has no category
    c.Slug AS CategorySlug
FROM   dbo.Posts p
LEFT   JOIN dbo.Categories c ON c.Id = p.CategoryId;

-- ── Filtering on a LEFT JOINed table — must use IS NULL, not <> ──────────
-- Posts WITH a category (CategoryId IS NOT NULL equivalent via JOIN):
SELECT p.Id, p.Title
FROM   dbo.Posts p
LEFT   JOIN dbo.Categories c ON c.Id = p.CategoryId
WHERE  c.Id IS NOT NULL;   -- only rows where join succeeded

-- Posts WITHOUT a category (anti-join pattern):
SELECT p.Id, p.Title
FROM   dbo.Posts p
LEFT   JOIN dbo.Categories c ON c.Id = p.CategoryId
WHERE  c.Id IS NULL;       -- only rows where no match found

-- ── FULL OUTER JOIN — all rows from both sides, NULLs for non-matches ─────
-- All users and all posts — even users with no posts and posts with no user
SELECT u.DisplayName, p.Title
FROM   dbo.Users u
FULL   OUTER JOIN dbo.Posts p ON p.AuthorId = u.Id;

-- ── Multi-table join: post + author + category + tags ─────────────────────
SELECT
    p.Id,
    p.Title,
    p.Slug,
    u.DisplayName   AS AuthorName,
    c.Name          AS CategoryName,
    t.Name          AS TagName
FROM   dbo.Posts      p
INNER  JOIN dbo.Users u  ON u.Id  = p.AuthorId
LEFT   JOIN dbo.Categories c ON c.Id  = p.CategoryId
LEFT   JOIN dbo.PostTags pt  ON pt.PostId = p.Id
LEFT   JOIN dbo.Tags     t   ON t.Id      = pt.TagId
WHERE  p.IsPublished = 1;
-- Note: a post with 3 tags returns 3 rows — use STRING_AGG to collapse

-- ── Self-join — category hierarchy ───────────────────────────────────────
-- If Categories had a ParentId column:
SELECT
    child.Name  AS ChildCategory,
    parent.Name AS ParentCategory
FROM   dbo.Categories child
LEFT   JOIN dbo.Categories parent ON parent.Id = child.ParentId;

Common Mistakes #

Mistake 1 — Filtering on LEFT JOIN column in WHERE (converts to INNER JOIN) #

❌ Wrong — LEFT JOIN Categories c ... WHERE c.Name = 'Tech'; posts with no category are excluded.

✅ Correct — move the filter to ON: LEFT JOIN Categories c ON c.Id = p.CategoryId AND c.Name = 'Tech'.

Mistake 2 — INNER JOIN where LEFT JOIN is needed (missing rows) #

❌ Wrong — INNER JOIN Categories c ON c.Id = p.CategoryId; posts with NULL CategoryId are excluded.

✅ Correct — LEFT JOIN Categories c ON c.Id = p.CategoryId; all posts returned, category NULL when unset.