انتقل إلى المحتوى
أمين أبوزيد
Englishالعربية
منشورBEG-050-L09

عرض عملي: joins تُسقط الصفوف بصمت

  • الدرس 9 من 9
  • 12 دقيقة قراءة
  • مبتدئ
في هذا الدرس 11 موضوعات

هذا الدرس متاح حاليًا باللغة الإنجليزية فقط، ولم تُنشر نسخته العربية بعد.

Lesson Overview

The most dangerous query is not the one that fails with an error. An error stops you. The dangerous query runs, returns tidy results, and is wrong: a smaller number than the truth, a list missing some people, a report that quietly leaves out the rows that matter most. Inner joins are the main source of such queries.

This demo lesson makes the problem visible. You will take a report that looks right, count in two different ways, see the numbers disagree, and fix it with a left join. You will then learn a short set of habits that catch this class of error before it reaches a user. Like Lesson 8, it prepares you for the module lab.

In the library picture: a librarian asked for "all members and their loans" brings you a list that skips members who never borrowed anything. She was not lying. She answered a different question, and nobody noticed.

This lesson builds on Lesson 6 (inner and left joins), and Lessons 5 and 8 (counting, and checking totals).

Learning Objectives

By the end of this lesson you will be able to:

  • Spot an inner join that is silently dropping rows by comparing counts.
  • Fix such a join by switching to a left join, and check that the fix worked.
  • Explain how a WHERE condition on the right-hand table cancels a left join.
  • Explain why a nullable foreign key makes an inner join risky.
  • Use a short checklist before trusting a join query.
  • Distinguish rows that vanish (inner join) from rows that multiply (several matches).

Written Explanation

Why nothing warns you

An inner join is defined as returning only matched pairs. When a left row has no partner, the database is doing exactly what you asked by leaving it out: no error, no warning, no NULL to notice. The result simply has fewer rows. If you did not know how many to expect, you cannot tell.

A left join defines the opposite: every left row stays, with NULL where there is no partner. A good rule: when you write a join, ask "may a row on this side have no match?" If yes and you still want the row, you need a left join from that side.

The counting check

You can detect most dropped rows with two numbers:

  1. The number of rows in the table that should be fully shown: SELECT COUNT(*) FROM members;
  2. The number of distinct rows of that table in the join result: COUNT(DISTINCT m.id).

If the second is smaller than the first, the join dropped some. COUNT(DISTINCT ...) counts unique values and is used here as a preview; it ignores the repeats that arise when a member has several loans.

The total number of rows in the joined result can also surprise you (it is the number of matches, not the number of members), which is why we compare distinct members rather than raw row counts.

The left join keeps rows only if you let it

Two ways a left join gets quietly turned back into an inner join:

  • A WHERE condition on a right-hand column (WHERE l.loaned_on >= '2025-10-01'). The NULL-extended row cannot satisfy it, so it is removed after the join. Put the condition into the ON clause instead.
  • Joining a further table with an inner join after a left join. If the next join needs a match, every row without one disappears, including the rows the left join had kept.

Nullable foreign keys

A foreign key column declared NOT NULL (like loans.member_id) guarantees a parent exists, so an inner join from child to parent keeps every child. A nullable foreign key does not: rows where it is NULL have no parent, and an inner join drops them. That is a design feature (an optional relationship), but it makes the query a decision: do I want those rows?

Rows that vanish versus rows that multiply

Joins misbehave in two opposite ways:

ProblemCauseSymptom
Rows vanishInner join, or a WHERE on the right table after a left joinFewer rows than expected: members missing
Rows multiplyOne left row matches several right rowsA member listed once per loan; sums and counts inflated

Multiplication is not a bug in the join; it is what a join does. It becomes a bug when you then count or sum as if each left row appeared once. Using COUNT(DISTINCT ...), or grouping, is the fix.

A checklist for any join query

  1. How many rows should the table that must be fully shown have?
  2. Is there a left join where the question says "all", "including", or "even"?
  3. Are conditions on the right-hand table in ON (not WHERE) when a left join is used?
  4. Do any foreign keys in the join allow NULL?
  5. Does COUNT(DISTINCT left.id) match the table's row count?

Examples

All results are real output from PostgreSQL 18.3 on the seeded library (5 members, 8 loans). Example 3 temporarily adds an optional publisher link, as in Lesson 2's publishers table, to books.

Example 1: the numbers disagree (simple)

A report is requested: "all members and their loans". A first attempt uses an inner join:

sql
SELECT COUNT(*) FROM members;
SELECT COUNT(*) FROM members m JOIN loans l ON l.member_id = m.id;
SELECT COUNT(DISTINCT m.id) FROM members m JOIN loans l ON l.member_id = m.id;
text
 count 
-------
     5

 count 
-------
     8

 count 
-------
     4

Five members exist. The joined result has 8 rows (one per loan), which looks reassuring: it is bigger than 5. But only 4 distinct members are in it. The raw row count hid the loss: one member vanished and was masked by the repeats. This is why you compare distinct members, not raw rows.

Example 2: the planned mistake, and the fix (practical)

The report written with the inner join, as the "all members and their loans" answer:

sql
SELECT m.name, l.id AS loan_id
FROM members m
JOIN loans l ON l.member_id = m.id
ORDER BY m.id, l.id;

It returns 8 rows, names Amal, Bilal, Carla, and Dina. Emil is nowhere. Nobody would have noticed, unless someone asked "where is Emil?". The fix is a left join:

sql
SELECT m.name, l.id AS loan_id
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
ORDER BY m.id, l.id;

It returns 9 rows: the same 8, plus one for Emil, with an empty (NULL) loan_id. Verify with the counting check:

sql
SELECT COUNT(DISTINCT m.id) FROM members m LEFT JOIN loans l ON l.member_id = m.id;
text
 count 
-------
     5

All five members are present. Now the second way to lose them: add a right-hand WHERE. "All members, and any loans since 1 October":

sql
SELECT m.name, l.loaned_on
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
WHERE l.loaned_on >= '2025-10-01'
ORDER BY m.id;
text
 name  | loaned_on  
-------+------------
 Amal  | 2025-10-25
 Bilal | 2025-10-01
 Carla | 2025-10-10
 Carla | 2025-10-12
 Dina  | 2025-10-20
(5 rows)

Emil is gone again: his NULL date fails the WHERE. Moving the condition into ON restores him:

sql
SELECT m.name, l.loaned_on
FROM members m
LEFT JOIN loans l ON l.member_id = m.id AND l.loaned_on >= '2025-10-01'
ORDER BY m.id, l.id;

This returned six rows: the five above, plus Emil with an empty date. Note that Amal, with three loans in total, now shows only the one since 1 October, because ON decides which loans are attached, while every member is kept.

Example 3: a nullable foreign key in a realistic report (realistic)

A new requirement: record each book's publisher, but some books have no publisher on file. We add an optional link (the foreign key is nullable, so it can be empty) and fill it for some books:

sql
CREATE TABLE publishers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO publishers (id, name) VALUES (1, 'Orbit Press'), (2, 'Nile House');
ALTER TABLE books ADD COLUMN publisher_id INTEGER REFERENCES publishers (id);
UPDATE books SET publisher_id = 1 WHERE id IN (1, 2, 3, 9);
UPDATE books SET publisher_id = 2 WHERE id IN (4, 5, 6);

The report "all books with their publisher" with an inner join, compared with a left join:

sql
SELECT COUNT(*) FROM books;
SELECT COUNT(*) FROM books b JOIN publishers p ON p.id = b.publisher_id;
SELECT COUNT(*) FROM books b LEFT JOIN publishers p ON p.id = b.publisher_id;
text
11
7
11

(Each query returned a one-row result with that count.) The inner join lost four books, with no error. The left join keeps all eleven and shows the gaps:

sql
SELECT b.title FROM books b
LEFT JOIN publishers p ON p.id = b.publisher_id
WHERE p.id IS NULL ORDER BY b.id;
text
        title         
----------------------
 Kindred
 Parable of the Sower
 Fledgling
 Collected Verses
(4 rows)

This is also a useful data-quality query: "which books lack a publisher?". The silent loss spreads: a chain from loans through books to publishers with inner joins counted only 6 of the 8 loans, because loans of books without publishers disappeared two joins away from the table you were looking at.

Backend Insight

Silent data loss in reports is a classic production bug. Picture a dashboard that reports fewer customers than the company really has, because customers without a matching region record were dropped by an inner join. Nobody sees an error, finance sees a number, and the number is wrong for months.

The defence is cheap and boring: compare a count of the join result with a count from the table that must be complete, and review joins with a specific question, "may a row lack a match?". In code review, "why an inner join here?" is a good question to ask and a good question to answer in a comment. When your Java code later loads related records through a framework, the same question applies to how that framework joins them.

Common Mistakes

Mistake 1: Trusting a result because it "looks plausible"

Eight rows from members and loans looks fine. Only the distinct-member count reveals that one member is missing.

Mistake 2: A right-hand WHERE after a left join

It silently re-creates the inner join. Use ON for conditions on the optional side.

Mistake 3: Inner-joining a further table after a left join

The later inner join removes the rows the left join kept. Use another left join, if those rows must stay.

Mistake 4: Ignoring nullable foreign keys

If a foreign key may be NULL, an inner join on it drops those rows. Decide on purpose.

Mistake 5: Counting joined rows as if they were left rows

A member with three loans appears three times. Count DISTINCT left ids, or group, when you mean "members".

Exercises

Try each exercise yourself before opening the solutions. Use the seeded library.

Level A: Foundation

A1. What does an inner join do with a left row that has no match? What does a left join do?

أظهر الحل A1

A1. An inner join drops the row; a left join keeps it, with NULL in the right-hand columns.

A2. SELECT COUNT(*) FROM members; returns 5 and SELECT COUNT(DISTINCT m.id) FROM members m JOIN loans l ON l.member_id = m.id; returns 4. What does the difference mean?

أظهر الحل A2

A2. One member (Emil) has no loans and is missing from the inner-join result: the inner join dropped a member.

A3. Which side of the relationship must be on the left of LEFT JOIN to keep all books, when joining books and loans?

أظهر الحل A3

A3. books goes on the left: FROM books b LEFT JOIN loans l ON l.book_id = b.id.

Level B: Application

B1. Write "all books and the ids of their loans, including books never loaned". How many rows should the result have, and how do you check that no book is missing? (There are 11 books and 8 loans.)

أظهر الحل B1

B1.

sql
SELECT b.id, b.title, l.id AS loan_id
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
ORDER BY b.id, l.id;

The result has one row per book without loans (4 books) plus one per loan (8), so 12 rows. To check no book is missing: SELECT COUNT(DISTINCT b.id) FROM books b LEFT JOIN loans l ON l.book_id = b.id; should equal SELECT COUNT(*) FROM books; (11).

B2. Rewrite this so that books loaned in or after October 2025 are attached but all books stay: SELECT b.title, l.loaned_on FROM books b LEFT JOIN loans l ON l.book_id = b.id WHERE l.loaned_on >= '2025-10-01';

أظهر الحل B2

B2. Move the date condition into ON:

sql
SELECT b.title, l.loaned_on
FROM books b
LEFT JOIN loans l ON l.book_id = b.id AND l.loaned_on >= '2025-10-01';

B3. Write a query counting books with no loans, using a left join. Compare with the NOT IN version from Lesson 7.

أظهر الحل B3

B3. SELECT COUNT(*) FROM books b LEFT JOIN loans l ON l.book_id = b.id WHERE l.id IS NULL; returns 4, the same number as the four titles from the NOT IN version.

Level C: Challenge

C1. A report counts "number of loans per author" using inner joins from loans through books to authors. Authors without borrowed books are missing. Write the left-join version that lists all authors, with 0 where applicable, and say which table starts the FROM clause.

أظهر الحل C1

C1.

sql
SELECT a.name, COUNT(l.id) AS loans
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY a.id, a.name
ORDER BY a.id;

authors starts the FROM, because every author must appear, and both joins are left joins (an inner join in the second step would drop authors again). The anonymous poet gets 0.

C2. A teammate says, "an inner join is fine here, because every member has loans in the test data". Respond: what is the weakness of the argument, and which small piece of test data would catch the problem?

أظهر الحل C2

C2. The weakness is that the test data does not contain the case that breaks the query: a member with no loans. Real data will. One extra test member with no loans, and a count comparison, would have exposed the bug immediately. (Emil is exactly that member in our seed data.)

Mini Challenge

Without being told the answer, find out whether this query is trustworthy:

sql
SELECT m.name, COUNT(*) AS loans
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;
  1. Run it, and write down what it shows for Emil. Is that correct?
  2. Fix it, and explain the change in one sentence.
  3. Write the counting check that proves all five members are present.

Check your work: the buggy version shows 1 for Emil (the NULL-extended row is counted); the fix is COUNT(l.id), which shows 0; and the check is COUNT(DISTINCT m.id) equal to 5.

Summary & Next Lesson

Key takeaways

  • An inner join silently drops rows with no match; no error or warning appears.
  • Detect it by comparing COUNT(DISTINCT ...) of the left table with the table's own row count.
  • Fix: LEFT JOIN from the table that must be complete, and test with IS NULL to list the gaps.
  • A WHERE on the right table after a left join, or a later inner join, silently undoes it; use ON or more left joins.
  • Nullable foreign keys make inner joins risky.
  • Rule of thumb: ask "may a row have no match?" before you choose the join.

Next lesson

You have finished the lectures for this module. The module lab, Library queries, uses everything from Lesson 1 to here: 25 graded queries against a seeded library database, such as overdue loans, most-borrowed authors, and members with no loans.

Self-check

  1. Why does an inner join give no warning when it drops rows?
  2. Which count shows that a member is missing from a join result?
  3. Why does WHERE l.loaned_on >= ... after a left join remove members without loans?
  4. How does a nullable foreign key affect an inner join?
  5. What is the question to ask before choosing a join?
أظهر الإجابات
  1. Dropping unmatched rows is what an inner join is defined to do, so the result is valid.
  2. COUNT(DISTINCT left.id) compared with the left table's row count.
  3. Members without loans have NULL in loaned_on, which fails the comparison, so their rows are filtered out after the join.
  4. Rows where the key is NULL have no parent and are dropped.
  5. "May a row have no match?"

References

  • PostgreSQL documentation, Table Expressions: Joined Tables. Supports the inner join returning only matching pairs, and the left join always producing at least one row for each left row, with nulls on the right when nothing matches.
  • PostgreSQL documentation, Comparison Functions and Operators. Supports comparisons with NULL yielding null rather than true, which explains why a right-hand WHERE removes unmatched rows.
  • All counts, rows, and results are real output from PostgreSQL 18.3 on the seeded library. The checklist, the vanish-versus-multiply framing, and the dashboard scenario are this course's own illustrative teaching material, not measurements.

التطبيق

طبّق هذا الدرس بمختبر الوحدة وتمارينها واختبارها.

المختبر والتمارين الاختبار

تعليم الدرس كمكتمل اختياري ويمكنك التراجع عنه في أي وقت. يبقى تقدمك محفوظًا في هذا المتصفح.

تابع إلى تدريب الوحدة

اكتب للبحث في المستويات والمراحل والوحدات والدروس.