SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Result: Alice (50), Alice (75)
-- Bob, Carol, Dave not in orders → excluded
-- Order 103 (user_id=4) → Dave not in users wait... Dave IS id=4
-- Actually: Dave (20)

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Result:
-- Alice  50.00
-- Alice  75.00
-- Bob    NULL   ← Bob has no orders
-- Carol  NULL   ← Carol has no orders
-- Dave   20.00

SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- Rarely used; swap table order and use LEFT JOIN instead

SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- Shows all users AND all orders, NULLs for no-matches
-- Note: MySQL doesn't support FULL OUTER JOIN; emulate with UNION

SELECT colors.name, sizes.label
FROM colors CROSS JOIN sizes;
-- 3 colors × 4 sizes = 12 rows

-- employees table: id, name, manager_id
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find users who have placed orders over $100
SELECT name
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE total > 100
);

-- Find users whose total spending > average order total
SELECT name
FROM users u
WHERE (
    SELECT SUM(total)
    FROM orders o
    WHERE o.user_id = u.id   -- references outer query's u.id
) > (SELECT AVG(total) FROM orders);

SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE avg_salary > 60000;

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

WITH high_value_orders AS (
    SELECT user_id, SUM(total) AS lifetime_value
    FROM orders
    GROUP BY user_id
    HAVING SUM(total) > 500
),
vip_users AS (
    SELECT u.name, hvo.lifetime_value
    FROM users u
    JOIN high_value_orders hvo ON u.id = hvo.user_id
)
SELECT * FROM vip_users ORDER BY lifetime_value DESC;

WITH RECURSIVE org_chart AS (
    -- Base case: top-level manager
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: join with children
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

-- EXISTS: stops at first match — more efficient for large sets
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- IN: loads all values — can be slow with large subquery results
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders);