# فصل ۰۴ — پیوندِ جدول با خودش، و `CROSS JOIN`

<div dir="rtl">

# فصل ۰۴ — پیوندِ جدول با خودش، و `CROSS JOIN`

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

دو پرسش که هیچ‌کدام با ابزارِ سه فصلِ قبل جواب ندارند:

**«کدام کتاب‌ها دو بار در فهرست ثبت شده‌اند؟»** — این پرسش دربارهٔ رابطهٔ `books` با جدولِ دیگری نیست؛ دربارهٔ رابطهٔ `books` با **خودش** است. جوابش **۲۸ جفت** است، که ۲۴تایشان در دو شعبهٔ متفاوت‌اند.

**«در کدام ماه‌ها هیچ امانتی نبوده؟»** — و این یکی از پرسشِ اول هم بدتر است، چون جوابش **در داده نیست**. گزارشی که از `loans` ساخته شود ۳۰ سطر دارد و کامل به‌نظر می‌رسد؛ وقتی کنارِ یک تقویمِ ۴۸ماهه بگذاری‌اش، **۱۸ ماه پیدا می‌شود که هیچ سطری ندارند** — و آن ۱۸ ماه دو معنیِ کاملاً متفاوت دارند.

![یک شبکهٔ مستطیلی که از تقاطعِ یک ردیف مهره با یک ستون مهره ساخته شده و بعضی خانه‌هایش خالی مانده](/lesson-assets/t3-s04-self-and-cross-join/prompt/01-image-grid-of-pairs.png)

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

- یک جدول را با دو نامِ مستعار به خودش پیوند بزنی و سطرهای تکراری را پیدا کنی
- بگویی چرا شرطِ `a.id < b.id` در یک self join اختیاری نیست
- با `CROSS JOIN` جدولی بسازی که خانه‌های خالی‌اش دیده می‌شوند
- تشخیص بدهی یک `CROSS JOIN`ِ تصادفی چه شکلی است

## قبل از شروع

**از فصلِ ۱ تا ۳:** `INNER JOIN` و `LEFT JOIN`، نامِ مستعارِ جدول، و fan-out.

**از ترمِ ۱:** `||` برای چسباندنِ متن و `substr` برای برداشتنِ بخشی از یک رشته — هر دو از فصلِ ۵ ترمِ ۱.

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

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

</div>

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

<div dir="rtl">

## ۱. یک جدول، دو نام

`JOIN` نمی‌داند دو طرفش دو جدولِ متفاوت‌اند یا یکی. تنها چیزی که لازم دارد این است که بتوانی به هر طرف اشاره کنی — و برای همین **نامِ مستعار اینجا از یک راحتی به یک ضرورت تبدیل می‌شود.**

سه کتاب، که دوتایشان یکی هستند:

</div>

```python
con.execute("DROP TABLE IF EXISTS tiny_books")
con.execute("CREATE TABLE tiny_books (id INTEGER PRIMARY KEY, title TEXT, author TEXT)")
con.executemany("INSERT INTO tiny_books VALUES (?, ?, ?)",
                [(1, "الف", "نویسندهٔ یک"), (2, "ب", "نویسندهٔ دو"), (3, "الف", "نویسندهٔ یک")])
con.commit()

both_ways = q("""
    SELECT a.id AS id_a, b.id AS id_b, a.title
    FROM tiny_books AS a
    JOIN tiny_books AS b ON b.title = a.title AND b.author = a.author
    ORDER BY a.id, b.id
""")
one_way = q("""
    SELECT a.id AS id_a, b.id AS id_b, a.title
    FROM tiny_books AS a
    JOIN tiny_books AS b ON b.title = a.title AND b.author = a.author AND b.id > a.id
    ORDER BY a.id, b.id
""")
print("بدون شرط روی id —", len(both_ways), "سطر")
print(both_ways)
print()
print("با شرط b.id > a.id —", len(one_way), "سطر")
print(one_way)
```

```text
بدون شرط روی id — 5 سطر
   id_a  id_b title
0     1     1   الف
1     1     3   الف
2     2     2     ب
3     3     1   الف
4     3     3   الف

با شرط b.id > a.id — 1 سطر
   id_a  id_b title
0     1     3   الف
```

<div dir="rtl">

**پنج سطر در برابرِ یک سطر، و آن پنج‌تا را می‌شود با انگشت توضیح داد:** سه سطر جایی است که هر کتاب با **خودش** جفت شده (۱ با ۱، ۲ با ۲، ۳ با ۳)، و دو سطرِ دیگر همان یک جفتِ واقعی است که **دو بار** شمرده شده — یک بار ۱ با ۳ و یک بار ۳ با ۱.

پس `b.id > a.id` دو کار را با هم انجام می‌دهد: **جفتِ هر سطر با خودش را حذف می‌کند، و از هر جفت فقط یک ترتیب را نگه می‌دارد.** بدونِ آن، جوابت دقیقاً دو برابرِ واقعیت به‌علاوهٔ تعدادِ کلِ سطرها می‌شود.

## ۲. همان کار روی ۱٬۲۰۰ کتاب

</div>

```python
raw = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS a
    JOIN books AS b ON b.title = a.title AND b.author = a.author
""").iloc[0, 0])
pairs = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS a
    JOIN books AS b ON b.title = a.title AND b.author = a.author AND b.id > a.id
""").iloc[0, 0])

print("بدون شرط روی id :", raw)
print("جفت‌های واقعی    :", pairs)
print("آزمون            :", 1200, "+", 2 * pairs, "=", 1200 + 2 * pairs)
```

```text
بدون شرط روی id : 1256
جفت‌های واقعی    : 28
آزمون            : 1200 + 56 = 1256
```

<div dir="rtl">

**۱٬۲۵۶ دقیقاً برابرِ ۱٬۲۰۰ به‌علاوهٔ دو برابرِ ۲۸ است.** همان الگوی جدولِ اسباب‌بازی، روی هزار و دویست سطر — و این شمارشِ دوم است که می‌گوید عددِ ۲۸ درست است.

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

</div>

```python
print(q("""
    SELECT a.id AS id_a, b.id AS id_b, a.title, a.author
    FROM books AS a
    JOIN books AS b ON b.title = a.title AND b.author = a.author AND b.id > a.id
    ORDER BY a.id
    LIMIT 4
"""))
print()
print("جفت‌هایی که در دو شعبهٔ متفاوت‌اند:",
      int(q("""
          SELECT COUNT(*) AS n
          FROM books AS a
          JOIN books AS b ON b.title = a.title AND b.author = a.author AND b.id > a.id
          WHERE b.branch_id <> a.branch_id
      """).iloc[0, 0]))
print("جفت‌هایی که در یک شعبه‌اند       :",
      int(q("""
          SELECT COUNT(*) AS n
          FROM books AS a
          JOIN books AS b ON b.title = a.title AND b.author = a.author AND b.id > a.id
          WHERE b.branch_id = a.branch_id
      """).iloc[0, 0]))
```

```text
   id_a  id_b            title        author
0    14   959    بازگشت به برف  فرهاد کاشانی
1    18   814      روزهای دریا    زهرا دهقان
2    25   118     بازگشت به شب    نگار سلیمی
3    33   796  در جست‌وجوی ابر  بهرام کاشانی

جفت‌هایی که در دو شعبهٔ متفاوت‌اند: 24
جفت‌هایی که در یک شعبه‌اند       : 4
```

<div dir="rtl">

**۲۴ به‌علاوهٔ ۴ می‌شود ۲۸ — شمارشِ دومِ همان عدد، این بار از راهِ تقسیم به دو گروه.**

و تفسیرشان کاملاً متفاوت است: ۲۴ جفتی که در دو شعبه‌اند احتمالاً اصلاً تکراری نیستند — یک کتاب که هر دو شعبه نسخه‌ای از آن دارند، و این طبیعی است. **۴ جفتی که در یک شعبه‌اند مشکوک‌اند** و همان‌هایی هستند که ارزشِ بررسیِ دستی دارند.

> ⚠️ **مواظب باش:** این جفت‌ها را با «عنوان و نویسندهٔ یکسان» پیدا کردیم. در برشِ این ترم نامِ نویسنده یکدست است، ولی در ترمِ ۱ دیدیم که یک نویسنده می‌تواند با نویسهٔ عربی هم ثبت شده باشد. **روی چنین داده‌ای این عدد کف است، نه سقف.** جمله‌ای که در گزارش می‌نویسی باید همین را بگوید.

## ۳. `CROSS JOIN`: همهٔ جفت‌های ممکن

`CROSS JOIN` بندِ `ON` ندارد، چون شرطی در کار نیست: **هر سطرِ چپ با هر سطرِ راست.**

</div>

```python
con.execute("DROP TABLE IF EXISTS tiny_branches")
con.execute("DROP TABLE IF EXISTS tiny_quarters")
con.execute("CREATE TABLE tiny_branches (name TEXT)")
con.execute("CREATE TABLE tiny_quarters (label TEXT)")
con.executemany("INSERT INTO tiny_branches VALUES (?)", [("مرکزی",), ("نسیم",), ("دانش",)])
con.executemany("INSERT INTO tiny_quarters VALUES (?)", [("q1",), ("q2",), ("q3",), ("q4",)])
con.commit()

grid = q("""
    SELECT br.name, qt.label
    FROM tiny_branches AS br
    CROSS JOIN tiny_quarters AS qt
    ORDER BY br.rowid, qt.rowid
""")
print("۳ شعبه ×", 4, "فصل =", len(grid), "سطر")
print(grid.head(5))
```

```text
۳ شعبه × 4 فصل = 12 سطر
    name label
0  مرکزی    q1
1  مرکزی    q2
2  مرکزی    q3
3  مرکزی    q4
4   نسیم    q1
```

<div dir="rtl">

**سه ضربدر چهار می‌شود دوازده** — همان اصلِ ضربی که در فصلِ ۳ دیدیم، این بار به‌عنوانِ کاری که عمداً می‌خواهیمش.

روی جدول‌های واقعی همین ضرب عددِ ترسناکی می‌سازد:

</div>

```python
cross = int(q("SELECT COUNT(*) AS n FROM books CROSS JOIN loans").iloc[0, 0])
print("books CROSS JOIN loans:", cross)
print("۱۲۰۰ × ۳۹۹۸           :", 1200 * 3998)
print("یکی‌اند؟", cross == 1200 * 3998)
```

```text
books CROSS JOIN loans: 4797600
۱۲۰۰ × ۳۹۹۸           : 4797600
یکی‌اند؟ True
```

<div dir="rtl">

**چهار میلیون و هفتصد و نودوهفت هزار و ششصد سطر، از دو جدولی که با هم پنج هزار سطر هم نمی‌شوند.**

## ۴. جدولِ تقویم: سطرهایی که در داده نیستند

اینجا `CROSS JOIN` از یک کنجکاوی به یک ابزارِ جدی تبدیل می‌شود. پرسش این است: در هر ماه چند امانت داشتیم؟

اگر از `loans` شروع کنی، فقط ماه‌هایی را می‌بینی که **سطری دارند**. ماهی که هیچ امانتی نداشته، اصلاً سطری ندارد که دیده شود. **راهِ حل این است که اول تقویم را بسازی و بعد داده را به آن وصل کنی.**

</div>

```python
con.execute("DROP TABLE IF EXISTS cal_years")
con.execute("DROP TABLE IF EXISTS cal_months")
con.execute("CREATE TABLE cal_years (y TEXT)")
con.execute("CREATE TABLE cal_months (m TEXT)")
con.executemany("INSERT INTO cal_years VALUES (?)",
                [("2022",), ("2023",), ("2024",), ("2025",)])
con.executemany("INSERT INTO cal_months VALUES (?)", [(f"{n:02d}",) for n in range(1, 13)])
con.commit()

print("سطرهای تقویم              :",
      int(q("SELECT COUNT(*) AS n FROM cal_years CROSS JOIN cal_months").iloc[0, 0]))
print("ماه‌هایی که در loans هستند:",
      int(q("SELECT COUNT(DISTINCT substr(loaned_at, 1, 7)) AS n FROM loans").iloc[0, 0]))
```

```text
سطرهای تقویم              : 48
ماه‌هایی که در loans هستند: 30
```

<div dir="rtl">

**تقویم ۴۸ ماه دارد و داده ۳۰ ماه. هجده ماه هیچ سطری ندارند** — و هجده سطری که هرگز در هیچ گزارشی ظاهر نمی‌شوند مگر اینکه خودت جایشان را از قبل باز کرده باشی.

</div>

```python
print(q("""
    SELECT y.y || '-' || m.m AS ym, COUNT(l.id) AS loans
    FROM cal_years AS y
    CROSS JOIN cal_months AS m
    LEFT JOIN loans AS l ON substr(l.loaned_at, 1, 7) = y.y || '-' || m.m
    GROUP BY ym
    HAVING loans = 0
    ORDER BY ym
"""))
```

```text
         ym  loans
0   2022-01      0
1   2022-02      0
2   2022-03      0
3   2022-04      0
4   2022-05      0
5   2022-06      0
6   2022-07      0
7   2022-08      0
8   2022-09      0
9   2022-10      0
10  2022-11      0
11  2022-12      0
12  2025-07      0
13  2025-08      0
14  2025-09      0
15  2025-10      0
16  2025-11      0
17  2025-12      0
```

<div dir="rtl">

**هجده ماه، و دقیقاً دو دسته — که همین تقسیمِ دو دسته، درسِ اصلیِ این بخش است.**

دوازده ماهِ اولِ فهرست مالِ سالی هستند که ثبتِ امانت هنوز شروع نشده بود. شش ماهِ آخر هنوز **نیامده‌اند**. هیچ‌کدامشان «صفر امانت» نیستند: **دستهٔ اول یعنی «نمی‌دانیم» و دستهٔ دوم یعنی «هنوز نه».** ستونِ `loans` برای این دو حالت — و برای ماهی که واقعاً صفر امانت داشته — همان عددِ صفر را چاپ می‌کند و هیچ راهی برای تفکیکِ این سه ندارد.

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

> ✅ **چک کن:** ۳۰ ماهِ دارای داده به‌علاوهٔ ۱۸ ماهِ خالی باید ۴۸ شود. اگر `HAVING loans = 0` را برداری و سطرها را بشماری باید همان ۴۸ دربیاید. اگر عددِ دیگری گرفتی، `LEFT JOIN` را `JOIN` نوشته‌ای و ماه‌های خالی همان‌جا افتاده‌اند بیرون.

## ۵. `CROSS JOIN`ِ تصادفی

`SQL` یک نگارشِ قدیمی‌تر هم دارد که جدول‌ها را با ویرگول جدا می‌کند و شرط را در `WHERE` می‌گذارد. کاملاً معتبر است — و دقیقاً به همین دلیل خطرناک:

</div>

```python
with_condition = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b, loans AS l
    WHERE l.book_id = b.id
""").iloc[0, 0])
without_condition = int(q("SELECT COUNT(*) AS n FROM books AS b, loans AS l").iloc[0, 0])

print("با شرط در WHERE :", with_condition)
print("بدون شرط        :", without_condition)
print("چند برابر       :", round(without_condition / with_condition, 1))
```

```text
با شرط در WHERE : 3998
بدون شرط        : 4797600
چند برابر       : 1200.0
```

<div dir="rtl">

**یک خطِ `WHERE` جا افتاد و نتیجه ۱٬۲۰۰ برابر شد.**

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

> 🔧 **اگر کار نکرد:** رایج‌ترین خطای self join این است که نامِ مستعار را جا بیندازی. آن‌وقت `SQLite` نمی‌داند کدام نسخهٔ جدول را می‌گویی:

</div>

```python
try:
    q("""
        SELECT title
        FROM books
        JOIN books ON books.title = books.title
        LIMIT 1
    """)
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT title
        FROM books
        JOIN books ON books.title = books.title
        LIMIT 1
    ': ambiguous column name: title
```

<div dir="rtl">

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در بخشِ ۲ یک سطر یک **جفت کتاب** است، نه یک کتاب — پس عددِ ۲۸ را نمی‌شود با ۱٬۲۰۰ مقایسه کرد. در بخشِ ۴ یک سطر یک **ماه** است، چه داده‌ای داشته باشد چه نه. **با کدام شمارشِ دوم سنجیدی؟** ۱٬۲۵۶ برابرِ ۱٬۲۰۰ به‌علاوهٔ دو برابرِ ۲۸؛ ۲۴ به‌علاوهٔ ۴ برابرِ ۲۸؛ و ۳۰ به‌علاوهٔ ۱۸ برابرِ ۴۸. **چه کسی از قلم افتاد؟** در این برش از داده کسی، چون نامِ نویسنده یکدست است؛ روی داده‌ای که یکدست نباشد، کتاب‌هایی که واقعاً تکراری‌اند ولی نامِ نویسنده‌شان فرق دارد در این عدد نمی‌آیند. و در جدولِ ماهانه، هیچ‌کس: **کلِ نکتهٔ آن بخش این بود که جای خالی‌ها را هم بشماریم.**

> 🤖 **از دستیارت بپرس:** «چرا در یک self join باید شرطی مثلِ `a.id < b.id` بگذاریم؟» بعد این را بپرس: **«اگر به‌جای `<` از `<>` استفاده کنم چه می‌شود؟»** — جوابِ درست می‌گوید `<>` فقط جفتِ هر سطر با خودش را حذف می‌کند و هر جفت را همچنان دو بار می‌شمارد. اگر جواب گفت هر دو یکی‌اند، با عددهای بخشِ ۱ ردش کن.

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| self join | سلف جوین | پیوندِ یک جدول با خودش، با دو نامِ مستعار |
| CROSS JOIN | کراس جوین | هر سطرِ چپ با هر سطرِ راست، بدونِ شرط |
| Cartesian product | کارتزین پرادکت | ضربِ دکارتی؛ نامِ ریاضیِ همان همهٔ‌جفت‌های‌ممکن |
| calendar table | کلندر تیبل | جدولی از همهٔ دوره‌ها، تا دورهٔ بی‌داده هم دیده شود |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: ۱٬۲۵۶ از چه دو عددی ساخته شد؟

**تمرینِ اجباری:** جفت اعضایی را پیدا کن که در یک شهر زندگی می‌کنند و در یک ماه عضو شده‌اند. **دو عدد چاپ کن: تعدادِ جفت‌ها با شرطِ `a.id < b.id` و بدونِ آن.** بعد بنویس **عددِ دوم باید چه رابطه‌ای با اولی داشته باشد** و بررسی کن که دارد یا نه.

**تمرینِ ستاره‌دار ⭐:** جدولِ ماهانهٔ بخشِ ۴ را کامل‌تر کن: به‌جای یک ستونِ امانت، برای **هر شعبه در هر ماه** یک سطر بساز — یعنی تقویم را با جدولِ شعبه‌ها هم `CROSS JOIN` کن. **بعد سؤالِ سخت را جواب بده: این جدول باید چند سطر داشته باشد، و چند سطرش صفر است؟** و مهم‌تر: **آیا همهٔ صفرها یک معنی دارند؟** جوابت باید دستِ‌کم به شعبه‌ای اشاره کند که در بخشی از این بازه اصلاً باز نبوده.

## در فصل بعد

تا اینجا همیشه دو جدول را **کنارِ هم** گذاشتیم تا سطری پهن‌تر بسازیم. فصلِ بعد کارِ دیگری می‌کند: دو نتیجه را **رویِ هم** می‌گذارد تا سطرهایشان یکی شود. و با آن سراغِ خطایی می‌رود که در هر گزارشی که دو شرط دارد کمین کرده: **دو دسته ۱۶۱ و ۵۱۶ کتاب دارند، ولی تعدادِ کتاب‌هایی که دستِ‌کم یکی از دو شرط را دارند ۶۷۷ نیست — ۶۱۲ است.**

</div>

---
Source: https://yaadestan.com/courses/avand/lessons/t3/s04-self-and-cross-join (یادستان, Yaadestan)
