Scalar subqueries in PostgreSQL - 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

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 do scalar subqueries differ from regular subqueries?

Scalar subqueries return a single value, while regular subqueries can return multiple rows and columns. Scalar subqueries are often used as expressions in SQL statements.

intermediate

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

SELECT name, (SELECT MAX(score) FROM exams WHERE exams.student_id = students.id) AS max_score FROM students;

This query lists each student's name and their highest exam score. The scalar subquery finds the maximum score for each student by matching student IDs.

beginner

Where can scalar subqueries be used in SQL statements?

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

What does a scalar subquery return?

A Multiple rows and columns

B Exactly one value (one row, one column)

C Only multiple rows

D Only multiple columns

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

Which SQL clause can contain a scalar subquery?

A CREATE TABLE

B FROM clause

C SELECT list

D ALTER TABLE

Scalar subqueries can be used in the SELECT list to provide a single value per row.

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

A An error occurs

B The extra rows are ignored

C Only the first row is used

D The query runs normally

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

In this query, what is the role of the scalar subquery?<br>

SELECT product_name, (SELECT AVG(price) FROM products) AS avg_price FROM products;

A Calculate average price once for all rows

B Calculate average price per product

C Return multiple prices

D Filter products by price

The scalar subquery calculates the average price once and shows it alongside each product.

Which of these is a valid use of a scalar subquery?

A INSERT INTO products VALUES (SELECT * FROM old_products)

B FROM (SELECT * FROM products)

C SELECT * FROM (SELECT * FROM products)

D WHERE price > (SELECT MAX(price) FROM products)

Using a scalar subquery in a WHERE clause to compare a value is valid.

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

Think about a subquery that returns a single number or value to use in a condition or calculation.

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

Consider how to limit the subquery result to a single value.