-- ============================================

-- SCALAR SUBQUERY IN SELECT (Non-correlated)

-- ============================================

-- Returns same value for all rows

-- Executes once, result cached

SELECT

name,

salary,

department,

(SELECT AVG(salary) FROM employees) AS company_avg, -- Scalar subquery

salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg

FROM employees;

-- Execution:

-- 1. Subquery runs once: AVG(salary) → 75000

-- 2. Result cached

-- 3. Main query processes all employees

-- 4. Each row gets 75000 as company_avg

-- Result:

-- name | salary | department | company_avg | diff_from_avg

-- Alice | 95000 | Engineering | 75000 | 20000

-- Bob | 85000 | Engineering | 75000 | 10000

-- Charlie | 65000 | Sales | 75000 | -10000

-- ============================================

-- CORRELATED SUBQUERY IN SELECT

-- ============================================

-- Returns different value per row

-- Executes once per row (expensive!)

SELECT

e1.name,

e1.salary,

e1.department,

(SELECT AVG(e2.salary) -- Correlated subquery

FROM employees e2

WHERE e2.department = e1.department) AS dept_avg, -- References e1

e1.salary -

(SELECT AVG(e2.salary)

FROM employees e2

WHERE e2.department = e1.department) AS diff_from_dept_avg

FROM employees e1;

-- Execution: