# فصل ۰۵ — `UNION`، `INTERSECT`، `EXCEPT`

<div dir="rtl">

# فصل ۰۵ — `UNION`، `INTERSECT`، `EXCEPT`

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

دو دستهٔ کاملاً معمولی: **۱۶۱ کتابِ شعر** و **۵۱۶ کتابی که امسال امانت رفته‌اند.**

پرسش: «چند کتاب دستِ‌کم یکی از این دو ویژگی را دارند؟» جوابِ اولی که همه می‌دهند ۱۶۱ به‌علاوهٔ ۵۱۶ است، یعنی **۶۷۷**. جوابِ درست **۶۱۲** است.

اختلافشان ۶۵ است و ۶۵ عددِ بی‌ربطی نیست: **دقیقاً همان کتاب‌هایی هستند که هم شعرند و هم امسال امانت رفته‌اند — و در جمعِ ساده دو بار شمرده شدند.**

این فصل سه عملگر می‌آورد که کارشان دقیقاً همین است: رویِ هم گذاشتنِ دو نتیجه، پیدا کردنِ اشتراکشان، و کم کردنِ یکی از دیگری.

![دو دایرهٔ متقاطع روی کاغذ که ناحیهٔ اشتراکشان با هاشورِ متفاوتی مشخص شده](/lesson-assets/t3-s05-sets/prompt/01-image-overlap.png)

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

- دو نتیجه را با `UNION` و `UNION ALL` رویِ هم بگذاری و تفاوتشان را با عدد نشان بدهی
- اشتراک و تفاضلِ دو دسته را با `INTERSECT` و `EXCEPT` بگیری
- ثابت کنی که چهار عددت با هم می‌خوانند
- بگویی چرا یک عملگرِ مجموعه‌ای روی نتیجهٔ یک `JOIN`ِ یک‌به‌چند خطرناک است

## قبل از شروع

**از فصلِ ۳:** fan-out — یک `JOIN` یک‌به‌چند سطر تکثیر می‌کند. این فصل نشان می‌دهد آن سطرهای تکثیرشده وقتی وارد یک `UNION ALL` شوند چه بلایی سرِ عدد می‌آورند.

**دانه‌بندی:** هر چهار پرسشِ این فصل یک ستون برمی‌گردانند — `id`ِ کتاب — و **یک سطر یعنی یک کتاب**. این را عمداً ساده نگه داشتیم چون تمامِ درسِ فصل دربارهٔ شمردنِ همان سطرهاست.

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

</div>

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

<div dir="rtl">

## ۱. `JOIN` کنارِ هم می‌گذارد، `UNION` رویِ هم

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

پس `UNION` یک شرطِ سخت دارد که `JOIN` ندارد: **دو طرف باید تعدادِ ستونِ برابر داشته باشند و ستون‌هایشان به یک ترتیب بیایند.**

</div>

```python
con.execute("DROP TABLE IF EXISTS tiny_left")
con.execute("DROP TABLE IF EXISTS tiny_right")
con.execute("CREATE TABLE tiny_left (v INTEGER)")
con.execute("CREATE TABLE tiny_right (v INTEGER)")
con.executemany("INSERT INTO tiny_left VALUES (?)", [(1,), (2,), (3,)])
con.executemany("INSERT INTO tiny_right VALUES (?)", [(3,), (4,)])
con.commit()

print("UNION ALL:", list(q("SELECT v FROM tiny_left UNION ALL SELECT v FROM tiny_right")["v"]))
print("UNION    :", list(q("SELECT v FROM tiny_left UNION SELECT v FROM tiny_right")["v"]))
print("INTERSECT:", list(q("SELECT v FROM tiny_left INTERSECT SELECT v FROM tiny_right")["v"]))
print("EXCEPT   :", list(q("SELECT v FROM tiny_left EXCEPT SELECT v FROM tiny_right")["v"]))
```

```text
UNION ALL: [1, 2, 3, 3, 4]
UNION    : [1, 2, 3, 4]
INTERSECT: [3]
EXCEPT   : [1, 2]
```

<div dir="rtl">

سه عدد و دو عدد. `UNION ALL` پنج سطر داد و عددِ ۳ را دو بار آورد؛ `UNION` چهار سطر داد و یک بار. **`UNION` سطرهای تکراری را حذف می‌کند و `UNION ALL` نمی‌کند — و این تنها تفاوتشان است.**

`INTERSECT` فقط چیزی را می‌دهد که در **هر دو** هست، و `EXCEPT` چیزی را که در اولی هست و در دومی نیست. **دقت کن `EXCEPT` متقارن نیست:** جای دو طرف را عوض کنی، جواب `[4]` می‌شود.

## ۲. دو دستهٔ واقعی

متنِ دو پرسش را در دو متغیرِ پایتونی نگه می‌داریم تا بتوانیم بعداً با هر سه عملگر ترکیبشان کنیم. **و برای شمردنِ نتیجه، هر پرسش را داخلِ پرانتز به‌جای نامِ جدول می‌گذاریم — همان کاری که در فصلِ ۳ برای درمانِ fan-out کردیم. فعلاً همین‌قدر بدان که هر پرسشی می‌تواند جای یک جدول بنشیند؛ فصلِ ۶ کاملاً بازش می‌کند و نامش را می‌گوید.**

</div>

```python
poetry = "SELECT id FROM books WHERE category = ?"
loaned = """
    SELECT DISTINCT b.id
    FROM books AS b
    JOIN loans AS l ON l.book_id = b.id
    WHERE l.loaned_at >= ?
"""
n_poetry = int(q(f"SELECT COUNT(*) AS n FROM ({poetry})", ("شعر",)).iloc[0, 0])
n_loaned = int(q(f"SELECT COUNT(*) AS n FROM ({loaned})", ("2025-01-01",)).iloc[0, 0])

print("کتاب‌های شعر               :", n_poetry)
print("کتاب‌هایی که امسال رفته‌اند :", n_loaned)
print("جمعِ ساده                  :", n_poetry + n_loaned)
```

```text
کتاب‌های شعر               : 161
کتاب‌هایی که امسال رفته‌اند : 516
جمعِ ساده                  : 677
```

<div dir="rtl">

**عددِ ۶۷۷ جوابِ هیچ پرسشی نیست.** یک کتابِ شعر که امسال هم امانت رفته، در هر دو دسته هست و در این جمع دو بار شمرده شده.

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

## ۳. `UNION` همان جمع را درست انجام می‌دهد

</div>

```python
union_all = int(q(f"SELECT COUNT(*) AS n FROM ({poetry} UNION ALL {loaned})",
                  ("شعر", "2025-01-01")).iloc[0, 0])
union_ = int(q(f"SELECT COUNT(*) AS n FROM ({poetry} UNION {loaned})",
                ("شعر", "2025-01-01")).iloc[0, 0])
both = int(q(f"SELECT COUNT(*) AS n FROM ({poetry} INTERSECT {loaned})",
             ("شعر", "2025-01-01")).iloc[0, 0])

print("UNION ALL     :", union_all)
print("UNION         :", union_)
print("INTERSECT     :", both)
print("آزمون         :", union_all, "-", both, "=", union_all - both)
```

```text
UNION ALL     : 677
UNION         : 612
INTERSECT     : 65
آزمون         : 677 - 65 = 612
```

<div dir="rtl">

**۶۷۷ منهای ۶۵ می‌شود ۶۱۲، و این دقیقاً همان `UNION` است.**

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

**و توجه کن `UNION ALL` عددِ ۶۷۷ داد** — یعنی همان جمعِ ساده. این تصادفی نیست: `UNION ALL` هیچ چیزی را حذف نمی‌کند، پس همیشه دقیقاً جمعِ دو تعداد است.

> 🌱 **ریشه‌اش کجاست:** همین قاعده در احتمال هم هست و همان‌جا هم بیشترین اشتباه را می‌سازد: احتمالِ «الف یا ب» جمعِ دو احتمال نیست، مگر وقتی دو پیشامد هیچ اشتراکی نداشته باشند. اگر داشته باشند، اشتراک دو بار شمرده می‌شود و باید یک بار کم شود. [ریشه ترمِ ۶ فصل ۳](/courses/risheh/lessons/term-6-probability-statistics/session-03-combine-events) دقیقاً همین را از پایه باز می‌کند.

## ۴. `INTERSECT` و `EXCEPT`، و آزمونی که هر چهار عدد را می‌سنجد

</div>

```python
only_poetry = int(q(f"SELECT COUNT(*) AS n FROM ({poetry} EXCEPT {loaned})",
                    ("شعر", "2025-01-01")).iloc[0, 0])
only_loaned = int(q(f"SELECT COUNT(*) AS n FROM ({loaned} EXCEPT {poetry})",
                    ("2025-01-01", "شعر")).iloc[0, 0])

print("فقط شعر            :", only_poetry)
print("فقط امانتِ امسال   :", only_loaned)
print("هر دو              :", both)
print()
print("آزمون ۱ — شعر    :", only_poetry, "+", both, "=", only_poetry + both, "| باید 161 باشد")
print("آزمون ۲ — امانت  :", only_loaned, "+", both, "=", only_loaned + both, "| باید 516 باشد")
print("آزمون ۳ — اجتماع :", only_poetry, "+", both, "+", only_loaned, "=",
      only_poetry + both + only_loaned, "| باید 612 باشد")
```

```text
فقط شعر            : 96
فقط امانتِ امسال   : 451
هر دو              : 65

آزمون ۱ — شعر    : 96 + 65 = 161 | باید 161 باشد
آزمون ۲ — امانت  : 451 + 65 = 516 | باید 516 باشد
آزمون ۳ — اجتماع : 96 + 65 + 451 = 612 | باید 612 باشد
```

<div dir="rtl">

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

این ساده‌ترین شمارشِ دومی است که در کارِ گزارش‌نویسی سراغ داریم و نیم دقیقه وقت می‌گیرد.

> ✅ **چک کن:** «فقط شعر» و «هر دو» با هم باید دقیقاً ۱۶۱ شوند. اگر نشد، دو پرسشت شرطِ یکسانی ندارند. سطرِ تکراری علتش نیست: `EXCEPT` و `INTERSECT` هم مثلِ `UNION` نتیجه را یکتا می‌کنند.

## ۵. سازگاریِ ستون‌ها

`UNION` هیچ حدسی نمی‌زند. اگر دو طرف تعدادِ ستونِ برابر نداشته باشند، صریح اعتراض می‌کند:

> 🔧 **اگر کار نکرد:** این پیام یعنی دو طرف تعدادِ ستونشان یکی نیست:

</div>

```python
try:
    q("""
        SELECT id, title FROM books WHERE category = ?
        UNION
        SELECT id FROM books WHERE year >= ?
    """, ("شعر", 2020))
except Exception as error:
    print(type(error).__module__ + "." + type(error).__name__)
    print(error)
```

```text
pandas.errors.DatabaseError
Execution failed on sql '
        SELECT id, title FROM books WHERE category = ?
        UNION
        SELECT id FROM books WHERE year >= ?
    ': SELECTs to the left and right of UNION do not have the same number of result columns
```

<div dir="rtl">

**ولی خطای خطرناک‌تر خطا نمی‌دهد:** اگر تعدادِ ستون‌ها برابر باشد و **ترتیبشان** فرق کند، `SQLite` بی‌صدا قبول می‌کند. `SELECT id, title` در یک طرف و `SELECT title, id` در طرفِ دیگر، یک نتیجهٔ کاملاً بی‌معنی می‌سازد که هیچ‌کس هشدارش را نمی‌دهد.

**نامِ ستونِ خروجی هم از پرسشِ اول می‌آید، نه از دومی.** پس اگر نامِ ستون‌ها را در دو طرف متفاوت نوشته باشی، خروجی نامِ طرفِ اول را دارد و این خودش می‌تواند گمراه‌کننده باشد.

## ۶. `UNION ALL` روی نتیجه‌ای که تکثیر شده

حالا خطری که مخصوصِ این ترم است. در بخشِ ۲ در پرسشِ دوم `DISTINCT` نوشتیم. اگر ننویسیم چه می‌شود؟

</div>

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

print("با DISTINCT   :", with_distinct, "کتاب")
print("بدون DISTINCT :", without_distinct, "سطر")
print()
print("UNION ALL با نسخهٔ بدون DISTINCT:", 161 + without_distinct)
print("جوابِ درست                      :", 612)
```

```text
با DISTINCT   : 516 کتاب
بدون DISTINCT : 770 سطر

UNION ALL با نسخهٔ بدون DISTINCT: 931
جوابِ درست                      : 612
```

<div dir="rtl">

**۹۳۱ در برابرِ ۶۱۲.** همان fan-out فصلِ ۳: ۵۱۶ کتاب، ۷۷۰ امانت، و هر کتابی که امسال دو بار امانت رفته دو سطر دارد.

**نکتهٔ ظریف این است که `UNION` این را درست می‌کرد و `UNION ALL` نه.** یعنی انتخابِ بینِ آن دو، وقتی یک طرف از یک `JOIN`ِ یک‌به‌چند می‌آید، دیگر یک ترجیحِ سلیقه‌ای نیست.

**قاعده:** `UNION ALL` را وقتی بنویس که **مطمئنی** دو طرف هم‌پوشانی ندارند و هیچ‌کدام سطرِ تکراری ندارند — مثلاً وقتی داری داده‌های دو ماهِ متفاوت را پشتِ سرِ هم می‌گذاری. در هر حالتِ دیگری `UNION` بنویس، و اگر عددت با `UNION ALL` فرق کرد، همان تفاوت را نگاه کن: **اگر هیچ طرف سطرِ تکراری نداشته باشد، اندازهٔ اشتراکشان است و باید بدانی چیست.** اگر داشته باشد، تکراری‌های درونِ هر طرف هم در همان تفاوت است.

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

> 🤖 **از دستیارت بپرس:** «چه فرقی بینِ `UNION` و `UNION ALL` هست و کِی باید کدام را بنویسم؟» بعد این را بپرس: **«اگر یکی از دو طرف از یک `JOIN`ِ یک‌به‌چند بیاید، کدامشان عددِ درست می‌دهد؟»** — جوابِ درست می‌گوید `UNION`، و دلیلش را به حذفِ سطرهای تکراری برمی‌گرداند. اگر جواب فقط گفت «`UNION ALL` سریع‌تر است»، پرسشِ درستی جواب نداده.

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| UNION | یونیون | رویِ هم گذاشتنِ دو نتیجه، با حذفِ تکراری‌ها |
| UNION ALL | یونیون آل | همان، بدونِ حذفِ تکراری‌ها |
| INTERSECT | اینترسکت | فقط سطرهایی که در هر دو نتیجه هستند |
| EXCEPT | اکسپت | سطرهایی که در اولی هستند و در دومی نه |
| double counting | دابل کانتینگ | دوباره‌شماری؛ شمردنِ اشتراکِ دو دسته در هر دو |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: ۶۷۷ و ۶۱۲ کدامشان جوابِ «دستِ‌کم یکی از دو شرط» است، و آن یکی جوابِ چیست؟

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

**تمرینِ ستاره‌دار ⭐:** `EXCEPT` را با الگوی «آنچه نیست» فصلِ ۲ مقایسه کن. همان فهرستِ کتاب‌هایی را که هرگز امانت نرفته‌اند یک بار با `LEFT JOIN ... WHERE IS NULL` بگیر و یک بار با `EXCEPT`. **دو عدد چاپ کن؛ باید هر دو ۲۰۰ باشند.** بعد سؤالِ سخت را جواب بده: **اگر ستونی که با آن پیوند می‌زنی در بعضی سطرها `NULL` باشد، آیا این دو راه هنوز یک جواب می‌دهند؟** جوابت باید بگوید `EXCEPT` برای مقایسهٔ `NULL` با `NULL` چه رفتاری دارد و چرا با `=` فرق می‌کند.

## در فصل بعد

از فصلِ ۳ یک بدهی روی دستمان مانده: آن `SELECT`ی که داخلِ پرانتز به‌جای نامِ جدول نشست و گفتیم «فصلِ ۶ بازش می‌کند». در همین فصل هم چند بار از همان استفاده کردیم بدونِ اینکه نامش را ببریم.

فصلِ بعد بدهی را می‌پردازد: **زیرپرسش** — داخلِ `SELECT`، داخلِ `FROM`، و داخلِ `WHERE`. و با یک تلهٔ آشنا تمام می‌شود: **پرسشی که با `NOT IN` نوشته شده، صفر سطر برمی‌گرداند در حالی که جوابِ درستش ۲۴۴ است.**

</div>

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