Alt sorğular

«Hansı məhsullar orta qiymətdən bahadır?» Bu sual iki addımdır: əvvəlcə orta qiyməti tapmaq (293,56), sonra onunla müqayisə etmək. Ədədi əllə sorğuya yazmaq olar, amma sabah qiymətlər dəyişəndə sorğu köhnələcək. Daha yaxşı yol — hər iki addımı bir sorğuda etmək. Bunun üçün alt sorğudan istifadə edirik.

WHERE-də alt sorğu

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

name | price
Laptop | 1450
Smartphone | 899.99
Monitor | 310

Əvvəlcə daxili sorğu icra olunur və bir ədəd qaytarır, sonra xarici sorğu onu adi ədəd kimi işlədir. Belə alt sorğu skalyar adlanır: o, düz bir sətir və bir sütun qaytarmalıdır. Keçən dərslərdəki «ən bahalı məhsulun adı» sualının universal həlli də budur:

SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

name | price
Laptop | 1450

IN və NOT IN ilə alt sorğu

Alt sorğu bir sütunda çoxlu sətir qaytarırsa, onu IN-in siyahısı kimi işlətmək olar. Fizika kurslarına yazılan şagirdləri tapaq. Alt sorğular bir-birinin içinə də yerləşdirilə bilər: ən daxili sorğu fizika kurslarının nömrələrini, ortadakı həmin kurslara yazılan şagirdlərin nömrələrini qaytarır.

SELECT first_name, last_name
FROM students
WHERE id IN (
  SELECT student_id
  FROM enrollments
  WHERE course_id IN (SELECT id FROM courses WHERE subject = 'Physics')
)
ORDER BY id;

first_name | last_name
Murad | Əliyev
Elvin | Quliyev
Rəşad | Kərimov
Fidan | Cəfərova

SELECT name, category
FROM products
WHERE id NOT IN (SELECT product_id FROM orders)
ORDER BY id;

name | category
Monitor | Electronics

SELECT COUNT(*) AS found
FROM products
WHERE id NOT IN (1, 2, NULL);

found
0

EXISTS və əlaqəli alt sorğular

Əlaqəli (correlated) alt sorğu xarici sorğunun sütununa istinad edir, məsələn, o.customer_id = c.id. Məntiqi olaraq o, xarici sorğunun hər sətri üçün yenidən hesablanır. EXISTS (...) alt sorğu ən azı bir sətir qaytaranda doğrudur — sətirlərin nə olduğu vacib deyil, ona görə içəridə adətən SELECT 1 yazılır.

SELECT c.name, c.country
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  WHERE o.customer_id = c.id
    AND p.category = 'Electronics'
)
ORDER BY c.id;

name | country
Anar Mustafayev | Azerbaijan
Lalə Əhmədova | Azerbaijan
Emre Yılmaz | Türkiye
Zəhra Hüseynli | Azerbaijan

Əlaqəli alt sorğu SELECT hissəsində də ola bilər — onda o, hər sətir üçün bir qiymət hesablayır. Aşağıda 10-cu sinif şagirdlərinin neçə kursa yazıldığını və ən yüksək balını görürük.

SELECT s.first_name,
       (SELECT COUNT(*)   FROM enrollments AS e WHERE e.student_id = s.id) AS courses,
       (SELECT MAX(score) FROM enrollments AS e WHERE e.student_id = s.id) AS best
FROM students AS s
WHERE s.grade = 10
ORDER BY s.id;

first_name | courses | best
Murad | 2 | 81
Rəşad | 2 | 99
Səbinə | 2 | 88

SELECT e.student_id, e.course_id, e.score
FROM enrollments AS e
WHERE e.score > (
  SELECT AVG(e2.score)
  FROM enrollments AS e2
  WHERE e2.course_id = e.course_id
)
ORDER BY e.course_id, e.score DESC;

student_id | course_id | score
11 | 1 | 97
1 | 1 | 92
3 | 2 | 95
11 | 3 | 94
2 | 3 | 81
4 | 4 | 72
5 | 5 | 93
6 | 6 | 99
3 | 7 | 90

FROM-da alt sorğu və WITH

Alt sorğunun nəticəsi cədvəldir, ona görə onu FROM-da da yazmaq olar — bu, müvəqqəti cədvəl kimi işləyir. Əksər VBİS-lər belə alt sorğuya ləqəb verməyi tələb edir (AS t). Aşağıda əvvəlcə hər kursun ortasını tapırıq, sonra bu ortaların ortasını və ən yaxşısını.

SELECT ROUND(AVG(avg_score), 1) AS avg_of_courses,
       ROUND(MAX(avg_score), 1) AS best_course
FROM (
  SELECT course_id, AVG(score) AS avg_score
  FROM enrollments
  GROUP BY course_id
) AS t;

avg_of_courses | best_course
82 | 92.7

Alt sorğular iç-içə çoxaldıqca sorğunu oxumaq çətinləşir. WITH ifadəsi (CTE — ümumi cədvəl ifadəsi) alt sorğuya əvvəlcədən ad verməyə və sonra onu adi cədvəl kimi işlətməyə imkan verir. WITH SQLite, PostgreSQL, SQL Server və MySQL 8.0-dan etibarən dəstəklənir.

WITH course_avg AS (
  SELECT course_id, ROUND(AVG(score), 1) AS avg_score
  FROM enrollments
  GROUP BY course_id
)
SELECT c.title, ca.avg_score
FROM course_avg AS ca
JOIN courses AS c ON c.id = ca.course_id
WHERE ca.avg_score > 85
ORDER BY ca.avg_score DESC;

title | avg_score
Python Basics | 92.7
World History | 90.5
Algebra | 87.3

SELECT first_name, age
FROM students
WHERE age > ( /* average age here */ )
ORDER BY age DESC, first_name;

first_name | age
Elvin | 17
Fidan | 17
Tural | 17
Murad | 16
Rəşad | 16
Səbinə | 16

SELECT name
FROM products
WHERE id IN (
  -- product ids from the orders of Turkish customers
)
ORDER BY name;

name
Chess set
Pen set
Smartphone