Skip to content
Quantoros

What is a scalar subquery?

SQLLevel: IntermediateSubqueries & CTEs

What is a scalar subquery?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

A scalar subquery returns exactly one row and one column. It can be used anywhere a single value is expected.

SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

If a scalar subquery returns more than one row, the database throws an error. If it returns no rows, it returns NULL.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions