در این فصل چه یاد میگیری#
سه فصل است یک بدهی روی دستمان مانده. در فصلِ ۳ برای درمانِ fan-out یک SELECT را داخلِ پرانتز بهجای نامِ جدول گذاشتیم و گفتیم «فصلِ ۶ بازش میکند». حالا بازش میکنیم، و اسمش زیرپرسش است.
و فصل با تلهای تمام میشود که ترمِ ۱ فقط اسمش را برد و اینجا واقعاً خطرناک میشود. پرسشِ «کدام اعضا هیچکس را معرفی نکردهاند؟» با NOT IN نوشته میشود و صفر سطر برمیگرداند. جوابِ درست ۲۴۴ است. هیچ خطایی، هیچ هشداری.
تفاوتِ ترمِ ۱ با اینجا این است: آنجا NULL را با دست داخلِ فهرست نوشتیم تا ببینی چه میشود. اینجا فهرست از یک پرسشِ دیگر میآید و هیچکس نمیداند داخلش NULL هست یا نه.

آخر این فصل میتوانی:
- یک زیرپرسشِ اسکالر بنویسی که بهجای یک مقدار در پرسش مینشیند
- یک زیرپرسش را در
FROMبهجای جدول بگذاری و بگویی چرا fan-out را درمان میکند - بینِ
INوEXISTSانتخاب کنی و بگویی کدام باNULLامن است - زیرپرسشِ همبسته را از ناهمبسته تشخیص بدهی و تفاوتشان را با دو عدد نشان بدهی
قبل از شروع#
از فصلِ ۳: درمانِ fan-out با تجمیع قبل از پیوند — همان چیزی که این فصل نامش را میگوید.
از ترمِ ۱ فصلِ ۶: منطقِ سهارزشی و اینکه NOT IN با یک NULL در فهرست، هیچ سطری برنمیگرداند.
دانهبندی: یک زیرپرسش دانهبندی ندارد تا وقتی نگویی چه چیزی برمیگرداند: یک مقدار، یک ستون، یا یک جدول. هر سه در این فصل هستند و جایی که میتوانند بنشینند فرق میکند.
📓 نوتبوک: نوتبوک این فصل را در Colab باز کن — همهٔ کدهای این فصل آماده و بهترتیب داخلش هست.
| جدول | تعدادِ سطر | زمانِ ساخت |
|---|---|---|
books |
۱٬۲۰۰ | کمتر از یک ثانیه |
branches |
۷ | آنی |
members |
۳۰۰ | آنی |
loans |
۳٬۹۹۸ | کمتر از یک ثانیه |
۱. زیرپرسشِ اسکالر: یک پرسش که یک عدد است#
اگر پرسشی یک سطر و یک ستون برگرداند، مثلِ یک عدد رفتار میکند و هر جایی که یک عدد میتواند بنشیند، او هم میتواند.
کاربردِ کلاسیکش «سهم از کل» است — عددی که برای حساب کردنش به کلِ جدول نیاز داری، در حالی که داری روی گروهها کار میکنی. در ترمِ ۲ یک بار از همین استفاده کردیم و همانجا گفتیم بدهیاش را این فصل میپردازد.
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
"""))
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
(SELECT COUNT(*) FROM books) یک بار اجرا میشود و عددِ ۱۲۰۰ میدهد، و همان عدد در مخرجِ همهٔ سطرها مینشیند. و آن 100.0 هم عمدی است: تقسیمِ دو عددِ صحیح در SQLite صحیح میماند و جوابِ همهٔ سطرها صفر میشد — همان تلهٔ تقسیمِ صحیحِ ترمِ ۱، که ضرب در یک عددِ اعشاری از کارش میاندازد. بدونِ زیرپرسش مجبور بودی اول کل را بگیری، در پایتون نگهش داری، و در پرسشِ دوم بفرستیاش — یعنی دو رفتوبرگشت و یک عددِ دستی.
✅ چک کن: جمعِ ستونِ
shareباید ۱۰۰ شود. اینجا دقیقاً ۱۰۰٫۰ میشود، ولی این تضمینشده نیست:ROUNDمیتواند جمع را کمی جابهجا کند. اگر جمع ۱۰۰ نشد، اول شک کن به گرد کردن، بعد به پرسش — و اگر اختلاف بیشتر از چند دهم بود، مشکل از گرد کردن نیست.
و یک چیزِ مهم که این جدول نمیگوید: شعبهٔ آفتاب اینجا نیست، پس این «سهم از کلِ کتابها» است و مخرجش ۱۲۰۰ است — نه «سهم از شعبهها».
۲. زیرپرسش در FROM — بدهیِ فصلِ ۳#
اگر پرسشی چند سطر و چند ستون برگرداند، دیگر یک عدد نیست؛ یک جدول است. و هر جایی که نامِ یک جدول میتواند بنشیند، او هم میتواند — منتها با یک شرط: باید نامِ مستعار داشته باشد، وگرنه راهی برای اشاره به ستونهایش نداری.
این دقیقاً همان چیزی است که در فصلِ ۳ fan-out را درمان کرد. حالا با نامش:
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
"""))
زیرپرسش، بهتنهایی — سطرها: 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
۱٬۰۰۰ سطر — یعنی زیرپرسش، جدولِ ۳٬۹۹۸سطریِ loans را به یک سطر برای هر کتاب تبدیل کرد. و از آن لحظه به بعد رابطهاش با books یکبهیک است، پس دیگر چیزی تکثیر نمیشود.
همینجاست که ارزشِ واقعیِ زیرپرسش در FROM معلوم میشود: دانهبندی را عوض میکند. جدولِ loans دانهبندی «یک امانت» دارد و books دانهبندی «یک کتاب»؛ زیرپرسش loans را به دانهبندیِ books میرساند تا دو طرف قابلِ پیوند شوند.
قاعدهای که از این ترم با خودت میبری: قبل از هر JOIN، دانهبندیِ دو طرف را بگو. اگر یکی نبودند، اول آن طرفی را تجمیع کن که دانهبندیاش ریزتر است.
۱٬۰۰۰ هم شمارشِ دومِ خودش را دارد: ۱٬۰۰۰ کتابی که دستِکم یک امانت دارند، بهعلاوهٔ ۲۰۰ کتابِ بیامانتِ فصلِ ۲، میشود ۱٬۲۰۰.
۳. زیرپرسش در WHERE: IN و EXISTS#
اگر پرسشی یک ستون برگرداند، یک فهرست است و میشود با IN سراغش رفت. EXISTS کارِ متفاوتی میکند: میپرسد «آیا این زیرپرسش دستِکم یک سطر برمیگرداند؟»
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)
با IN : 516
با EXISTS : 516
و با JOIN + DISTINCT از فصل ۵: 516
هر سه راه به ۵۱۶ رسیدند، و این خودش یک شمارشِ سهگانه است.
تفاوتشان در ساختار است، نه در جواب. EXISTS به سطرِ بیرونی نگاه میکند — l.book_id = b.id داخلش هست — پس برای هر کتاب یک بار پرسیده میشود. IN فهرست را یک بار میسازد و بعد در آن جستوجو میکند.
و SELECT 1 داخلِ EXISTS عمدی است: EXISTS فقط میپرسد سطری هست یا نه و اصلاً به مقدارها نگاه نمیکند، پس نوشتنِ نامِ ستون آنجا فقط خواننده را گمراه میکند.
مهمترین تفاوتشان جای دیگری است، و بخشِ بعد همان است.
۴. NOT IN و تلهای که هیچ صدایی ندارد#
جدولِ members ستونی به نامِ referred_by دارد: اگر عضوی را عضوِ دیگری معرفی کرده باشد، شمارهٔ او آنجاست؛ وگرنه NULL.
پرسش: کدام اعضا هیچکس را معرفی نکردهاند؟
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]))
NOT IN، همانطور که همه مینویسند: 0
NOT IN، با فیلترِ IS NOT NULL : 244
NOT EXISTS : 244
و در مقابل، IN : 56
آزمون : 56 + 244 = 300
سطرهایی که referred_by شان NULL است: 225
صفر در برابرِ ۲۴۴.
و بدترین بخشش این است: IN کاملاً درست کار کرد و ۵۶ داد. یعنی همان پرسش، با همان زیرپرسش، در یک جهت درست است و در جهتِ دیگر نه. کسی که IN را امتحان کرده و دیده جواب میدهد، هیچ دلیلی ندارد به NOT IN شک کند.
مکانیزمش همان ترمِ ۱ است. x NOT IN (…) یعنی «x نه با این برابر است، نه با آن، نه با آن یکی». آن زیرپرسش ۲۲۵ سطرِ NULL برمیگرداند، و «آیا x با یک چیزِ نامعلوم برابر نیست؟» هیچوقت قطعاً درست نمیشود. پس کلِ زنجیره هیچوقت درست نمیشود و هیچ سطری نمیماند.
NOT EXISTS این مشکل را ندارد چون اصلاً مقایسهای بینِ مقدارها انجام نمیدهد — فقط میپرسد سطری برگشت یا نه، و جوابِ آن همیشه بله یا خیر است.
قاعدهٔ عملی: NOT IN را روی ستونی که ممکن است NULL داشته باشد ننویس. یا NOT EXISTS بنویس، یا IS NOT NULL را داخلِ زیرپرسش بگذار. و چون در پرسشِ واقعی معمولاً نمیدانی زیرپرسشِ کسِ دیگری NULL برمیگرداند یا نه، NOT EXISTS انتخابِ پیشفرضِ امنتری است.
✅ چک کن: ۵۶ بهعلاوهٔ ۲۴۴ باید دقیقاً ۳۰۰ شود، یعنی کلِ اعضا. این آزمون در نسخهٔ اول شکست میخورد — ۵۶ بهعلاوهٔ صفر میشود ۵۶ — و همان یک خط جلوی انتشارِ یک عددِ غلط را میگیرد.
۵. همبسته در برابرِ ناهمبسته#
زیرپرسشِ ناهمبسته به سطرِ بیرونی کاری ندارد؛ یک بار حساب میشود و تمام. زیرپرسشِ همبسته به ستونی از سطرِ بیرونی اشاره میکند، پس جوابش برای هر سطر فرق میکند.
تفاوتشان فنی بهنظر میرسد تا وقتی ببینی دو پرسشِ متفاوتاند، نه دو نوشتنِ یک پرسش:
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)
بالاتر از میانگینِ کل : 589
بالاتر از میانگینِ شعبهٔ خودش: 587
فقط در اولی : 5
فقط در دومی : 3
آزمون : 589 - 5 + 3 = 587
۵۸۹ و ۵۸۷ خیلی نزدیکاند و همین خطرناکشان میکند. اگر فقط این دو عدد را ببینی، فکر میکنی دو راهِ نوشتنِ یک چیزند و اختلافشان نویز است.
ولی هشت کتاب هست که در یکی هستند و در دیگری نه — پنجتا از میانگینِ کل بالاترند و از شعبهٔ خودشان نه، سهتا برعکس. اینها دو پرسشِ متفاوتاند و کدامش را میخواهی به این بستگی دارد که «کتابِ قطور» را نسبت به کلِ کتابخانه تعریف میکنی یا نسبت به قفسهای که در آن است.
و این دقیقاً همان چیزی است که عددِ نزدیک پنهانش میکند. اختلافِ ۲تایی میگوید «فرقی نمیکند»؛ آزمونِ سهخطی میگوید هشت کتاب جابهجا شدهاند.
🔧 اگر کار نکرد: رایجترین خطای زیرپرسشِ اسکالر این است که بیشتر از یک ستون برگرداند.
SQLiteصریح اعتراض میکند:
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)
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
ولی اگر زیرپرسشِ اسکالر چند سطر برگرداند، SQLite خطا نمیدهد — بیصدا سطرِ اول را برمیدارد. و «اول» بدونِ ORDER BY هیچ معنایی ندارد. این یکی از معدود جاهایی است که یک عددِ کاملاً دلخواه وارد گزارشت میشود و هیچ نشانهای هم ندارد.
📏 اندازه بگیر: یک سطر یعنی چه؟ در بخشِ ۲ زیرپرسش دانهبندی را عوض کرد:
loansبا دانهبندیِ «یک امانت» وارد شد و با دانهبندیِ «یک کتاب» بیرون آمد — و کلِ درمانِ fan-out همین بود. با کدام شمارشِ دوم سنجیدی؟ ۱٬۰۰۰ بهعلاوهٔ ۲۰۰ برابرِ ۱٬۲۰۰؛ ۵۶ بهعلاوهٔ ۲۴۴ برابرِ ۳۰۰؛ و ۵۸۹ منهای ۵ بهعلاوهٔ ۳ برابرِ ۵۸۷. چه کسی از قلم افتاد؟ در بخشِ ۴، نسخهٔ اول همه را انداخت — ۲۴۴ عضو، بهخاطرِ ۲۲۵ مقدارِNULLکه هیچکدامشان در پرسش دیده نمیشدند.
🤖 از دستیارت بپرس: «چرا
NOT INوقتی زیرپرسشNULLبرمیگرداند صفر سطر میدهد، ولیINدرست کار میکند؟» بعد این را بپرس: «اگر زیرپرسشِ من امروزNULLندارد ولی فردا ممکن است داشته باشد، باز هم بایدNOT EXISTSبنویسم؟» — جوابِ درست بله است و دلیلش را به این برمیگرداند که این خطا هیچ پیامی ندارد، پس روزی که پیش بیاید کسی متوجه نمیشود.
واژههای تازهٔ این فصل#
| کلمه | تلفظ به حروف فارسی | یعنی چه |
|---|---|---|
| subquery | سابکوئری | زیرپرسش؛ پرسشی داخلِ پرسشِ دیگر |
| scalar subquery | اسکالر سابکوئری | زیرپرسشی که یک سطر و یک ستون میدهد، پس مثلِ یک مقدار است |
| derived table | درایوِد تیبل | زیرپرسشی که در FROM مینشیند و مثلِ یک جدول رفتار میکند |
| EXISTS | اگزیستس | میپرسد زیرپرسش دستِکم یک سطر دارد یا نه |
| correlated subquery | کورلیتد سابکوئری | زیرپرسشِ همبسته؛ به سطرِ بیرونی اشاره میکند |
تمرینها
اول خودت فکر کن یا امتحان کن — بعد اینجا را باز کن.
در فصل بعد#
پرسشِ بخشِ ۲ یک زیرپرسش داشت و خواندنش از وسط شروع میشد. با یکی دو مرحلهٔ بیشتر، عملاً غیرقابلِ خواندن میشود.
فصلِ بعد پرسشی از همین جنس را به مرحلههای نامدار میشکند — CTE — و نشان میدهد چطور میشود هر مرحله را جدا اجرا کرد و شمرد. بعد سراغِ چیزی میرود که تا اینجا اصلاً ممکن نبود: پیمودنِ یک سلسلهمراتب تا هر عمقی، با پرسشی که خودش را صدا میزند — و روی ۱۲ بخشِ کتابخانه، چهار طبقه پیدا میکند.
به آخر این فصل رسیدی!
اگر ساختی و جواب داد، این دکمه مال توست.