Alt Sorgular (Subquery)

Subquery Türleri

Scalar Subquery (Tek Değer)

-- Ortalama fiyatın üzerindeki ürünler
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Her ürünün ortalamaya olan farkı
SELECT 
    name,
    price,
    price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

List Subquery (Liste)

-- En az bir sipariş veren kullanıcılar
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders
);

-- Hiç sipariş vermeyenler
SELECT * FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL
);

-- Son 30 günde aktif olan ürünler
SELECT * FROM products
WHERE id IN (
    SELECT DISTINCT product_id 
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.id
    WHERE o.created_at > NOW() - INTERVAL '30 days'
);

EXISTS Subquery

EXISTS, IN'den daha performanslıdır çünkü ilk eşleşmede durur.

-- Siparişi olan kullanıcılar
SELECT u.* FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id
);

-- Tamamlanmış siparişi olmayanlar
SELECT u.* FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
    AND o.status = 'completed'
);

FROM'da Subquery (Inline View)

-- En çok harcama yapan 10 kullanıcının detayları
SELECT 
    u.username,
    u.email,
    top.total_spent,
    top.order_count
FROM (
    SELECT 
        user_id,
        SUM(total_amount) AS total_spent,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
    ORDER BY total_spent DESC
    LIMIT 10
) top
JOIN users u ON top.user_id = u.id;

Correlated Subquery

Her satır için ayrı çalışır, dış sorguya bağlıdır.

-- Her kategorinin en pahalı ürünü
SELECT p1.name, p1.category, p1.price
FROM products p1
WHERE p1.price = (
    SELECT MAX(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
);

-- Departman ortalamasının üstünde maaş alanlar
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) 
    FROM employees 
    WHERE department = e.department
);

ANY / ALL

-- Premium kategorideki herhangi bir üründen pahalı olanlar
SELECT * FROM products
WHERE price > ANY (
    SELECT price FROM products WHERE category = 'Premium'
);

-- Tüm budget ürünlerden pahalı olanlar
SELECT * FROM products
WHERE price > ALL (
    SELECT price FROM products WHERE category = 'Budget'
);