Alt sorgular

“Hangi ürünler ortalama fiyattan pahalı?” Bu soru iki adımdan oluşur: önce ortalama fiyatı bulmak (293,56), sonra onunla karşılaştırmak. Sayıyı sorguya elle yazabilirsin, ama yarın fiyatlar değişince sorgu eskir. Daha iyi yol, iki adımı tek bir sorguda yapmaktır. Bunun için bir alt sorgu kullanırız.

WHERE içinde alt sorgu

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

Önce içteki sorgu çalışır ve tek bir sayı döndürür; ardından dıştaki sorgu onu sıradan bir sayı gibi kullanır. Böyle bir alt sorguya skaler denir: tam olarak bir satır ve bir sütun döndürmelidir. Önceki derslerdeki “en pahalı ürünün adı ne?” sorusunun taşınabilir çözümü de budur:

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

name | price
Laptop | 1450

IN ve NOT IN ile alt sorgular

Bir alt sorgu tek sütunda çok sayıda satır döndürüyorsa IN için liste olarak kullanılabilir. Fizik derslerine kayıtlı öğrencileri bulalım. Alt sorgular iç içe de yazılabilir: en içteki fizik derslerinin numaralarını, ortadaki ise bu derslere kayıtlı öğrencilerin numaralarını döndürü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 ve ilişkili alt sorgular

İlişkili (correlated) bir alt sorgu, dıştaki sorgunun bir sütununa başvurur; örneğin o.customer_id = c.id. Mantıksal olarak dıştaki sorgunun her satırı için yeniden hesaplanır. EXISTS (...), alt sorgu en az bir satır döndürdüğünde doğrudur; satırların içeriği önemli olmadığından içeride genellikle 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

İlişkili bir alt sorgu SELECT içinde de olabilir; o zaman her satır için bir değer hesaplar. Aşağıda her 10. sınıf öğrencisinin kaç derse kayıtlı olduğunu ve en yüksek puanını görüyoruz.

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 içinde alt sorgu ve WITH

Bir alt sorgunun sonucu bir tablodur; bu yüzden FROM içine de yazılabilir ve orada geçici bir tablo gibi çalışır. Çoğu VTYS böyle bir alt sorguya takma ad verilmesini ister (AS t). Aşağıda önce her dersin ortalamasını, sonra bu ortalamaların ortalamasını ve en iyisini buluyoruz.

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 sorgular iç içe çoğaldıkça sorguyu okumak zorlaşır. WITH ifadesi (CTE — ortak tablo ifadesi), bir alt sorguya önceden ad vermeyi ve sonra onu sıradan bir tablo gibi kullanmayı sağlar. WITH; SQLite, PostgreSQL, SQL Server ve 8.0 sürümünden itibaren MySQL tarafından desteklenir.

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