Scalar Subquery: Compare to Company Average

basic

Show each employee with company average salary and difference. Scalar subquery returns single value used for all rows.

SELECT

name,

department,

salary,

(SELECT AVG(salary) FROM employees) AS company_avg,

salary - (SELECT AVG(salary) FROM employees) AS difference_from_avg,

CASE

WHEN salary > (SELECT AVG(salary) FROM employees) THEN 'Above Average'

ELSE 'Below Average'

END AS status

FROM employees

ORDER BY salary DESC;

employees: 10 rows

Average salary: 75000

name | department | salary | company_avg | difference_from_avg | status

Alice | Engineering | 95000 | 75000 | 20000 | Above Average

Bob | Sales | 85000 | 75000 | 10000 | Above Average

Carol | Engineering | 75000 | 75000 | 0 | Below Average

Dave | Marketing | 65000 | 75000 | -10000 | Below Average

Eve | Sales | 55000 | 75000 | -20000 | Below Average

Scalar subquery (SELECT AVG(salary) FROM employees) returns single value: 75000. This subquery executes once, result is cached and reused for all rows. We use it three times in the query (in SELECT twice, in CASE once) but it only executes once. This is efficient. Shows each employee's salary compared to company average.

All

Scalar non-correlated subquery executes once. Very efficient. Result cached for all rows.

graph LR

A["Subquery:<br/>(SELECT AVG(salary))"] --> B["Returns: 75000<br/>Executes once"]

B --> C["Main query uses 75000<br/>for all employee rows"]

style B fill:#90EE90

Multi-Row Subquery with IN: Filter by List

basic

Find employees in NYC departments. Subquery returns list of department IDs, main query filters employees.

-- Find employees in NYC departments

SELECT

e.id,

e.name,

e.department_id,

d.name AS department_name

FROM employees e