Scalar subqueries in MySQL - 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 wherever a single value is expected, like in SELECT, WHERE, or SET clauses.

beginner

How does a scalar subquery differ from other subqueries?

A scalar subquery returns a single value, while other subqueries can return multiple rows or columns. Scalar subqueries are used as single values in expressions.

beginner

Example: What does this scalar subquery return?<br>

SELECT (SELECT COUNT(*) FROM employees) AS total_employees;

It returns the total number of employees as a single value in the column 'total_employees'.

intermediate

Can a scalar subquery return more than one row or column?

No. If a scalar subquery returns more than one row or column, it causes an error because it expects exactly one value.

intermediate

Where can scalar subqueries be used in SQL statements?

Scalar subqueries can be used in SELECT lists, WHERE clauses, HAVING clauses, and SET clauses where a single value is expected.

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, which means one row and one column.

Where can you use a scalar subquery in SQL?

A Only in JOIN conditions

B In places expecting a single value, like SELECT or WHERE clauses

C Only in CREATE statements

D Only in GROUP BY clauses

Scalar subqueries are used where a single value is expected, such as in SELECT or WHERE clauses.

What happens if a scalar subquery returns more than one row?

A It returns the first row only

B It returns all rows concatenated

C SQL returns an error

D It ignores extra rows silently

If a scalar subquery returns more than one row, SQL throws an error because it expects exactly one value.

Which of these is a valid scalar subquery?

A SELECT (SELECT MAX(salary) FROM employees) AS max_salary;

B SELECT * FROM (SELECT * FROM employees);

C SELECT (SELECT name, age FROM employees);

D SELECT (SELECT salary FROM employees);

Option A returns a single value (maximum salary), which is valid for a scalar subquery.

Why use scalar subqueries?

A To delete data

B To return multiple rows for joins

C To create new tables

D To get a single value from another query to use in expressions

Scalar subqueries help get a single value from another query to use in expressions or conditions.

Explain what a scalar subquery is and give an example of where you might use it.

Think about a query inside parentheses that returns one number or text.

What error occurs if a scalar subquery returns more than one row? How can you avoid this?

Consider how to limit results to one row.