# فصل ۰۲ — `LEFT JOIN` و سطرهایی که جفت ندارند

<div dir="rtl">

# فصل ۰۲ — `LEFT JOIN` و سطرهایی که جفت ندارند

## در این فصل چه یاد می‌گیری

فصلِ قبل با یک اعتراف تمام شد: شعبهٔ آفتاب از نتیجهٔ `JOIN` بیرون افتاد و هیچ عددی به ما نگفت. حالا می‌شماریمشان.

**۲۰۰ کتاب هرگز امانت نرفته‌اند، ۴۲ عضو هرگز کتابی نبرده‌اند، و ۱ شعبه هیچ کتابی ندارد.** هر سه گروه با `INNER JOIN` بی‌صدا ناپدید می‌شوند — و هر سه گروه دقیقاً همان‌هایی هستند که یک گزارشِ خوب باید دربارهٔ آن‌ها حرف بزند. کتابی که هیچ‌کس نمی‌برد، مسئله است؛ عضوی که هیچ‌وقت برنمی‌گردد، مسئله است.

و بعد یک تله: **پرسشی که با `LEFT JOIN` نوشته شده و یک شرطِ ساده در جای اشتباه، بی‌صدا به `INNER JOIN` برش می‌گرداند.** تفاوتِ دو نسخه در این فصل ۵۱۶ در برابرِ ۱٬۲۰۰ کتاب است.

![دو صافیِ پشتِ سرِ هم که یکی همهٔ دانه‌ها را رد می‌کند و دیگری فقط دانه‌های جفت‌دار را نگه می‌دارد](/lesson-assets/t3-s02-left-join/prompt/01-image-two-gates.png)

آخر این فصل می‌توانی:

- با `LEFT JOIN` تضمین کنی که هیچ سطری از جدولِ چپ گم نمی‌شود
- سطرهایی را که جفت ندارند با `IS NULL` پیدا کنی و بشماری
- بگویی چرا `COUNT(*)` بعد از یک `LEFT JOIN` عددِ غلط می‌دهد
- تشخیص بدهی یک شرط باید در `ON` بنشیند یا در `WHERE`، و تفاوتش را با دو عدد نشان بدهی

## قبل از شروع

**از فصلِ ۱:** `INNER JOIN ... ON`، نامِ مستعارِ جدول، و اینکه `INNER` فقط جفت‌های کامل را نگه می‌دارد.

**از ترمِ ۱ فصلِ ۶:** `NULL` یعنی «نمی‌دانیم»، و `IS NULL` تنها راهِ درستِ پرسیدن از آن است. این فصل همان درس را در جای تازه‌ای به کار می‌گیرد.

**دانه‌بندی:** یک سطر از `loans` یعنی یک بار امانت رفتنِ یک کتاب توسطِ یک عضو. یک کتاب می‌تواند صفر، یک، یا ده‌ها سطر در این جدول داشته باشد.

> 📓 **نوت‌بوک:** [نوت‌بوک این فصل را در Colab باز کن](/notebooks/avand/term-3-joins/session-02-left-join.ipynb) — همهٔ کدهای این فصل آماده و به‌ترتیب داخلش هست.

</div>

| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
| `books` | ۱٬۲۰۰ | کمتر از یک ثانیه |
| `branches` | ۷ | آنی |
| `members` | ۳۰۰ | آنی |
| `loans` | ۳٬۹۹۸ | کمتر از یک ثانیه |

<div dir="rtl">

## ۱. پرسشی که `INNER JOIN` نمی‌تواند جواب بدهد

«هر شعبه چند کتاب دارد؟» — با ابزارِ فصلِ قبل جوابش را می‌گیریم:

</div>

```python
print(q("""
    SELECT br.name, COUNT(*) AS books
    FROM branches AS br
    JOIN books AS b ON b.branch_id = br.id
    GROUP BY br.id, br.name
    ORDER BY books DESC
"""))
```

```text
    name  books
0   کوشا    225
1   نسیم    208
2   پویا    204
3  مرکزی    197
4   دانش    192
5   بهار    174
```

<div dir="rtl">

**شش سطر، در حالی که هفت شعبه داریم.**

این گزارش غلط نیست؛ **ناقص است**، و بدتر از غلط بودن این است که ناقص بودنش از رویش پیدا نیست. کسی که این جدول را می‌بیند فرض می‌کند شش شعبه وجود دارد. شعبهٔ آفتاب — که تازه باز شده و هنوز کتابی ندارد — دقیقاً همان چیزی است که مدیرِ کتابخانه باید ببیند، و دقیقاً همان چیزی است که `INNER JOIN` انداخته بیرون.

**قاعدهٔ سومِ این دوره اینجا صورتِ عملی پیدا می‌کند: قبل از باور، بپرس چه کسی از قلم افتاد.** جوابش را هم می‌شود از خودِ پرسش گرفت، اگر ابزارش را داشته باشی.

## ۲. `LEFT JOIN`: هیچ سطری از جدولِ چپ گم نمی‌شود

`LEFT JOIN` می‌گوید: **همهٔ سطرهای جدولِ چپ را نگه دار.** اگر سطری در جدولِ راست جفت داشت، کنارش بگذار؛ اگر نداشت، ستون‌های راست را `NULL` بگذار و سطر را دور نینداز.

</div>

```python
print(q("""
    SELECT br.name, COUNT(b.id) AS books
    FROM branches AS br
    LEFT JOIN books AS b ON b.branch_id = br.id
    GROUP BY br.id, br.name
    ORDER BY books DESC
"""))
```

```text
    name  books
0   کوشا    225
1   نسیم    208
2   پویا    204
3  مرکزی    197
4   دانش    192
5   بهار    174
6  آفتاب      0
```

<div dir="rtl">

**هفت سطر، و صفرِ آخر یک عددِ واقعی است، نه یک جای خالی.**

«چپ» و «راست» یعنی ترتیبی که در پرسش نوشته‌ای: جدولِ بعد از `FROM` چپ است، جدولِ بعد از `JOIN` راست. **و همین‌جاست که تقارنِ فصلِ قبل از بین می‌رود:** برای `INNER JOIN` جای دو جدول فرقی نمی‌کرد؛ برای `LEFT JOIN` جای دو جدول کلِ پرسش را عوض می‌کند، چون تعیین می‌کند کدام طرف حقِ گم شدن ندارد.

حالا همان کار روی جدولِ بزرگ‌تر. اینجا شمارشِ سطرهای خام گویاتر است:

</div>

```python
inner_rows = int(q("""
    SELECT COUNT(*) AS n FROM books AS b JOIN loans AS l ON l.book_id = b.id
""").iloc[0, 0])
left_rows = int(q("""
    SELECT COUNT(*) AS n FROM books AS b LEFT JOIN loans AS l ON l.book_id = b.id
""").iloc[0, 0])

print("سطرهای loans           :", int(q("SELECT COUNT(*) AS n FROM loans").iloc[0, 0]))
print("سطرهای نتیجهٔ INNER JOIN:", inner_rows)
print("سطرهای نتیجهٔ LEFT JOIN :", left_rows)
print("اختلاف                 :", left_rows - inner_rows)
```

```text
سطرهای loans           : 3998
سطرهای نتیجهٔ INNER JOIN: 3998
سطرهای نتیجهٔ LEFT JOIN : 4198
اختلاف                 : 200
```

<div dir="rtl">

**اختلاف دقیقاً ۲۰۰ سطر است، و این عدد شمارشِ دومِ ماست:** هر کتابی که هیچ امانتی ندارد، در نتیجهٔ `LEFT JOIN` دقیقاً یک سطر می‌گیرد — سطری با ستون‌های خالی. پس ۴٬۱۹۸ منهای ۳٬۹۹۸ باید برابرِ تعدادِ کتاب‌های بی‌امانت باشد. بخشِ ۴ همان عدد را از راهِ کاملاً دیگری هم می‌گیرد.

> ✅ **چک کن:** `INNER JOIN` اینجا دقیقاً ۳٬۹۹۸ سطر داد، یعنی برابرِ کلِ `loans`. این اتفاقی نیست: هر سطرِ `loans` به یک کتابِ موجود اشاره می‌کند، پس هیچ امانتی گم نشد. اگر عددِ تو کمتر بود، یعنی سطرهایی در `loans` هست که به کتابی اشاره می‌کنند که وجود ندارد — و آن مسئلهٔ دیگری است که ترمِ ۴ نامش را می‌گذارد.

## ۳. تلهٔ `COUNT(*)` بعد از `LEFT JOIN`

در بخشِ ۲ نوشتیم `COUNT(b.id)`، نه `COUNT(*)`. این انتخاب نبود، اجبار بود:

</div>

```python
print(q("""
    SELECT b.id,
           COUNT(*)     AS with_star,
           COUNT(l.id)  AS with_column
    FROM books AS b
    LEFT JOIN loans AS l ON l.book_id = b.id
    WHERE b.id IN (?, ?)
    GROUP BY b.id
""", (2, 14)))
```

```text
   id  with_star  with_column
0   2          1            0
1  14          1            0
```

<div dir="rtl">

**این دو کتاب هرگز امانت نرفته‌اند، و `COUNT(*)` برایشان ۱ گفت.**

دلیلش دقیقاً همان تعریفِ ترمِ ۱ است: `COUNT(*)` **سطرها** را می‌شمارد، و `LEFT JOIN` برای این کتاب یک سطر ساخته — سطری که تمامِ ستون‌های سمتِ راستش `NULL` است. `COUNT(l.id)` سطرهایی را می‌شمارد که در آن ستون **مقدار دارند**، و اینجا هیچ‌کدام ندارند.

**این خطا هیچ پیامی ندارد.** یک گزارشِ «کم‌محبوب‌ترین کتاب‌ها» که با `COUNT(*)` نوشته شده باشد، هیچ کتابی با صفر امانت پیدا نمی‌کند — چون همه‌شان یک شده‌اند. **بعد از هر `LEFT JOIN`، ستونی از جدولِ راست را بشمار، نه ستاره را.**

## ۴. الگوی «آنچه نیست»

اگر بعد از یک `LEFT JOIN` فقط سطرهایی را نگه داری که سمتِ راستشان `NULL` است، دقیقاً همان‌هایی می‌مانند که **جفت ندارند**. این پرکاربردترین الگوی این فصل است:

</div>

```python
never_borrowed = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b
    LEFT JOIN loans AS l ON l.book_id = b.id
    WHERE l.id IS NULL
""").iloc[0, 0])
quiet_members = int(q("""
    SELECT COUNT(*) AS n
    FROM members AS m
    LEFT JOIN loans AS l ON l.member_id = m.id
    WHERE l.id IS NULL
""").iloc[0, 0])
empty_branches = int(q("""
    SELECT COUNT(*) AS n
    FROM branches AS br
    LEFT JOIN books AS b ON b.branch_id = br.id
    WHERE b.id IS NULL
""").iloc[0, 0])

print("کتاب‌هایی که هرگز امانت نرفته‌اند:", never_borrowed, "از 1200")
print("اعضایی که هرگز کتابی نبرده‌اند  :", quiet_members, "از 300")
print("شعبه‌هایی که هیچ کتابی ندارند   :", empty_branches, "از 7")
```

```text
کتاب‌هایی که هرگز امانت نرفته‌اند: 200 از 1200
اعضایی که هرگز کتابی نبرده‌اند  : 42 از 300
شعبه‌هایی که هیچ کتابی ندارند   : 1 از 7
```

<div dir="rtl">

**۲۰۰ همان عددی است که در بخشِ ۲ از تفریقِ ۴٬۱۹۸ و ۳٬۹۹۸ درآمد** — دو مسیرِ کاملاً مستقل، یک جواب. این شمارشِ دوم است و بدونِ آن هیچ‌کدام از این سه عدد را نباید باور کنی.

**و توجه کن این سه عدد چه چیزی هستند: هر سه دربارهٔ چیزی حرف می‌زنند که در جدولِ `loans` وجود ندارد.** ۴۲ عضوی که هرگز کتابی نبردند، در هیچ سطری از `loans` نیستند؛ برای پیدا کردنشان باید از جدولی شروع می‌کردی که هستند.

## ۵. شرطی که `LEFT` را بی‌صدا به `INNER` تبدیل می‌کند

حالا مهم‌ترین بخشِ فصل. سه کتاب و سه امانت، جوری که با انگشت شمردنی باشد:

</div>

```python
con.execute("DROP TABLE IF EXISTS tiny_books")
con.execute("DROP TABLE IF EXISTS tiny_loans")
con.execute("CREATE TABLE tiny_books (id INTEGER PRIMARY KEY, title TEXT)")
con.execute("CREATE TABLE tiny_loans (id INTEGER PRIMARY KEY, book_id INTEGER, loaned_at TEXT)")
con.executemany("INSERT INTO tiny_books VALUES (?, ?)",
                [(1, "الف"), (2, "ب"), (3, "پ")])
con.executemany("INSERT INTO tiny_loans VALUES (?, ?, ?)",
                [(1, 1, "2025-03-02"), (2, 1, "2024-05-11"), (3, 2, "2024-08-20")])
con.commit()

print(q("""
    SELECT t.title, n.loaned_at
    FROM tiny_books AS t
    LEFT JOIN tiny_loans AS n ON n.book_id = t.id
    ORDER BY t.id, n.id
"""))
```

```text
  title   loaned_at
0   الف  2025-03-02
1   الف  2024-05-11
2     ب  2024-08-20
3     پ         NaN
```

<div dir="rtl">

چهار سطر: الف دو بار، ب یک بار، و پ با یک `NaN` — این همان `NULL`ِ پایگاه داده است. `loaned_at` ستونِ متنی است و نسخه‌های تازهٔ `pandas` در چنین ستونی هم `NaN` چاپ می‌کنند؛ نسخه‌های قدیمی‌تر `None` نشان می‌دهند. معنی هر دو یکی است.

حالا پرسشِ کاملاً معقولِ «امانت‌های سالِ ۲۰۲۵ را نشانم بده» را به همین اضافه کن — یک بار در `WHERE` و یک بار در `ON`:

</div>

```python
in_where = q("""
    SELECT t.title, n.loaned_at
    FROM tiny_books AS t
    LEFT JOIN tiny_loans AS n ON n.book_id = t.id
    WHERE n.loaned_at >= ?
    ORDER BY t.id
""", ("2025-01-01",))
in_on = q("""
    SELECT t.title, n.loaned_at
    FROM tiny_books AS t
    LEFT JOIN tiny_loans AS n ON n.book_id = t.id AND n.loaned_at >= ?
    ORDER BY t.id
""", ("2025-01-01",))

print("شرط در WHERE —", len(in_where), "سطر")
print(in_where)
print()
print("شرط در ON    —", len(in_on), "سطر")
print(in_on)
```

```text
شرط در WHERE — 1 سطر
  title   loaned_at
0   الف  2025-03-02

شرط در ON    — 3 سطر
  title   loaned_at
0   الف  2025-03-02
1     ب         NaN
2     پ         NaN
```

<div dir="rtl">

**یک سطر در برابرِ سه سطر. جای یک شرط عوض شد، و دو کتاب از سه کتاب ناپدید شدند.**

مکانیزمش دقیقاً همان منطقِ سه‌ارزشیِ ترمِ ۱ است. `LEFT JOIN` برای کتابِ «پ» سطری ساخت که `loaned_at`ش `NULL` است. بعد `WHERE` پرسید «آیا این `NULL` از ۲۰۲۵ بزرگ‌تر است؟» و جواب **نامعلوم** بود — نه درست. `WHERE` سطرهایی را نگه می‌دارد که شرطشان **درست** است، پس «پ» افتاد بیرون. کتابِ «ب» هم افتاد چون امانتش مالِ سالِ قبل بود.

**و نتیجه‌اش این است: یک `LEFT JOIN` که شرطی روی جدولِ راستش در `WHERE` دارد، دقیقاً یک `INNER JOIN` است — با ظاهری که خلافش را می‌گوید.**

روی جدولِ واقعی، هزینه‌اش این است. **دقت کن `COUNT(DISTINCT b.id)` نوشتیم، نه `COUNT(*)`:** یک کتاب می‌تواند چند بار امانت رفته باشد و در نتیجهٔ این پیوند چند سطر داشته باشد، و ما داریم **کتاب** می‌شماریم نه سطر. فصلِ بعد کاملاً دربارهٔ همین است.

</div>

```python
where_version = int(q("""
    SELECT COUNT(DISTINCT b.id) AS n
    FROM books AS b
    LEFT JOIN loans AS l ON l.book_id = b.id
    WHERE l.loaned_at >= ?
""", ("2025-01-01",)).iloc[0, 0])
on_version = int(q("""
    SELECT COUNT(DISTINCT b.id) AS n
    FROM books AS b
    LEFT JOIN loans AS l ON l.book_id = b.id AND l.loaned_at >= ?
""", ("2025-01-01",)).iloc[0, 0])

print("کتاب‌هایی که در گزارش دیده می‌شوند — شرط در WHERE:", where_version)
print("کتاب‌هایی که در گزارش دیده می‌شوند — شرط در ON   :", on_version)
print("کتاب‌هایی که فقط در نسخهٔ دوم دیده می‌شوند       :", on_version - where_version)
```

```text
کتاب‌هایی که در گزارش دیده می‌شوند — شرط در WHERE: 516
کتاب‌هایی که در گزارش دیده می‌شوند — شرط در ON   : 1200
کتاب‌هایی که فقط در نسخهٔ دوم دیده می‌شوند       : 684
```

<div dir="rtl">

**۶۸۴ کتاب از گزارشِ «امانت‌های امسال» غایب‌اند، و دقیقاً همان‌هایی هستند که امسال امانت نرفته‌اند.** یعنی گزارشی که قرار بود کم‌کارترین کتاب‌ها را نشان بدهد، همان‌ها را حذف کرده.

> 🔧 **اگر کار نکرد:** یک اشتباهِ رایج در همین ساختار خطا **می‌دهد** و خوش‌شانسی است که می‌دهد — وقتی شرط را در `ON` بگذاری ولی نامِ مستعار را جا بیندازی:

</div>

```python
try:
    q("""
        SELECT t.title, n.loaned_at
        FROM tiny_books AS t
        LEFT JOIN tiny_loans AS n ON n.book_id = t.id AND loan_date >= ?
    """, ("2025-01-01",))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT t.title, n.loaned_at
        FROM tiny_books AS t
        LEFT JOIN tiny_loans AS n ON n.book_id = t.id AND loan_date >= ?
    ': no such column: loan_date
```

<div dir="rtl">

**ولی خطای واقعیِ این فصل همان ۵۱۶ است که هیچ پیامی نداشت.**

## ۶. پس شرط را کجا بگذارم

قاعده یک جمله است و از خودِ معنیِ دو بند بیرون می‌آید. **`ON` می‌گوید کدام سطرها با هم جفت‌اند؛ `WHERE` می‌گوید کدام سطرها در نتیجه بمانند.**

- شرط دربارهٔ **جدولِ چپ** است؟ در `WHERE` بگذار. سطرهای چپی که شرط را ندارند، اصلاً نباید در گزارش باشند.
- شرط دربارهٔ **جدولِ راست** است و می‌خواهی سطرهای بی‌جفت بمانند؟ در `ON` بگذار.
- شرط دربارهٔ **جدولِ راست** است و واقعاً می‌خواهی سطرهای بی‌جفت بیفتند؟ در `WHERE` بگذار — **ولی آن‌وقت صادقانه‌تر است که همان اول `INNER JOIN` بنویسی**، چون همان است و خواننده‌ات گمراه نمی‌شود.

و یک استثنای مهم که در بخشِ ۴ از آن استفاده کردیم: `WHERE l.id IS NULL` هم شرطی روی جدولِ راست در `WHERE` است، و **عمداً** همان کار را می‌کند. تفاوتش این است که آن شرط فقط برای سطرهای بی‌جفت درست است، پس به‌جای اینکه `LEFT` را به `INNER` تبدیل کند، دقیقاً برعکسش می‌کند: فقط سطرهای بی‌جفت را نگه می‌دارد. **همان ابزار، همان جا، دو نتیجهٔ متضاد — و تفاوت در این است که شرط با `NULL` چه می‌کند.**

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در جدولِ بخشِ ۲ یک سطر یعنی یک شعبه؛ در نتیجهٔ خامِ `LEFT JOIN` بخشِ ۲ یک سطر یعنی «یک امانت، یا یک کتابِ بی‌امانت» — دو چیزِ متفاوت در یک ستون، و دقیقاً به همین دلیل `COUNT(*)` رویش بی‌معنی است. **با کدام شمارشِ دوم سنجیدی؟** عددِ ۲۰۰ را دو بار گرفتیم: یک بار از تفریقِ ۴٬۱۹۸ و ۳٬۹۹۸، و یک بار مستقیم با `WHERE l.id IS NULL`. **چه کسی از قلم افتاد؟** در نسخهٔ `WHERE`ی بخشِ ۵، ۶۸۴ کتاب — و هیچ‌کدام از عددهای آن گزارش این را لو نمی‌داد.

> 🤖 **از دستیارت بپرس:** «چرا یک شرط روی جدولِ راست در `WHERE`، یک `LEFT JOIN` را به `INNER JOIN` تبدیل می‌کند؟» بعد این را بپرس: **«اگر شرط `IS NULL` باشد چه؟ آن هم `LEFT` را به `INNER` تبدیل می‌کند؟»** — جوابِ درست می‌گوید نه، و دلیلش را به این برمی‌گرداند که این تنها شرطی است که برای سطرهای بی‌جفت **درست** است. اگر جواب فقط قاعده‌ای تکرار کرد و به `NULL` اشاره نکرد، ناقص است.

## واژه‌های تازهٔ این فصل

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| LEFT JOIN | لفت جوین | همهٔ سطرهای جدولِ چپ می‌مانند، جفت داشته باشند یا نه |
| outer join | آوتر جوین | نامِ خانوادگیِ پیوندهایی که سطرِ بی‌جفت را نگه می‌دارند |
| anti-join | آنتی‌جوین | الگوی «آنچه نیست»: `LEFT JOIN` به‌علاوهٔ `IS NULL` |
| unmatched row | آن‌مچد رو | سطری که در طرفِ دیگر هیچ جفتی ندارد |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا `COUNT(*)` بعد از یک `LEFT JOIN` برای کتابِ بی‌امانت عددِ ۱ داد؟

**تمرینِ اجباری:** فهرستِ اعضایی را دربیاور که هرگز کتابی نبرده‌اند، با نام و شهرشان. **بعد دو عدد چاپ کن: تعدادِ این اعضا، و تعدادِ اعضایی که دستِ‌کم یک بار کتاب برده‌اند. جمعشان باید دقیقاً ۳۰۰ شود.** اگر نشد، شرطت `IS NULL` نیست.

**تمرینِ ستاره‌دار ⭐:** پرسشِ «هر شعبه چند امانت داشته؟» را بنویس — سه جدول لازم دارد و باید شعبهٔ بی‌کتاب هم در نتیجه بماند. **بعد سؤالِ سخت را جواب بده: ستونِ امانت‌ها را با `COUNT(*)` بگیری چه می‌شود و با `COUNT(l.id)` چه می‌شود؟ هر دو را چاپ کن.** و بعد بگو **جمعِ کدام ستون باید برابرِ ۳٬۹۹۸ باشد و چرا آن یکی نیست.**

## در فصل بعد

در همین فصل یک عدد بی‌سروصدا از کنارمان رد شد: پیوندِ `books` به `loans` نتیجه‌ای با ۴٬۱۹۸ سطر ساخت، در حالی که `books` فقط ۱٬۲۰۰ سطر دارد. تعدادِ سطرها **بیشتر** شد و ما فقط از آن برای شمردنِ کتاب‌های بی‌امانت استفاده کردیم.

فصلِ بعد نشان می‌دهد همان اتفاق چطور یک `SUM` را خراب می‌کند: **جمعِ صفحاتِ این کتابخانه ۵۸۸٬۰۴۹ است و بعد از یک `JOIN` می‌شود ۱٬۹۴۶٬۱۰۳** — بدونِ هیچ خطایی، و با رتبه‌بندیِ شعبه‌ها که کاملاً وارونه می‌شود.

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t3/s02-left-join (یادستان, Yaadestan)
