SQL and MySQL course Β· Module 8: Subqueries

Scalar subquery in WHERE

2 min read Β·
In this lesson3

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:

SELECT tytul, cena
FROM ksiazki
WHERE 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:

SELECT tytul
FROM ksiazki
WHERE 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.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What does a scalar subquery such as (SELECT AVG(cena) FROM ksiazki) return?

  2. 2. Which aggregate function in a scalar subquery returns the most recent year of publication of a book?

Hands-on tasks in the game

  • Code editor

    Write a query that retrieves from the ksiazki table all columns of those books whose price is greater than the average price (AVG) of all books. Compute the average in a subquery.

  • Horizontal ordering

    Arrange in one line (left to right) the start of the condition comparing the price with the average from a subquery:

  • Code editor

    Write a query that retrieves from the ksiazki table the tytul column of those books whose rok_wydania equals the greatest year of publication (MAX) in the table. Compute the maximum in a subquery.

  1. QuestionScalar subquery
  2. QuestionAggregate functions in a subquery
  3. Coding taskBooks more expensive than average
  4. Put in orderComparing the price with the average
  5. Coding taskThe newest book

Useful articles