Где можно использовать подзапросы в SQL запросе?

# Podzaprosy v SQL

Podzapros - eto SQL zapros vnutri drugogo SQL zaprosa. Podzaprosy mozhna ispolzovat v mnozhestve mest dlya resheniqa slozhnykh zadach.

Gde mozhno ispolzovat podzaprosy

1. V klauze SELECT

Dobavlyat novyj stolbec dannykh na osnove podleta iz drugoj tablicy.

SELECT 
    user_id,
    email,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS total_orders,
    (SELECT AVG(amount) FROM orders WHERE user_id = users.id) AS avg_order_amount
FROM users;

Primery:

-- Poluchit imena i kolichestvo zakazov kazhdogo polzovatelya
SELECT 
    u.name,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- Poluchit producten i ego ceny
SELECT 
    product_name,
    price,
    (SELECT MAX(price) FROM products) AS max_price
FROM products;

2. V klauze FROM

Ispoolzovat podzapros kak ishodnuyu tablicu.

SELECT * 
FROM (SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id) AS user_orders
WHERE order_count > 5;

Primery:

-- Poluchit srednyuyu tsenu po kategoriyam
SELECT 
    category,
    avg_price
FROM (
    SELECT 
        category,
        AVG(price) as avg_price
    FROM products
    GROUP BY category
) AS category_prices
WHERE avg_price > 100;

-- Reyting polzovatelej
SELECT 
    rank,
    COUNT(*) as user_count
FROM (
    SELECT 
        CASE 
            WHEN total_orders > 10 THEN 'VIP'
            WHEN total_orders > 5 THEN 'Regular'
            ELSE 'New'
        END as rank,
        user_id
    FROM (
        SELECT user_id, COUNT(*) as total_orders FROM orders GROUP BY user_id
    ) AS user_order_counts
) AS user_ranks
GROUP BY rank;

3. V klauze WHERE

Proverka uslovij, zavisyashchikh ot druginkg dannykh.

SELECT * 
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

Primery:

-- Poluchit polzovatelej, kotorye delali zakazy
SELECT * 
FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- Poluchit producty dorozhe srednej tseny
SELECT * 
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Poluchit polzovatelej, kotorye nikogda ne delali zakazy
SELECT * 
FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- Poluchit zakazy, gde amount > serednego
SELECT * 
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

4. V klauze HAVING

Filtrovat gruppy na osnove agregirovannyh dannyh.

SELECT 
    category,
    COUNT(*) as product_count
FROM products
GROUP BY category
HAVING COUNT(*) > (SELECT AVG(product_count) FROM (SELECT COUNT(*) as product_count FROM products GROUP BY category) AS t);

Primery:

-- Kategorii s bolee chem srednee kolichestvo produktov
SELECT 
    category,
    COUNT(*) as product_count
FROM products
GROUP BY category
HAVING COUNT(*) > (SELECT COUNT(*) / (SELECT COUNT(DISTINCT category) FROM products) FROM products);

-- Departamenty s srednej zarplatoj vyshe srednej po kompanii
SELECT 
    department,
    AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

5. V klauze JOIN

Ispolzovat podzapros dlya soedineniya tablits.

SELECT u.name, subq.total_amount
FROM users u
JOIN (
    SELECT user_id, SUM(amount) as total_amount
    FROM orders
    GROUP BY user_id
) subq ON u.id = subq.user_id;

Primery:

-- Poluchit imena i kolichestvo zakazov
SELECT 
    u.name,
    order_info.order_count,
    order_info.total_spent
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) as order_count, SUM(amount) as total_spent
    FROM orders
    GROUP BY user_id
) order_info ON u.id = order_info.user_id;

6. S operatorom EXISTS / NOT EXISTS

Proverka sushchestvovaniya dannyh.

SELECT * 
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Primery:

-- Poluchit polzovatelej, kotorye delali zakazy
SELECT * 
FROM users u
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);

-- Poluchit polzovatelej, kotorye nikogda ne delali zakazy
SELECT * 
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);

-- Produkty, kotorye byli v bole chem n zakazah
SELECT * 
FROM products p
WHERE EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.product_id = p.id
    GROUP BY oi.product_id
    HAVING COUNT(*) > 10
);

7. S operatorami IN, ALL, ANY

Sravnenie so mnozhestvom znachenij.

-- IN: provet, est li znachenie v podmore
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = true);

-- ALL: vse znacheniya iz podzaprosa
SELECT * FROM products WHERE price > ALL (SELECT AVG(price) FROM products GROUP BY category);

-- ANY: khote by odnoye znacheniye iz podzaprosa
SELECT * FROM orders WHERE amount = ANY (SELECT amount FROM orders WHERE user_id = 5);

Tipy podzaprosy

Scalar Subquery (vozvrashchaet odno znacheniye)

SELECT 
    product_name,
    price,
    (SELECT MAX(price) FROM products) as max_price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Multiple Row Subquery (vozvrashchaet neskolko strok)

SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE country = 'Russia');

Correlated Subquery (zavisit ot vneshnego zaprosa)

SELECT u.id, u.name,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;

Sravnenie Subquery vs JOIN

Subquery:

SELECT u.name, u.email
FROM users u
WHERE u.id IN (SELECT user_id FROM orders WHERE amount > 100);

JOIN (obychno bystree):

SELECT DISTINCT u.name, u.email
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

Luchshie praktiki

Ok Ispolzuj EXISTS vmesto IN dlya bolshikh dataset-ov Ok Dlya JOIN obychno bystree, chem subquery Ok Indeksiruй kolonnay, ispolzuemyya v WHERE dlya podleta Ok Izbegaj deeply nested subqueries Ok Testiruй performance raznyh variantov Ok Kommentiruj slozhnyye zapros Ok Ispolzuj CTE (WITH) vmesto subqueries dlya chitaemosti

CTE (Common Table Expression) - Alternativa subqueries

WITH user_orders AS (
    SELECT user_id, COUNT(*) as order_count, SUM(amount) as total_spent
    FROM orders
    GROUP BY user_id
)
SELECT u.name, uo.order_count, uo.total_spent
FROM users u
JOIN user_orders uo ON u.id = uo.user_id
WHERE uo.total_spent > 1000;

CTE obychno:

• Chitaemyye
• Mogut byt rekursivnye
• Mogu byt repeused
• Chasto bystree chem nested subqueries