Learning PostgreSQL 11 - Third Edition

Lateral subqueries

We discussed subqueries in Chapter 5, SQL Language. However, here, we'll go into detail about one specific pattern for using them.

It's very convenient to use subqueries in the select-list. They are used in SELECT statements, for example, to create calculated attributes when querying a table. Let's look at the car_portal database again. Suppose you need to query the car table and, for each car, you need to assess its age by comparing it with the age of other cars of the same model. Furthermore, you want to query the number of cars of the same model.

These two additional fields can be generated by scalar subqueries, as follows:

car_portal=> SELECT car_id, manufacture_year,  CASE WHEN manufacture_year <= ( SELECT avg(manufacture_year) ...