Lesson 4: Joins (INNER/LEFT/RIGHT/FULL) and Common Join Mistakes

Section 3 — Lesson 4: Joins (INNER/LEFT/RIGHT/FULL) and Common Join Mistakes

Welcome back. In Lesson 3, you learned how to summarize data using GROUP BY, HAVING, and aggregates.
To summarize real production data, you almost always need data from more than one table.

That is exactly what joins are for: they let you connect tables using their relationships
(primary keys and foreign keys) and produce a single combined result set.

Joins are also one of the most common places SQL beginners produce incorrect results without realizing it.
A “working query” can still be wrong if it duplicates rows or mismatches relationships.
In this lesson, you will learn joins the DBA way: correctness first, then performance.

---

Lesson description: You will learn how INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN work,
how to choose the correct join type based on the question you are answering,
how primary keys and foreign keys should guide your JOIN conditions,
and how to detect and prevent common mistakes such as accidental cross joins, duplicate row multiplication,
and filters placed in the wrong location (WHERE vs ON).

---

1) The Core Concept: Joins Match Rows Between Tables

A join combines rows from two tables by matching values in related columns.
In a properly designed database, this usually means matching:

• a foreign key column in one table
• to a primary key column in another table

In your lab schema:

• dbo.Booking.CustomerID → dbo.Customer.CustomerID
• dbo.Booking.ServiceID → dbo.Service.ServiceID
• dbo.Payment.BookingID → dbo.Booking.BookingID

DBA rule: Joins are not “guessing columns that look similar.” Joins should follow real relationships.

---

2) INNER JOIN (Return Only Matching Rows)

INNER JOIN returns only rows where a match exists in both tables.
If a booking references a customer, that customer must exist (thanks to FK),
so INNER JOIN is typically safe in that direction.

Example: show bookings with customer and service details

SELECT
    b.BookingID,
    b.ConfirmationCode,
    b.Status,
    b.PickupAt,
    b.TotalAmountUSD,
    c.FullName   AS CustomerName,
    c.Email      AS CustomerEmail,
    s.ServiceName
FROM dbo.Booking AS b
INNER JOIN dbo.Customer AS c
    ON b.CustomerID = c.CustomerID
INNER JOIN dbo.Service AS s
    ON b.ServiceID = s.ServiceID
ORDER BY b.PickupAt ASC;

DBA check: Does every booking have exactly one customer and exactly one service?
In this schema, yes. That means each booking row should produce one output row.

---

3) LEFT JOIN (Return All Rows From the Left, Matched Rows From the Right)

LEFT JOIN is used when you want all rows from the left table,
even if there is no match in the right table.

DBAs use LEFT JOIN for questions like:

• “Show all customers, even those with zero bookings.”
• “Show all bookings, even if no payment exists yet.”

Example: list all customers and how many bookings they have (including zero)

This is a real DBA reporting pattern. Notice the use of LEFT JOIN and GROUP BY.

SELECT
    c.CustomerID,
    c.FullName,
    c.Email,
    COUNT(b.BookingID) AS BookingCount
FROM dbo.Customer AS c
LEFT JOIN dbo.Booking AS b
    ON b.CustomerID = c.CustomerID
GROUP BY
    c.CustomerID, c.FullName, c.Email
ORDER BY
    BookingCount DESC, c.CustomerID ASC;

Important detail: We used COUNT(b.BookingID), not COUNT(*).
If you used COUNT(*), customers with zero bookings would still count as 1 row (because the left-side row exists).
Counting the right-side key avoids that error.

---

4) RIGHT JOIN (Usually Avoided in Professional Work)

RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table and matched rows from the left.

Most DBAs avoid RIGHT JOIN because it reduces readability. Almost every RIGHT JOIN can be rewritten as a LEFT JOIN
by switching table order.

Example: RIGHT JOIN rewritten as LEFT JOIN (preferred)

-- Not recommended style (RIGHT JOIN)
SELECT
    b.BookingID,
    p.PaymentID,
    p.PaymentStatus
FROM dbo.Booking AS b
RIGHT JOIN dbo.Payment AS p
    ON p.BookingID = b.BookingID;

-- Preferred style (LEFT JOIN)
SELECT
    b.BookingID,
    p.PaymentID,
    p.PaymentStatus
FROM dbo.Payment AS p
LEFT JOIN dbo.Booking AS b
    ON p.BookingID = b.BookingID;

DBA standard: Use LEFT JOIN for “include all rows” logic; keep queries easy to read.

---

5) FULL OUTER JOIN (Return All Rows From Both Sides)

FULL OUTER JOIN returns:

• all matching rows (like INNER JOIN), plus
• unmatched rows from the left, plus
• unmatched rows from the right

DBAs use FULL OUTER JOIN for reconciliation questions:

• “Which records exist in system A but not system B?”
• “Which customers exist in staging but not in production?”

With your lab, a common use would be finding bookings without payments and payments without bookings
(payments without bookings should not happen with a correct FK, but it’s a useful concept).

SELECT
    b.BookingID,
    b.ConfirmationCode,
    p.PaymentID,
    p.PaymentStatus
FROM dbo.Booking AS b
FULL OUTER JOIN dbo.Payment AS p
    ON p.BookingID = b.BookingID
WHERE b.BookingID IS NULL
   OR p.PaymentID IS NULL
ORDER BY
    COALESCE(b.BookingID, p.BookingID) ASC;

How to read this result:

• If PaymentID is NULL → booking exists with no payment (normal if unpaid).
• If BookingID is NULL → payment exists with no booking (usually a data integrity problem).

---

6) The Most Common Join Mistakes (And How DBAs Catch Them)

Mistake A: Missing join condition (accidental CROSS JOIN)

If you forget the ON condition, you can multiply every row by every row (cartesian product).
This can explode row counts and crash performance.

-- DANGEROUS: Do not run on real large tables
-- This produces CustomerCount * BookingCount rows.
SELECT c.CustomerID, b.BookingID
FROM dbo.Customer AS c
CROSS JOIN dbo.Booking AS b;

DBA habit: Always join on keys, and validate expected row counts.

Mistake B: Joining on the wrong columns

Joins must align with relationships. If you join on non-key columns (like names),
you can get duplicates, missing matches, and incorrect results.

DBA rule: Use PK/FK joins unless you have a documented reason not to.

Mistake C: Row multiplication (one-to-many joins)

This is one of the biggest real-world sources of bad reports.
Example: A booking can have multiple payment attempts.
If you join Booking to Payment and then COUNT bookings, you may inflate booking counts.

-- This counts payments, not bookings (could be higher than booking count)
SELECT
    b.BookingID,
    COUNT(p.PaymentID) AS PaymentAttempts
FROM dbo.Booking AS b
LEFT JOIN dbo.Payment AS p
    ON p.BookingID = b.BookingID
GROUP BY b.BookingID
ORDER BY PaymentAttempts DESC;

DBA check: Before aggregating, ask:
“Is this relationship one-to-one or one-to-many?”

Mistake D: Filtering the right table in WHERE (turns LEFT JOIN into INNER JOIN)

This mistake is extremely common and very important.

If you LEFT JOIN payments to bookings, but then put a payment condition in the WHERE clause,
you eliminate rows where payment is NULL—meaning you accidentally convert the LEFT JOIN into an INNER JOIN.

Wrong pattern (breaks the LEFT JOIN purpose)

-- WRONG if your goal is "all bookings even without payments"
SELECT
    b.BookingID,
    b.ConfirmationCode,
    p.PaymentStatus
FROM dbo.Booking AS b
LEFT JOIN dbo.Payment AS p
    ON p.BookingID = b.BookingID
WHERE p.PaymentStatus = 'SUCCEEDED';

The WHERE condition removes rows where p.PaymentStatus is NULL, so unpaid bookings disappear.

Correct pattern (filter in the ON clause)

-- Correct: keep all bookings, show only succeeded payments when they exist
SELECT
    b.BookingID,
    b.ConfirmationCode,
    p.PaymentStatus
FROM dbo.Booking AS b
LEFT JOIN dbo.Payment AS p
    ON p.BookingID = b.BookingID
   AND p.PaymentStatus = 'SUCCEEDED'
ORDER BY b.BookingID;

DBA rule: With LEFT JOIN, conditions about the right-side table usually belong in ON,
unless you intentionally want to remove NULL matches.

---

7) DBA Validation Techniques for Joins (How Professionals Confirm Correctness)

7A) Validate expected row counts

If you expect 1 row per booking, confirm it.

-- Should be the same count if the join is truly 1-to-1 from Booking to Customer/Service
SELECT COUNT(*) AS BookingCount
FROM dbo.Booking;

SELECT COUNT(*) AS JoinedCount
FROM dbo.Booking AS b
JOIN dbo.Customer AS c ON b.CustomerID = c.CustomerID
JOIN dbo.Service  AS s ON b.ServiceID  = s.ServiceID;

If JoinedCount is unexpectedly larger, you likely have a join mistake or data duplication in the referenced table.

7B) Look for duplicates created by the join

-- Find bookings that appear more than once after a join (a strong warning sign)
SELECT
    b.BookingID,
    COUNT(*) AS RowOccurrences
FROM dbo.Booking AS b
JOIN dbo.Customer AS c ON b.CustomerID = c.CustomerID
JOIN dbo.Service  AS s ON b.ServiceID  = s.ServiceID
GROUP BY b.BookingID
HAVING COUNT(*) > 1;

DBA note: This technique is used constantly in production troubleshooting.

---

8) Practice (Job-Ready Exercises)

Run these exercises and validate your output carefully:

1. Write an INNER JOIN query that returns bookings with customer name + email and service name.
2. Write a LEFT JOIN query that returns all customers and their BookingCount (including customers with 0 bookings).
3. Write a LEFT JOIN query that returns all bookings and shows PaymentStatus if a payment exists.
4. Demonstrate the “WHERE breaks LEFT JOIN” mistake using PaymentStatus, then fix it by moving the filter into ON.
5. Validate your booking join query returns 1 row per BookingID (use GROUP BY + HAVING COUNT(*) > 1).

---

Lesson Summary

• INNER JOIN returns only matching rows.
• LEFT JOIN returns all left rows plus matching right rows (useful for “include zero” reporting).
• RIGHT JOIN is usually avoided; rewrite as LEFT JOIN for readability.
• FULL OUTER JOIN is used for reconciliation (find mismatches).
• Common mistakes: missing ON (cross join), wrong join keys, row multiplication, and filtering the right table in WHERE.
• DBAs validate joins by checking expected row counts and detecting duplicates.

---

Next Lesson: Subqueries vs Joins (When Each Is Appropriate)

Next, we will deepen join skill with practical patterns: joining multiple tables safely,
self-joins, anti-joins (find missing records), and how DBAs choose join strategy for accuracy and performance.