Scalar subquery in WHERE

The first corridor of the Labyrinth is the simplest. A scalar subquery is one that returns exactly one value - one row and one column. Thanks to that we can use it anywhere SQL expects a single number or piece of text.

Comparing with the average

Most often we put it in the WHERE clause to compare each row with the result of a calculation:

1SELECT tytul, cena
2FROM ksiazki
3WHERE cena > (SELECT AVG(cena) FROM ksiazki);

The subquery

(SELECT AVG(cena) FROM ksiazki)

returns a single number - the average price. The outer query compares each book's price with it and keeps only those above the average.

Other aggregate functions

Functions such as

MAX

,

MIN

,

COUNT

work beautifully in a scalar subquery:

1SELECT tytul
2FROM ksiazki
3WHERE rok_wydania = (SELECT MAX(rok_wydania) FROM ksiazki);

This query finds the newest book in the Library - the one with the greatest year of publication. If we wanted the oldest, we would use

MIN

instead of

MAX

.

Mind the scalar rule

A scalar subquery must return one value. If it returned many rows and we used the

=

or

>

operator, the Library would protest with an error. If you expect many results - you will need the

IN

operator, which you will meet in the next corridor.

@name