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

-- BASIC SCALAR SUBQUERY SYNTAX

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

-- Pattern: (SELECT single_value FROM table WHERE conditions)

-- Must return: 1 row, 1 column

-- Returns: A single value or NULL

-- 1. SCALAR SUBQUERY IN SELECT CLAUSE

-- Adds a calculated column to each row

SELECT

name,

salary,

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

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

FROM employees;

-- How it works:

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

-- 2. Value added to each row as company_avg column

-- 3. Same subquery reused in calculation (cached)

-- Result:

-- name | salary | company_avg | diff_from_avg

-- Alice | 95000 | 75000 | 20000

-- Bob | 85000 | 75000 | 10000

-- Charlie | 65000 | 75000 | -10000

-- 2. SCALAR SUBQUERY IN WHERE CLAUSE

-- Filters rows based on calculated value

SELECT

name,

salary,

department

FROM employees

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

-- How it works:

-- 1. Subquery calculates: AVG(salary) → 75000

-- 2. WHERE becomes: WHERE salary > 75000

-- 3. Only rows with salary > 75000 returned

-- Returns only above-average employees

-- 3. MULTIPLE SCALAR SUBQUERIES

-- Each executes once, results cached

SELECT

name,