زیرپرس‌وجوها در SQL

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

زیرپرس‌وجوهای تک‌مقداری

زیرپرس‌وجوی تک‌مقداری (Single-Valued Subquery) پرس‌وجویی است که ۱) نتیجه‌ای با یک ستون و یک ردیف تولید می‌کند و ۲) در بند WHERE پرس‌وجوی دیگری «تو در تو» می‌شود. زیرپرس‌وجو باید داخل پرانتز قرار گیرد.

SELECT AVG( POP )
FROM COUNTRIES

SELECT COUNTRY, POP
FROM COUNTRIES
WHERE POP > 30150531

SELECT COUNTRY, POP
FROM COUNTRIES
WHERE POP > ( SELECT AVG( POP )
              FROM COUNTRIES )

• پرس‌وجوهای تک‌مقداری را می‌توان در مقایسه‌ها استفاده کرد.
• زیرپرس‌وجوها داخل پرانتز قرار می‌گیرند.

تمرین‌ها

1. بیشترین جمعیت همهٔ کشورها را پیدا کنید. اکنون همان پرس‌وجو را به‌عنوان زیرپرس‌وجو به کار ببرید تا همهٔ ستون‌های کشوری را که بیشترین جمعیت را دارد نمایش دهید.
2. همهٔ ستون‌های ارتش‌هایی را نمایش دهید که بودجه‌شان از میانگین بودجهٔ همهٔ ارتش‌ها بیشتر است. نتیجه ۵ ردیف دارد.

امتیاز اضافه

1. زبان مادری، نام، عنوان شغلی و تاریخ تولد همهٔ افرادی را نمایش دهید که همان شغل Luther را دارند. بر اساس نام درون زبان مرتب کنید. نتیجه ۱۳ ردیف دارد.

زیرپرس‌وجوهای چندمقداری

زیرپرس‌وجوی چندمقداری (Multi-Valued Subquery) پرس‌وجویی است که ۱) نتیجه‌ای تک‌ستونی با صفر، یک یا چند ردیف برمی‌گرداند و ۲) در بند WHERE پرس‌وجوی دیگری تو در تو می‌شود. زیرپرس‌وجوهای چندمقداری همیشه پس از عملگر IN قرار می‌گیرند.

SELECT DISTINCT JOB
FROM PERSONS
WHERE GENDER = 'FEMALE'

SELECT JOB, TITLE
FROM JOBS
WHERE NOT JOB IN ('W','R','E','S','M')

SELECT JOB, TITLE
FROM JOBS
WHERE NOT JOB IN
( SELECT DISTINCT JOB
  FROM PERSONS
  WHERE GENDER = 'FEMALE' )

• زیرپرس‌وجوهای چندمقداری پس از عملگر IN قرار می‌گیرند.

تمرین‌ها

1. فهرستی از کشورهایی بسازید که نرخ باسوادی آن‌ها کمتر از ۵۰٪ است. سپس از آن پرس‌وجو برای نمایش همهٔ ستون‌های نویسندگان هر یک از آن کشورها استفاده کنید. ۲ ردیف وجود دارد.
2. همهٔ ستون‌های افرادی را نمایش دهید که در کشورهایی با بیش از ۹۵٪ Catholic متولد شده‌اند. ۱۳ نفر وجود دارد.

امتیاز اضافه

1. همهٔ ستون‌های رهبران نظامی را در کشورهای انگلیسی‌زبان نمایش دهید که تعداد نیروهایشان کمتر از میانگین تعداد نیروهای ارتش‌ها است. ۲ ردیف وجود دارد.

زیرپرس‌وجوهای همبسته

زیرپرس‌وجوی همبسته (Correlated Subquery) یک زیرپرس‌وجوی تک‌مقداری یا چندمقداری است که از طریق Alias جدولی تعریف‌شده در بند FROM پرس‌وجوی بیرونی به آن پرس‌وجو ارجاع می‌دهد. زیرپرس‌وجو برای هر ردیف پرس‌وجوی بیرونی یک‌بار اجرا می‌شود.

SELECT JOB, NAME
FROM PERSONS
WHERE JOB = 'S'
AND BDATE =
( SELECT MIN( BDATE )
  FROM PERSONS
  WHERE JOB = 'S' )

SELECT JOB, NAME
FROM PERSONS AS P
WHERE BDATE =
( SELECT MIN( BDATE )
  FROM PERSONS
  WHERE JOB = P.JOB )

• زیرپرس‌وجوهای همبسته چندین بار اجرا می‌شوند.
• زیرپرس‌وجوهای همبسته به Alias جدول نیاز دارند.

تمرین‌ها

1. شغل و نام جوان‌ترین فرد هر حرفه را نمایش دهید. نتیجه ۷ ردیف دارد و افرادی مانند Grisham، Mountbatten و Forbes را شامل می‌شود.
2. با یک پرس‌وجو و یک زیرپرس‌وجوی همبسته، همهٔ ستون‌های جوان‌ترین مرد و جوان‌ترین زن را نمایش دهید. چه وجه مشترک دیگری دارند؟

امتیاز اضافه

1. دین غالب را در هر کشور آلمانی‌زبان پیدا کنید. جدول نتیجه ۴ ردیف دارد.