Scalar and correlated subqueries

A subquery can compute a reference value used by an outer query. The worked example selects roles whose known salary exceeds the global observed average. That average is approximately 84.29, so identities 1, 4, 5 and 6 qualify. The unknown observation is not turned into a numeric reference.

Name the comparison population before choosing the subquery. A global average and a company-specific average answer different questions. A query can be syntactically correct and still compare each role with the wrong peer population.

The aggregate subquery returns one scalar value. AVG produces a single aggregate result even when its input is empty, though the result would then be null. The outer salary comparison keeps true rows and naturally excludes unknown salaries under the stated policy.

A correlated subquery refers to an outer row, such as its company identity, to choose the reference population. The logical meaning is per outer row; the optimizer may transform execution. Do not claim that every correlated query necessarily performs a full repeated scan.

The counterexample compares against the maximum instead of the mean and returns no rows under a strict greater-than rule. This illustrates that the reference statistic is part of the contract. Another danger is a nonaggregate scalar subquery that unexpectedly returns several rows.

PostgreSQL rejects a multirow scalar result; permissive behavior in another engine is not a portable selection rule. Prove uniqueness or use a meaningful aggregate. Adding an arbitrary LIMIT 1 only hides ambiguity unless its ordering is itself part of the question.

Find roles whose salary exceeds their own company's observed average. Predict identities 1, 4 and 6. Correlate on company_id and keep the same unknown-value policy. Role 5 exceeds the global average but not its company average, making it an important negative case.

Write the inner company filter before composing the outer comparison. This task changes the reference population only. Do not add active status silently: the prompt uses all stored role records, including archived observations, for each company's teaching average.

Step 1: Find the company peer key. Step 2: Use that key inside the scalar aggregate comparison.

The solution computes company-specific averages through a correlated aggregate. Company A's average is about 86.67, B's is 60 and C's is 105. The above-average identities are therefore 1, 4 and 6. Company D has no outer role row to classify.

The acceptance result includes a role that changes classification between global and local comparisons. This is stronger than a fixture where both queries happen to select the same identities. For larger data, compare a preaggregated join plan with the correlated form without assuming either is always faster.

Explain scalar cardinality, correlation and comparison population separately. A correct interview answer should identify the one-row guarantee of the inner expression and show how a changed peer population affects one concrete role. Avoid treating nested SQL as mysterious procedural code.

The next lesson uses subqueries for existence rather than numeric values. That distinction avoids multiplying parent entities and provides a reliable negative-match pattern when staging data can contain unknown keys.

Optional stretch — after the core task works: Rewrite the practice using a grouped company-average join and compare the exact outputs.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.