Scalar subquery in SELECT in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a scalar subquery in SQL?

A scalar subquery is a subquery that returns exactly one value (one row and one column). It can be used in places where a single value is expected, such as in the SELECT list.

beginner

Can a scalar subquery return multiple rows or columns?

No, a scalar subquery must return exactly one row and one column. Returning more than one row or column causes an error.

intermediate

How is a scalar subquery used in a SELECT statement?

It is placed inside the SELECT clause to compute a value for each row, for example: SELECT name, (SELECT MAX(score) FROM scores WHERE scores.user_id = users.id) AS max_score FROM users;

intermediate

What happens if a scalar subquery returns no rows?

If a scalar subquery returns no rows, it returns NULL in the SELECT statement.

intermediate

Why use a scalar subquery instead of a JOIN?

Scalar subqueries can be simpler to write and understand when you need a single value related to each row, especially for aggregation or lookup. JOINs are better for multiple related rows.

What does a scalar subquery return?

A Exactly one value (one row, one column)

B Multiple rows and columns

C Only multiple rows

D Only multiple columns

A scalar subquery returns exactly one value, meaning one row and one column.

Where can you use a scalar subquery in SQL?

A Only in the GROUP BY clause

B Only in the WHERE clause

C Only in the FROM clause

D In the SELECT clause

Scalar subqueries are often used in the SELECT clause to compute a single value per row.

What happens if a scalar subquery returns no rows?

A It returns zero

B It returns NULL

C It causes an error

D It returns an empty string

If no rows are returned, the scalar subquery returns NULL.

What error occurs if a scalar subquery returns more than one row?

A Division by zero error

B Syntax error

C Subquery returns more than one row error

D No error, it works fine

Scalar subqueries must return one row; more than one row causes an error.

Which is a good use case for scalar subqueries?

A Getting a single aggregated value per row

B Joining multiple tables with many rows

C Updating multiple rows at once

D Creating indexes

Scalar subqueries are useful for getting one value per row, like an aggregate.

Explain what a scalar subquery is and how it is used in a SELECT statement.

Think about a small query inside the SELECT that gives one value per row.

What errors or results can happen if a scalar subquery returns zero or multiple rows?

Consider what SQL expects from a scalar subquery's output.