زیرپرس‌وجوها (Subqueries)

فصل ۴ به ما یاد داد چطور با JOIN چند جدول را کنار هم بگذاریم. اما گاهی نیازی به ترکیب کل جدول‌ها نداریم — فقط می‌خواهیم نتیجه‌ی یک پرس‌وجو را به‌عنوان ورودی پرس‌وجوی دیگری استفاده کنیم. به این تکنیک زیرپرس‌وجو (Subquery) می‌گویند: یک SELECT کامل، داخل یک SELECT دیگر.

زیرپرس‌وجوی اسکالر (Scalar Subquery)

ساده‌ترین نوع زیرپرس‌وجو، دقیقاً یک مقدار (یک ردیف، یک ستون) برمی‌گرداند و می‌تواند هرجا یک عدد یا متن مجاز باشد، به‌کار برود:

-- محصولاتی که قیمتشان از میانگین قیمت همه‌ی محصولات بیشتر است
SELECT product, price
FROM sales
WHERE price > (SELECT AVG(price) FROM sales);

این‌جا زیرپرس‌وجوی (SELECT AVG(price) FROM sales) اول اجرا و به یک عدد تبدیل می‌شود؛ سپس پرس‌وجوی بیرونی هر ردیف را با همان عدد مقایسه می‌کند. بیا ببینیم:

زیرپرس‌وجو با IN

وقتی زیرپرس‌وجو ممکن است چند مقدار برگرداند (نه فقط یکی)، از IN استفاده می‌کنیم — دقیقاً همان‌طور که در درس WHERE با لیست‌های ثابت دیدیم، فقط این‌بار لیست از یک پرس‌وجوی دیگر می‌آید:

-- دانش‌آموزانی که حداقل در یک درس ثبت‌نام کرده‌اند
SELECT full_name
FROM students
WHERE id IN (SELECT student_id FROM enrollments);

نکته‌ی جالب: این پرس‌وجو دقیقاً همان نتیجه‌ای را می‌دهد که در فصل ۴ با INNER JOIN (به‌همراه DISTINCT) هم می‌شد گرفت — یعنی اغلب چند راه مختلف برای رسیدن به یک نتیجه وجود دارد. بیا امتحان کنیم:

⚠️ هشدار مهم: NOT IN و مقادیر NULL

این‌جا یکی از رایج‌ترین و خطرناک‌ترین تله‌های SQL قرار دارد. فرض کن می‌خواهی برعکس مثال بالا را بپرسی: «کدام درس‌ها هیچ دانش‌آموزی ندارند؟» و می‌نویسی:

SELECT title
FROM courses
WHERE id NOT IN (SELECT course_id FROM enrollments);

اگر حتی یک ردیف در جدول enrollments مقدار course_idاش NULL باشد (مثلاً ثبت‌نامی که هنوز درسش انتخاب نشده)، این پرس‌وجو به‌طور کامل و بی‌سروصدا هیچ نتیجه‌ای برمی‌گرداند — حتی اگر واقعاً درسی بدون دانش‌آموز وجود داشته باشد! علتش یادت هست؟ در درس NULL دیدیم مقایسه با NULL همیشه UNKNOWN می‌شود، نه TRUE و نه FALSE؛ و کافی است یکی از مقادیر داخل لیست IN نامشخص باشد تا کل شرط NOT IN برای همه‌ی ردیف‌ها غیرقابل‌اعتماد شود. بیا این تله را با چشم خودت ببینی:

زیرپرس‌وجوی همبسته (Correlated Subquery)

بر خلاف مثال‌های بالا که زیرپرس‌وجو مستقل و فقط یک‌بار اجرا می‌شد، زیرپرس‌وجوی همبسته به ستون‌های پرس‌وجوی بیرونی ارجاع می‌دهد و برای هر ردیف بیرونی، جداگانه دوباره اجرا می‌شود:

-- تعداد درس‌های ثبت‌نامی هر دانش‌آموز
SELECT
  s.full_name,
  (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.id) AS CourseCount
FROM students s;

این‌جا e.student_id = s.id باعث می‌شود زیرپرس‌وجو به ازای هر دانش‌آموز، دوباره اجرا شود. توجه کن این دقیقاً همان نتیجه‌ای است که در درس LEFT JOIN با ترکیب LEFT JOIN و GROUP BY گرفتیم — فقط این‌بار بدون نیاز به GROUP BY. بیا ببینیم:

زیرپرس‌وجو در FROM (جدول مشتق‌شده)

زیرپرس‌وجو را می‌توان به‌جای یک جدول واقعی، در FROM هم به‌کار برد — به این حالت جدول مشتق‌شده (Derived Table) می‌گویند. این‌جا دقیقاً همان جایی است که مفید می‌شود که SQL اجازه‌ی تو در تو کردن مستقیم توابع تجمیعی را نمی‌دهد — نمی‌توانی بنویسی AVG(AVG(price)). اما با یک جدول مشتق‌شده، می‌شود:

-- میانگینِ میانگین‌قیمتِ هر دسته (نه میانگین کل محصولات)
SELECT AVG(avg_price) AS AvgOfCategoryAverages
FROM (
  SELECT category, AVG(price) AS avg_price
  FROM sales
  GROUP BY category
) AS category_avgs;

دقت کن: جدول مشتق‌شده حتماً باید نام مستعار (این‌جا category_avgs) داشته باشد، وگرنه SQL Server خطا می‌دهد. بیا امتحان کنیم:

جمع‌بندی این درس

• زیرپرس‌وجوی اسکالر یک مقدار برمی‌گرداند و می‌تواند در WHERE یا SELECT استفاده شود.
• زیرپرس‌وجو با IN لیستی از مقادیر برمی‌گرداند.
• هرگز از NOT IN با زیرپرس‌وجویی که ممکن است NULL برگرداند استفاده نکن — نتیجه‌اش می‌تواند به‌طور کامل و بی‌خطا، خالی باشد.
• زیرپرس‌وجوی همبسته به ازای هر ردیف بیرونی دوباره اجرا می‌شود؛ برای مقادیر محاسبه‌شده‌ی وابسته به هر ردیف مفید است.
• زیرپرس‌وجو در FROM (جدول مشتق‌شده) امکان تجمیع چندمرحله‌ای را فراهم می‌کند و حتماً به نام مستعار نیاز دارد.

در درس بعدی سراغ EXISTS می‌رویم: راه‌حل امن و اغلب سریع‌تر برای بررسی «آیا رابطه‌ای وجود دارد؟» — بدون تله‌ی NULL که همین حالا دیدیم.