-- 1. SCALAR SUBQUERY (returns single value)

-- Location: SELECT, WHERE, HAVING

-- Result: 1 row, 1 column

SELECT

name,

salary,

(SELECT AVG(salary) FROM employees) AS company_avg -- Scalar in SELECT

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees); -- Scalar in WHERE

-- Subquery executes once, returns one number

-- 2. MULTI-ROW SUBQUERY (returns list)

-- Location: WHERE with IN, ANY, ALL

-- Result: N rows, 1 column

SELECT name, department_id

FROM employees

WHERE department_id IN ( -- Multi-row subquery

SELECT id

FROM departments

WHERE location = 'NYC'

);

-- Subquery returns list: (1, 3, 5)

-- IN checks if employee's dept_id is in that list

-- 3. TABLE SUBQUERY / DERIVED TABLE (returns table)

-- Location: FROM clause

-- Result: N rows, M columns

-- MUST have alias

SELECT

department,

AVG(salary) AS avg_salary

FROM (

SELECT * -- Table subquery

FROM employees

WHERE hire_date > '2023-01-01'

) AS recent_hires -- Alias required!

GROUP BY department;

-- Subquery creates temporary table

-- Outer query aggregates that table

-- 4. CORRELATED SUBQUERY (references outer query)

-- Executes once per outer row

SELECT