Learning PostgreSQL 11 - Third Edition

Subqueries

Subqueries are a very powerful feature of SQL. They can be used almost everywhere in queries. The most obvious way to use subqueries is in a FROM clause, as a source for the main query:

car_portal=> SELECT * FROM (  SELECT car_model_id, count(*) c   FROM car_portal_app.car   GROUP BY car_model_id) subqWHERE c = 1; car_model_id | c--------------+---            8 | 1           80 | 1...(14 rows)

When subqueries are used in a FROM clause, they must have an alias. In the preceding example, the subquery is given the name subq.

Subqueries are often used in SQL conditions in IN expressions:

car_portal=> SELECT car_id, registration_numberFROM car_portal_app.carWHERE car_model_id IN (  SELECT car_model_id FROM car_portal_app.car_model     WHERE make='Peugeot'); car_id ...