Subquery ও UNION

Scalar Subquery

-- গড়ের চেয়ে বেশি দাম
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- সর্বোচ্চ দামী
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);

Subquery in WHERE (IN)

-- যারা order দিয়েছে
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- যারা দেয়নি
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

EXISTS ⭐ (দ্রুত)

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

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

Subquery in FROM

-- Top 5 customer
SELECT u.name, t.orders
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS orders
    FROM orders GROUP BY user_id
    ORDER BY orders DESC LIMIT 5
) t ON u.id = t.user_id;

Correlated Subquery

-- প্রতি category-র সবচেয়ে দামী product
SELECT p1.*
FROM products p1
WHERE p1.price = (
    SELECT MAX(p2.price) FROM products p2
    WHERE p2.category_id = p1.category_id
);

Subquery in SELECT

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

UNION

-- দুই query-র ফলাফল
SELECT id, name FROM users WHERE status='active'
UNION
SELECT id, name FROM customers WHERE created_at > '2025-01-01';

-- নিয়ম: column সংখ্যা ও type সামঞ্জস্যপূর্ণ

-- UNION — ডুপ্লিকেট সরায়
-- UNION ALL — সব রাখে (দ্রুত)

SELECT city FROM users
UNION ALL
SELECT city FROM suppliers;

CTE (MySQL 8+)

WITH top_customers AS (
    SELECT user_id, SUM(total) AS spent
    FROM orders GROUP BY user_id
    HAVING spent > 5000
)
SELECT u.name, tc.spent
FROM top_customers tc
JOIN users u ON u.id = tc.user_id
ORDER BY tc.spent DESC;