# فصل ۰۶ — پرسش داخلِ پرسش

<div dir="rtl">

# فصل ۰۶ — پرسش داخلِ پرسش

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

سه فصل است یک بدهی روی دستمان مانده. در فصلِ ۳ برای درمانِ fan-out یک `SELECT` را داخلِ پرانتز به‌جای نامِ جدول گذاشتیم و گفتیم «فصلِ ۶ بازش می‌کند». حالا بازش می‌کنیم، و اسمش **زیرپرسش** است.

و فصل با تله‌ای تمام می‌شود که ترمِ ۱ فقط اسمش را برد و اینجا واقعاً خطرناک می‌شود. پرسشِ «کدام اعضا هیچ‌کس را معرفی نکرده‌اند؟» با `NOT IN` نوشته می‌شود و **صفر سطر** برمی‌گرداند. جوابِ درست **۲۴۴** است. هیچ خطایی، هیچ هشداری.

تفاوتِ ترمِ ۱ با اینجا این است: آنجا `NULL` را با دست داخلِ فهرست نوشتیم تا ببینی چه می‌شود. اینجا فهرست از یک پرسشِ دیگر می‌آید و **هیچ‌کس نمی‌داند داخلش `NULL` هست یا نه.**

![قوطی‌ای که داخلش قوطیِ کوچک‌تری هست و لولهٔ کوچک، محتوای قوطیِ درونی را به بیرونی می‌دهد](/lesson-assets/t3-s06-subqueries/prompt/01-image-nested.png)

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

- یک زیرپرسشِ اسکالر بنویسی که به‌جای یک مقدار در پرسش می‌نشیند
- یک زیرپرسش را در `FROM` به‌جای جدول بگذاری و بگویی چرا fan-out را درمان می‌کند
- بینِ `IN` و `EXISTS` انتخاب کنی و بگویی کدام با `NULL` امن است
- زیرپرسشِ همبسته را از ناهمبسته تشخیص بدهی و تفاوتشان را با دو عدد نشان بدهی

## قبل از شروع

**از فصلِ ۳:** درمانِ fan-out با تجمیع قبل از پیوند — همان چیزی که این فصل نامش را می‌گوید.

**از ترمِ ۱ فصلِ ۶:** منطقِ سه‌ارزشی و اینکه `NOT IN` با یک `NULL` در فهرست، هیچ سطری برنمی‌گرداند.

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

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

</div>

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

<div dir="rtl">

## ۱. زیرپرسشِ اسکالر: یک پرسش که یک عدد است

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

کاربردِ کلاسیکش «سهم از کل» است — عددی که برای حساب کردنش به کلِ جدول نیاز داری، در حالی که داری روی گروه‌ها کار می‌کنی. **در ترمِ ۲ یک بار از همین استفاده کردیم و همان‌جا گفتیم بدهی‌اش را این فصل می‌پردازد.**

</div>

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

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

<div dir="rtl">

`(SELECT COUNT(*) FROM books)` یک بار اجرا می‌شود و عددِ ۱۲۰۰ می‌دهد، و همان عدد در مخرجِ همهٔ سطرها می‌نشیند. **و آن `100.0` هم عمدی است:** تقسیمِ دو عددِ صحیح در `SQLite` صحیح می‌ماند و جوابِ همهٔ سطرها صفر می‌شد — همان تلهٔ تقسیمِ صحیحِ ترمِ ۱، که ضرب در یک عددِ اعشاری از کارش می‌اندازد. **بدونِ زیرپرسش مجبور بودی اول کل را بگیری، در پایتون نگهش داری، و در پرسشِ دوم بفرستی‌اش — یعنی دو رفت‌وبرگشت و یک عددِ دستی.**

> ✅ **چک کن:** جمعِ ستونِ `share` باید ۱۰۰ شود. اینجا دقیقاً ۱۰۰٫۰ می‌شود، ولی این تضمین‌شده نیست: `ROUND` می‌تواند جمع را کمی جابه‌جا کند. **اگر جمع ۱۰۰ نشد، اول شک کن به گرد کردن، بعد به پرسش** — و اگر اختلاف بیشتر از چند دهم بود، مشکل از گرد کردن نیست.

**و یک چیزِ مهم که این جدول نمی‌گوید:** شعبهٔ آفتاب اینجا نیست، پس این «سهم از کلِ کتاب‌ها» است و مخرجش ۱۲۰۰ است — نه «سهم از شعبه‌ها».

## ۲. زیرپرسش در `FROM` — بدهیِ فصلِ ۳

اگر پرسشی **چند سطر و چند ستون** برگرداند، دیگر یک عدد نیست؛ **یک جدول است.** و هر جایی که نامِ یک جدول می‌تواند بنشیند، او هم می‌تواند — منتها با یک شرط: **باید نامِ مستعار داشته باشد**، وگرنه راهی برای اشاره به ستون‌هایش نداری.

این دقیقاً همان چیزی است که در فصلِ ۳ fan-out را درمان کرد. حالا با نامش:

</div>

```python
per_book = """
    SELECT book_id, COUNT(*) AS n
    FROM loans
    GROUP BY book_id
"""
print("زیرپرسش، به‌تنهایی — سطرها:", len(q(per_book)))
print(q(per_book + " ORDER BY book_id LIMIT 3"))
print()
print(q(f"""
    SELECT br.name, SUM(b.pages) AS pages, SUM(COALESCE(x.n, 0)) AS loans
    FROM books AS b
    JOIN branches AS br ON br.id = b.branch_id
    LEFT JOIN ({per_book}) AS x ON x.book_id = b.id
    GROUP BY br.id, br.name
    ORDER BY pages DESC
"""))
```

```text
زیرپرسش، به‌تنهایی — سطرها: 1000
   book_id  n
0        1  4
1        3  3
2        4  2

    name   pages  loans
0   نسیم  108608    837
1   کوشا  106918    506
2   پویا   97273    378
3  مرکزی   94868   1113
4   دانش   93673    676
5   بهار   86709    488
```

<div dir="rtl">

**۱٬۰۰۰ سطر — یعنی زیرپرسش، جدولِ ۳٬۹۹۸سطریِ `loans` را به یک سطر برای هر کتاب تبدیل کرد.** و از آن لحظه به بعد رابطه‌اش با `books` یک‌به‌یک است، پس دیگر چیزی تکثیر نمی‌شود.

**همین‌جاست که ارزشِ واقعیِ زیرپرسش در `FROM` معلوم می‌شود: دانه‌بندی را عوض می‌کند.** جدولِ `loans` دانه‌بندی «یک امانت» دارد و `books` دانه‌بندی «یک کتاب»؛ زیرپرسش `loans` را به دانه‌بندیِ `books` می‌رساند تا دو طرف قابلِ پیوند شوند.

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

`۱٬۰۰۰` هم شمارشِ دومِ خودش را دارد: ۱٬۰۰۰ کتابی که دستِ‌کم یک امانت دارند، به‌علاوهٔ ۲۰۰ کتابِ بی‌امانتِ فصلِ ۲، می‌شود ۱٬۲۰۰.

## ۳. زیرپرسش در `WHERE`: `IN` و `EXISTS`

اگر پرسشی **یک ستون** برگرداند، یک فهرست است و می‌شود با `IN` سراغش رفت. `EXISTS` کارِ متفاوتی می‌کند: می‌پرسد «آیا این زیرپرسش دستِ‌کم یک سطر برمی‌گرداند؟»

</div>

```python
by_in = int(q("""
    SELECT COUNT(*) AS n
    FROM books
    WHERE id IN (SELECT book_id FROM loans WHERE loaned_at >= ?)
""", ("2025-01-01",)).iloc[0, 0])
by_exists = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b
    WHERE EXISTS (SELECT 1 FROM loans AS l
                  WHERE l.book_id = b.id AND l.loaned_at >= ?)
""", ("2025-01-01",)).iloc[0, 0])

print("با IN     :", by_in)
print("با EXISTS :", by_exists)
print("و با JOIN + DISTINCT از فصل ۵:", 516)
```

```text
با IN     : 516
با EXISTS : 516
و با JOIN + DISTINCT از فصل ۵: 516
```

<div dir="rtl">

**هر سه راه به ۵۱۶ رسیدند، و این خودش یک شمارشِ سه‌گانه است.**

تفاوتشان در ساختار است، نه در جواب. `EXISTS` به سطرِ بیرونی نگاه می‌کند — `l.book_id = b.id` داخلش هست — پس برای هر کتاب یک بار پرسیده می‌شود. `IN` فهرست را یک بار می‌سازد و بعد در آن جست‌وجو می‌کند.

**و `SELECT 1` داخلِ `EXISTS` عمدی است:** `EXISTS` فقط می‌پرسد سطری هست یا نه و اصلاً به مقدارها نگاه نمی‌کند، پس نوشتنِ نامِ ستون آنجا فقط خواننده را گمراه می‌کند.

**مهم‌ترین تفاوتشان جای دیگری است، و بخشِ بعد همان است.**

## ۴. `NOT IN` و تله‌ای که هیچ صدایی ندارد

جدولِ `members` ستونی به نامِ `referred_by` دارد: اگر عضوی را عضوِ دیگری معرفی کرده باشد، شمارهٔ او آنجاست؛ وگرنه `NULL`.

پرسش: **کدام اعضا هیچ‌کس را معرفی نکرده‌اند؟**

</div>

```python
naive = int(q("""
    SELECT COUNT(*) AS n
    FROM members
    WHERE id NOT IN (SELECT referred_by FROM members)
""").iloc[0, 0])
filtered = int(q("""
    SELECT COUNT(*) AS n
    FROM members
    WHERE id NOT IN (SELECT referred_by FROM members WHERE referred_by IS NOT NULL)
""").iloc[0, 0])
by_not_exists = int(q("""
    SELECT COUNT(*) AS n
    FROM members AS m
    WHERE NOT EXISTS (SELECT 1 FROM members AS r WHERE r.referred_by = m.id)
""").iloc[0, 0])
referrers = int(q("""
    SELECT COUNT(*) AS n
    FROM members
    WHERE id IN (SELECT referred_by FROM members)
""").iloc[0, 0])

print("NOT IN، همان‌طور که همه می‌نویسند:", naive)
print("NOT IN، با فیلترِ IS NOT NULL     :", filtered)
print("NOT EXISTS                        :", by_not_exists)
print()
print("و در مقابل، IN                    :", referrers)
print("آزمون                             :", referrers, "+", filtered, "=", referrers + filtered)
print("سطرهایی که referred_by شان NULL است:",
      int(q("SELECT COUNT(*) AS n FROM members WHERE referred_by IS NULL").iloc[0, 0]))
```

```text
NOT IN، همان‌طور که همه می‌نویسند: 0
NOT IN، با فیلترِ IS NOT NULL     : 244
NOT EXISTS                        : 244

و در مقابل، IN                    : 56
آزمون                             : 56 + 244 = 300
سطرهایی که referred_by شان NULL است: 225
```

<div dir="rtl">

**صفر در برابرِ ۲۴۴.**

و بدترین بخشش این است: **`IN` کاملاً درست کار کرد** و ۵۶ داد. یعنی همان پرسش، با همان زیرپرسش، در یک جهت درست است و در جهتِ دیگر نه. کسی که `IN` را امتحان کرده و دیده جواب می‌دهد، هیچ دلیلی ندارد به `NOT IN` شک کند.

مکانیزمش همان ترمِ ۱ است. `x NOT IN (…)` یعنی «`x` نه با این برابر است، نه با آن، نه با آن یکی». آن زیرپرسش ۲۲۵ سطرِ `NULL` برمی‌گرداند، و «آیا `x` با یک چیزِ نامعلوم برابر نیست؟» هیچ‌وقت قطعاً درست نمی‌شود. پس کلِ زنجیره هیچ‌وقت درست نمی‌شود و **هیچ سطری نمی‌ماند.**

`NOT EXISTS` این مشکل را ندارد چون اصلاً مقایسه‌ای بینِ مقدارها انجام نمی‌دهد — فقط می‌پرسد سطری برگشت یا نه، و جوابِ آن همیشه بله یا خیر است.

**قاعدهٔ عملی: `NOT IN` را روی ستونی که ممکن است `NULL` داشته باشد ننویس. یا `NOT EXISTS` بنویس، یا `IS NOT NULL` را داخلِ زیرپرسش بگذار.** و چون در پرسشِ واقعی معمولاً نمی‌دانی زیرپرسشِ کسِ دیگری `NULL` برمی‌گرداند یا نه، **`NOT EXISTS` انتخابِ پیش‌فرضِ امن‌تری است.**

> ✅ **چک کن:** ۵۶ به‌علاوهٔ ۲۴۴ باید دقیقاً ۳۰۰ شود، یعنی کلِ اعضا. این آزمون در نسخهٔ اول شکست می‌خورد — ۵۶ به‌علاوهٔ صفر می‌شود ۵۶ — و **همان یک خط جلوی انتشارِ یک عددِ غلط را می‌گیرد.**

## ۵. همبسته در برابرِ ناهمبسته

زیرپرسشِ **ناهمبسته** به سطرِ بیرونی کاری ندارد؛ یک بار حساب می‌شود و تمام. زیرپرسشِ **همبسته** به ستونی از سطرِ بیرونی اشاره می‌کند، پس جوابش برای هر سطر فرق می‌کند.

تفاوتشان فنی به‌نظر می‌رسد تا وقتی ببینی **دو پرسشِ متفاوت‌اند، نه دو نوشتنِ یک پرسش:**

</div>

```python
global_avg = int(q("""
    SELECT COUNT(*) AS n
    FROM books
    WHERE pages > (SELECT AVG(pages) FROM books)
""").iloc[0, 0])
branch_avg = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b
    WHERE b.pages > (SELECT AVG(x.pages) FROM books AS x WHERE x.branch_id = b.branch_id)
""").iloc[0, 0])
only_global = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b
    WHERE b.pages > (SELECT AVG(pages) FROM books)
      AND b.pages <= (SELECT AVG(x.pages) FROM books AS x WHERE x.branch_id = b.branch_id)
""").iloc[0, 0])
only_branch = int(q("""
    SELECT COUNT(*) AS n
    FROM books AS b
    WHERE b.pages <= (SELECT AVG(pages) FROM books)
      AND b.pages > (SELECT AVG(x.pages) FROM books AS x WHERE x.branch_id = b.branch_id)
""").iloc[0, 0])

print("بالاتر از میانگینِ کل        :", global_avg)
print("بالاتر از میانگینِ شعبهٔ خودش:", branch_avg)
print("فقط در اولی                  :", only_global)
print("فقط در دومی                  :", only_branch)
print("آزمون                        :", global_avg, "-", only_global, "+", only_branch,
      "=", global_avg - only_global + only_branch)
```

```text
بالاتر از میانگینِ کل        : 589
بالاتر از میانگینِ شعبهٔ خودش: 587
فقط در اولی                  : 5
فقط در دومی                  : 3
آزمون                        : 589 - 5 + 3 = 587
```

<div dir="rtl">

**۵۸۹ و ۵۸۷ خیلی نزدیک‌اند و همین خطرناکشان می‌کند.** اگر فقط این دو عدد را ببینی، فکر می‌کنی دو راهِ نوشتنِ یک چیزند و اختلافشان نویز است.

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

**و این دقیقاً همان چیزی است که عددِ نزدیک پنهانش می‌کند.** اختلافِ ۲تایی می‌گوید «فرقی نمی‌کند»؛ آزمونِ سه‌خطی می‌گوید هشت کتاب جابه‌جا شده‌اند.

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

</div>

```python
try:
    q("SELECT id, (SELECT id, name FROM branches LIMIT 1) AS x FROM books 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, (SELECT id, name FROM branches LIMIT 1) AS x FROM books LIMIT 1': sub-select returns 2 columns - expected 1
```

<div dir="rtl">

**ولی اگر زیرپرسشِ اسکالر چند سطر برگرداند، `SQLite` خطا نمی‌دهد — بی‌صدا سطرِ اول را برمی‌دارد.** و «اول» بدونِ `ORDER BY` هیچ معنایی ندارد. **این یکی از معدود جاهایی است که یک عددِ کاملاً دلخواه وارد گزارشت می‌شود و هیچ نشانه‌ای هم ندارد.**

> 📏 **اندازه بگیر:** **یک سطر یعنی چه؟** در بخشِ ۲ زیرپرسش دانه‌بندی را عوض کرد: `loans` با دانه‌بندیِ «یک امانت» وارد شد و با دانه‌بندیِ «یک کتاب» بیرون آمد — و کلِ درمانِ fan-out همین بود. **با کدام شمارشِ دوم سنجیدی؟** ۱٬۰۰۰ به‌علاوهٔ ۲۰۰ برابرِ ۱٬۲۰۰؛ ۵۶ به‌علاوهٔ ۲۴۴ برابرِ ۳۰۰؛ و ۵۸۹ منهای ۵ به‌علاوهٔ ۳ برابرِ ۵۸۷. **چه کسی از قلم افتاد؟** در بخشِ ۴، نسخهٔ اول همه را انداخت — ۲۴۴ عضو، به‌خاطرِ ۲۲۵ مقدارِ `NULL` که هیچ‌کدامشان در پرسش دیده نمی‌شدند.

> 🤖 **از دستیارت بپرس:** «چرا `NOT IN` وقتی زیرپرسش `NULL` برمی‌گرداند صفر سطر می‌دهد، ولی `IN` درست کار می‌کند؟» بعد این را بپرس: **«اگر زیرپرسشِ من امروز `NULL` ندارد ولی فردا ممکن است داشته باشد، باز هم باید `NOT EXISTS` بنویسم؟»** — جوابِ درست بله است و دلیلش را به این برمی‌گرداند که این خطا هیچ پیامی ندارد، پس روزی که پیش بیاید کسی متوجه نمی‌شود.

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

</div>

| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| subquery | ساب‌کوئری | زیرپرسش؛ پرسشی داخلِ پرسشِ دیگر |
| scalar subquery | اسکالر ساب‌کوئری | زیرپرسشی که یک سطر و یک ستون می‌دهد، پس مثلِ یک مقدار است |
| derived table | درایوِد تیبل | زیرپرسشی که در `FROM` می‌نشیند و مثلِ یک جدول رفتار می‌کند |
| EXISTS | اگزیستس | می‌پرسد زیرپرسش دستِ‌کم یک سطر دارد یا نه |
| correlated subquery | کورلیتد ساب‌کوئری | زیرپرسشِ همبسته؛ به سطرِ بیرونی اشاره می‌کند |

<div dir="rtl">

## تمرین‌ها

قبل از تمرین، بدونِ نگاه به بالا جواب بده: چرا `IN` درست کار کرد و `NOT IN` نه، در حالی که زیرپرسشِ هر دو یکی بود؟

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

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

## در فصل بعد

پرسشِ بخشِ ۲ یک زیرپرسش داشت و خواندنش از وسط شروع می‌شد. با یکی دو مرحلهٔ بیشتر، عملاً غیرقابلِ خواندن می‌شود.

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

</div>

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