7 - پرس‌وجو درون پرس‌وجو

• Subqueryهای تک‌مقداری
• Subqueryهای چندمقداری
• Subqueryهای هم‌بسته

Subqueryهای تک‌مقداری

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

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);

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

Subqueryهای تک‌مقداری - ادامه

در نحو فعلی، Subquery تک‌مقداری به Clause WHERE افزوده شده است.

SELECT ...
FROM ...
WHERE column operator ( subquery )

-- Example
WHERE POP > (SELECT AVG(POP) FROM COUNTRIES)

تمرین‌ها

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

امتیاز اضافه

3. زبان مادری، نام، عنوان شغلی و تاریخ تولد همه افرادی را نشان دهید که شغلی مشابه Luther دارند. درون هر زبان بر اساس نام مرتب کنید. نتیجه 13 سطر دارد.

Subqueryهای چندمقداری

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

SELECT DISTINCT JOB
FROM PERSONS
WHERE GENDER = 'FEMALE';

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

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

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

Subqueryهای چندمقداری - ادامه

در نحو فعلی، Subquery چندمقداری به Clause WHERE افزوده شده است.

WHERE column IN ( subquery )
-- Example
WHERE JOB IN (SELECT DISTINCT JOB ...)

تمرین‌ها

1. فهرستی از کشورهایی بسازید که نرخ سوادشان کمتر از 50٪ است. سپس آن پرس‌وجو را به‌کار ببرید تا همه ستون‌های نویسندگانی را نشان دهید که در هر یک از آن کشورها متولد شده‌اند. 2 سطر وجود دارد.
2. همه ستون‌های افرادی را نمایش دهید که در کشورهایی با بیش از 95٪ جمعیت کاتولیک متولد شده‌اند. 13 نفر وجود دارند.

امتیاز اضافه

3. همه ستون‌های رهبران نظامی را نشان دهید که در کشورهای انگلیسی‌زبان متولد شده‌اند و ارتش آن کشور کمتر از میانگین تعداد نیروها را دارد. 2 سطر وجود دارد.

Subqueryهای هم‌بسته

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

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);

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

Subqueryهای هم‌بسته - ادامه

نحو فعلی نشان می‌دهد Subquery هم‌بسته می‌تواند تک‌مقداری یا چندمقداری باشد و به Alias جدول نیاز دارد.

SELECT ...
FROM table AS alias
WHERE column operator
      (SELECT ... WHERE inner_column = alias.outer_column)
-- or
WHERE column IN
      (SELECT ... WHERE inner_column = alias.outer_column)

تمرین‌ها

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

امتیاز اضافه

3. دین غالب هر کشور آلمانی‌زبان را پیدا کنید. جدول نتیجه 4 سطر دارد.