# فصل ۰۱ — چرا داده در یک جدول نمی‌ماند، و اولین `JOIN`

<div dir="rtl">

# فصل ۰۱ — چرا داده در یک جدول نمی‌ماند، و اولین `JOIN`

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

یک پرسشِ کاملاً معمولی: **شعبهٔ کوشا چند صندلی دارد؟**

جدولی که جلوی ماست دو جواب می‌دهد. **۱۲۷ سطر می‌گویند ۶۰ و ۹۸ سطر می‌گویند ۹۶.** هیچ‌کدام خطا نمی‌دهند، هیچ‌کدام هشدار نمی‌دهند، و اگر پرسشت اتفاقاً سراغِ یکی از آن دو دسته برود، عددی می‌گیری که کاملاً قابلِ باور است و کاملاً هم ممکن است غلط باشد.

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

![چند کارتِ یک‌شکل که هرکدام یک برچسبِ تکراری دارند، در کنارِ یک لوحِ واحد که همان برچسب فقط یک بار رویش نوشته شده](/lesson-assets/t3-s01-inner-join/prompt/01-image-one-fact-many-copies.png)

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

- بگویی چرا نگه داشتنِ یک واقعیت در هزار سطر دیر یا زود به دو جوابِ متناقض می‌رسد
- دو جدول را با `INNER JOIN ... ON` کنارِ هم بیاوری
- با نامِ مستعارِ جدول، ابهامِ نامِ ستون را حل کنی و پیامِ خطایش را بخوانی
- ثابت کنی که یک `JOIN` مشخص، تعدادِ سطرها را عوض **نکرده**

## قبل از شروع

**از ترمِ ۱ و ۲:** `SELECT` و `WHERE` و `GROUP BY`، `COUNT(*)` و `COUNT(DISTINCT ...)` و `SUM`، `DISTINCT`، و اینکه هر مقدار با پارامترِ `?` به پرسش می‌رود.

**هیچ آشناییِ قبلی با `JOIN` فرض نشده.** تا امروز هر پرسشی که نوشتی یک `FROM` داشت با یک نامِ جدول. از این فصل به بعد بیشتر از یکی.

**دانه‌بندیِ جدول‌هایی که با آن‌ها کار می‌کنیم:** یک سطر از `books` یعنی یک عنوانِ ثبت‌شده در یکی از شعبه‌ها. یک سطر از `branches` یعنی یک شعبه. یک سطر از `loans` یعنی یک بار امانت رفتنِ یک کتاب.

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

</div>

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

<div dir="rtl">

**این همان کتابخانهٔ ترمِ ۱ است، این بار در چند جدول و با یک برشِ تازه از داده** — پس عددهای کلی با ترمِ ۱ یکی نیستند و هیچ ادعایی در این ترم به عددهای آنجا گره نمی‌خورد. داده ساختگی است و با یک `SEED` ثابت ساخته می‌شود، پس روی هر ماشینی همان عددها را می‌گیری.

## ۱. یک جدولِ پهن، و دو جوابِ متناقض

`books_wide` همان چیزی است که آدم اولین بار می‌سازد: یک جدول که **همه‌چیز** در آن است — مشخصاتِ کتاب، و کنارش مشخصاتِ شعبه‌ای که کتاب در آن است.

</div>

```python
print(q("SELECT id, title, branch_name, branch_seats FROM books_wide LIMIT 3"))
```

```text
   id           title branch_name  branch_seats
0   1       روزهای پل        نسیم           120
1   2  خانه پشت باران        پویا            45
2   3         شب و مه        دانش            90
```

<div dir="rtl">

خواندنش راحت است و برای یک نگاهِ سریع هم بد نیست. مشکل جای دیگری است: **`branch_seats` یک واقعیت دربارهٔ شعبه است، نه دربارهٔ کتاب — و اینجا به‌ازای هر کتاب یک بار نوشته شده.**

شعبهٔ کوشا سالِ گذشته سالنِ تازه‌ای اضافه کرد و صندلی‌هایش از ۶۰ به ۹۶ رسید. کسی که این جدول را نگه می‌داشت، سطرهایی را که آن روز جلوی چشمش بود به‌روز کرد. حالا ببین چه چیزی در جدول مانده:

</div>

```python
print(q("""
    SELECT branch_name, branch_seats, COUNT(*) AS rows_saying_so
    FROM books_wide
    WHERE branch_name = ?
    GROUP BY branch_name, branch_seats
""", ("کوشا",)))
```

```text
  branch_name  branch_seats  rows_saying_so
0        کوشا            60             127
1        کوشا            96              98
```

<div dir="rtl">

**یک شعبه، دو عددِ صندلی، و جدول هیچ نظری ندارد کدام درست است.**

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

و همان مکانیزم، یک عددِ دیگر را هم خراب کرده. بشمار چند شهرِ متمایز در این جدول هست:

</div>

```python
print("شهرهای متمایز در books_wide:",
      int(q("SELECT COUNT(DISTINCT branch_city) AS n FROM books_wide").iloc[0, 0]))
print("شهرهای متمایز در branches  :",
      int(q("SELECT COUNT(DISTINCT city) AS n FROM branches").iloc[0, 0]))
print()
print(q("SELECT branch_city, COUNT(*) AS n FROM books_wide GROUP BY branch_city ORDER BY n DESC"))
```

```text
شهرهای متمایز در books_wide: 9
شهرهای متمایز در branches  : 7

  branch_city    n
0      اصفهان  225
1        مشهد  208
2    کرمانشاه  199
3       تهران  197
4       شیراز  187
5       تبریز  168
6       تبريز    6
7    كرمانشاه    5
8       شيراز    5
```

<div dir="rtl">

**نُه شهر، در حالی که این کتابخانه شش شهر دارد که کتاب در آن‌ها هست.**

سه سطرِ آخر همان نام‌هایند با نویسهٔ عربی — همان تلهٔ فصلِ ۴ ترمِ ۱، این بار در نامِ شهر. شانزده سطر از ۱٬۲۰۰. **و دقیقاً به همین دلیل که نامِ شهر ۱٬۲۰۰ بار تایپ شده، ۱٬۲۰۰ فرصت برای غلط تایپ کردنش هم وجود داشته.**

> ✅ **چک کن:** جمعِ ستونِ `n` باید دقیقاً ۱۲۰۰ شود. اگر نشد، پرسش را عوض کرده‌ای؛ اگر شد، مطمئنی که هیچ سطری از قلم نیفتاده و آن نُه دسته واقعاً کلِ جدول‌اند.

## ۲. درمان: هر واقعیت فقط یک جا

راهِ درست این است که **واقعیت‌های شعبه در جدولِ شعبه‌ها بنشینند و در جدولِ کتاب‌ها فقط یک اشاره به آن‌ها بماند.**

</div>

```python
print(q("SELECT id, name, city, seats FROM branches"))
print()
print(q("SELECT id, title, branch_id, pages FROM books LIMIT 3"))
```

```text
   id   name      city  seats
0   1  مرکزی     تهران    240
1   2   نسیم      مشهد    120
2   3   دانش     شیراز     90
3   4   بهار     تبریز     75
4   5   کوشا    اصفهان     96
5   6   پویا  کرمانشاه     45
6   7  آفتاب       یزد     30

   id           title  branch_id  pages
0   1       روزهای پل          2    311
1   2  خانه پشت باران          6    206
2   3         شب و مه          3    762
```

<div dir="rtl">

حالا نامِ هر شهر **یک بار** نوشته شده، نه ۱٬۲۰۰ بار. اگر کوشا فردا صندلی اضافه کند، یک سطر عوض می‌شود و کارِ تمام است. غلطِ تایپی هم اگر پیش بیاید، در یک سطر است و همان‌جا دیده می‌شود.

دو نامِ فنی که از همین‌جا شروع می‌شوند و **ترمِ ۴ کاملشان می‌کند** — فعلاً همین‌قدر بدان:

- **کلیدِ اصلی:** ستونی که هر سطر را یکتا می‌کند. در `branches` این `id` است.
- **کلیدِ خارجی:** ستونی که در جدولِ دیگری به آن کلیدِ اصلی اشاره می‌کند. در `books` این `branch_id` است.

`branch_id` عددِ بی‌معنایی نیست؛ **یک ارجاع است.** و کاری که `JOIN` می‌کند دقیقاً دنبال کردنِ همین ارجاع است.

## ۳. کوچک‌ترین `JOIN` ممکن

قبل از ۱٬۲۰۰ سطر، روی چیزی که با انگشت می‌شود شمرد. سه کتاب، سه شعبه:

</div>

```python
con.execute("DROP TABLE IF EXISTS tiny_books")
con.execute("DROP TABLE IF EXISTS tiny_branches")
con.execute("CREATE TABLE tiny_branches (branch_id INTEGER PRIMARY KEY, city TEXT)")
con.execute("CREATE TABLE tiny_books (id INTEGER PRIMARY KEY, title TEXT, branch_id INTEGER)")
con.executemany("INSERT INTO tiny_branches VALUES (?, ?)",
                [(1, "تهران"), (2, "مشهد"), (3, "شیراز")])
con.executemany("INSERT INTO tiny_books VALUES (?, ?, ?)",
                [(1, "الف", 1), (2, "ب", 1), (3, "پ", 2)])
con.commit()

print(q("SELECT * FROM tiny_books"))
print()
print(q("SELECT * FROM tiny_branches"))
```

```text
   id title  branch_id
0   1   الف          1
1   2     ب          1
2   3     پ          2

   branch_id   city
0          1  تهران
1          2   مشهد
2          3  شیراز
```

<div dir="rtl">

جوابِ «هر کتاب در چه شهری است؟» را با چشم می‌شود داد: الف و ب در تهران، پ در مشهد. حالا همان را از پایگاه داده بخواه:

</div>

```python
print(q("""
    SELECT tiny_books.title, tiny_branches.city
    FROM tiny_books
    JOIN tiny_branches ON tiny_branches.branch_id = tiny_books.branch_id
"""))
```

```text
  title   city
0   الف  تهران
1     ب  تهران
2     پ   مشهد
```

<div dir="rtl">

**سه سطر رفت، سه سطر برگشت — و همان سه‌تایی که با انگشت شمردی.**

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

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

وقتی ستونِ کلید در هر دو جدول **دقیقاً یک نام** داشته باشد، `USING` کوتاه‌ترش می‌کند:

</div>

```python
print(q("""
    SELECT title, city
    FROM tiny_books
    JOIN tiny_branches USING (branch_id)
"""))
```

```text
  title   city
0   الف  تهران
1     ب  تهران
2     پ   مشهد
```

<div dir="rtl">

همان نتیجه، با نوشتنِ کمتر. **ولی `USING` فقط وقتی کار می‌کند که دو ستون هم‌نام باشند**، و جدول‌های واقعیِ ما نیستند: در `books` نامش `branch_id` است و در `branches` نامش `id`. برای همین `ON` ابزارِ عمومی است و `USING` یک میان‌بُر.

## ۴. همان کار روی ۱٬۲۰۰ سطر

</div>

```python
print(q("""
    SELECT b.title, br.name AS branch, br.city
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    ORDER BY b.id
    LIMIT 3
"""))
print()
print("سطرهای books                :",
      int(q("SELECT COUNT(*) AS n FROM books").iloc[0, 0]))
print("سطرهای نتیجهٔ JOIN          :",
      int(q("""
          SELECT COUNT(*) AS n
          FROM books AS b
          JOIN branches AS br ON br.id = b.branch_id
      """).iloc[0, 0]))
```

```text
            title branch      city
0       روزهای پل   نسیم      مشهد
1  خانه پشت باران   پویا  کرمانشاه
2         شب و مه   دانش     شیراز

سطرهای books                : 1200
سطرهای نتیجهٔ JOIN          : 1200
```

<div dir="rtl">

**۱۲۰۰ سطر رفت، ۱۲۰۰ سطر برگشت. یک ستون اضافه شد و تعدادِ سطرها دست‌نخورده ماند.**

این جمله را جدی بگیر، چون **ادعای این فصل است، نه قاعدهٔ عمومیِ `JOIN`.** اینجا درست است چون هر کتاب دقیقاً به یک شعبه اشاره می‌کند: از سمتِ `books` رابطه «یک‌به‌یک» است. **فصلِ ۳ همین `JOIN` را به جدولی می‌زند که یک کتاب چند سطر در آن دارد، و آنجا تعدادِ سطرها عوض می‌شود — بی‌صدا.**

**نامِ مستعارِ جدول** (`AS b` و `AS br`) از این فصل به بعد در هر پرسشی می‌بینی. کارش فقط کوتاه کردن نیست؛ در پرسشی که یک جدول دو بار در آن آمده — فصلِ ۴ — تنها راهِ حرف زدن دربارهٔ هرکدام است.

## ۵. ابهامِ نامِ ستون

هر دو جدول ستونی به نامِ `id` دارند. اگر بدونِ پیشوند صدایش کنی، `SQLite` نمی‌داند کدام را می‌خواهی — و برخلافِ خیلی از خطاهای این دوره، این یکی صریح اعتراض می‌کند:

> 🔧 **اگر کار نکرد:** پیامِ زیر یعنی نامِ ستون بینِ دو جدول مشترک است و باید بگویی از کدام جدول:

</div>

```python
try:
    q("""
        SELECT id, name
        FROM books AS b
        JOIN branches AS br ON br.id = b.branch_id
        LIMIT 1
    """)
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT id, name
        FROM books AS b
        JOIN branches AS br ON br.id = b.branch_id
        LIMIT 1
    ': ambiguous column name: id
```

<div dir="rtl">

`b.id` یا `br.id` — هر کدام را که بخواهی، **باید بگویی.** جالب اینکه `name` مشکلی نداشت، چون فقط `branches` ستونی به این نام دارد.

**و یک عادت که از همین‌جا برایت می‌سازیم: در پرسشی که بیشتر از یک جدول دارد، هر ستون را با پیشوندِ جدولش بنویس، حتی وقتی مجبور نیستی.** پرسشی که امروز بی‌ابهام است، فردا با اضافه شدنِ یک ستون به یکی از دو جدول مبهم می‌شود — و آن‌وقت پرسشی که سالِ گذشته کار می‌کرد، ناگهان خطا می‌دهد.

## ۶. ترتیبِ نوشتنِ جدول‌ها نتیجه را عوض نمی‌کند

سؤالِ طبیعیِ هر کسی که اولین `JOIN`ش را نوشته: کدام جدول باید اول بیاید؟

</div>

```python
left_first = q("""
    SELECT COUNT(*) AS rows, SUM(b.pages) AS pages
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
""")
right_first = q("""
    SELECT COUNT(*) AS rows, SUM(b.pages) AS pages
    FROM branches AS br
    JOIN books AS b ON b.branch_id = br.id
""")
print("books اول :", int(left_first["rows"][0]), "سطر |", int(left_first["pages"][0]), "صفحه")
print("branches اول:", int(right_first["rows"][0]), "سطر |", int(right_first["pages"][0]), "صفحه")
print("یکی‌اند؟", left_first.equals(right_first))
```

```text
books اول : 1200 سطر | 588049 صفحه
branches اول: 1200 سطر | 588049 صفحه
یکی‌اند؟ True
```

<div dir="rtl">

**برای `INNER JOIN` جای دو جدول فرقی نمی‌کند.** مجموعهٔ سطرهای نتیجه یکی است.

دو نکته که این جمله را از یک شعار به یک ادعای دقیق تبدیل می‌کنند: **اولاً ترتیبِ نمایشِ سطرها تضمین‌شده نیست** — همان قاعدهٔ فصلِ ۴ ترمِ ۱، که اگر ترتیب برایت مهم است `ORDER BY` بنویس. **و ثانیاً این تقارن مالِ `INNER` است؛** برای `LEFT JOIN` که فصلِ بعد می‌آید، جای دو جدول کاملاً نتیجه را عوض می‌کند، و این تفاوت دلیلِ وجودِ آن فصل است.

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** یک سطر از نتیجهٔ بخشِ ۴ یعنی «یک کتاب، به‌همراهِ مشخصاتِ شعبه‌اش» — دقیقاً همان دانه‌بندیِ `books`، نه چیزِ تازه‌ای. **با کدام شمارشِ دوم سنجیدی؟** با `COUNT(*)` قبل و بعد از `JOIN` که هر دو ۱۲۰۰ شدند، و با `SUM(pages)` که از دو ترتیبِ متفاوتِ نوشتن یک عدد داد. **چه کسی از قلم افتاد؟** شعبهٔ آفتاب: هفت شعبه داریم و در نتیجهٔ `JOIN` فقط شش‌تا دیده می‌شوند، چون هیچ کتابی به آفتاب اشاره نمی‌کند. `INNER JOIN` بی‌صدا انداختش بیرون و هیچ عددی در این فصل به تو نگفت.

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

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

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| JOIN | جوین | کنارِ هم آوردنِ سطرهای دو جدول بر اساسِ یک شرط |
| INNER JOIN | اینر جوین | فقط سطرهایی که در هر دو طرف جفت دارند |
| ON | آن | شرطی که می‌گوید کدام سطر با کدام سطر جفت می‌شود |
| USING | یوزینگ | میان‌بُرِ `ON` وقتی نامِ ستونِ کلید در دو جدول یکی است |
| primary key | پرایمری کی | ستونی که هر سطر را یکتا می‌کند |
| foreign key | فارن کی | ستونی که به کلیدِ اصلیِ جدولِ دیگری اشاره می‌کند |
| redundancy | ریداندنسی | افزونگی؛ نوشتنِ یک واقعیت در چند جا |
| update anomaly | آپدیت آنومالی | ناهنجاریِ به‌روزرسانی؛ وقتی یک نسخه به‌روز می‌شود و بقیه نه |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا `books_wide` نُه شهر داشت در حالی که کتاب‌ها در شش شهرند؟

**تمرینِ اجباری:** جدولِ `members` را با `branches` جفت کن — هر عضو یک `city` دارد و هر شعبه هم. پرسشی بنویس که برای هر عضو، نامِ شعبهٔ شهرش را بیاورد. **بعد سه عدد چاپ کن: تعدادِ سطرهای `members`، تعدادِ سطرهای نتیجه، و تفاوتشان.** اگر این دو یکی نبودند، دلیلش را بنویس — و جوابت باید بگوید تفاوت از کدام سمت آمده.

**تمرینِ ستاره‌دار ⭐:** با `tiny_books` و `tiny_branches` بازی کن. یک کتابِ چهارم اضافه کن که `branch_id`ش عددی باشد که در `tiny_branches` وجود ندارد — مثلاً ۹. **بعد همان `JOIN` را دوباره بگیر و تعدادِ سطرها را بشمار.** حالا سؤالِ سخت را جواب بده: **چهار کتاب داری و نتیجه سه سطر است. اگر این جدول گزارشِ «هر کتاب در چه شهری است» باشد، آن گزارش دربارهٔ چند کتاب حرف می‌زند و دربارهٔ چند کتاب سکوت می‌کند؟** جوابت باید نام ببرد که خواننده از کجا می‌تواند بفهمد چیزی کم است.

## در فصل بعد

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

</div>

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