PostgreSQL Subqueries

In PostgreSQL, a subquery is a query nested within another query, also known as an inner query.

Typically, we use subquery to build more complex SQL statements. For example, you can use subqueries in some operators such as EXISTS, IN, ANY, ALL etc. You can also compare the results of subqueries with values directly.

Using subqueries in the PostgreSQL EXISTS operator

PostgreSQL EXISTS operators require a subquery as an operand to check if this subquery returns a row.

For example, to find those languages ​​used in the film table from the language table, you would use the following statement:

SELECT *
FROM language
WHERE EXISTS(
    SELECT *
    FROM film
    WHERE film.language_id = language.language_id
  );

 language_id |         name         |     last_update
-------------+----------------------+---------------------
           1 | English              | 2006-02-15 05:02:19
(1 row)

In the above statement, the following query is a subquery:

SELECT *
FROM film
WHERE film.language_id = language.language_id

Using subqueries in the PostgreSQL IN operator

PostgreSQL IN operators also require a set as an operand, so you can also use subqueries as the operand on the right-hand side of the IN operator.

For example, you can use the following statement with the IN operator and a subquery to achieve the above requirement:

SELECT *
FROM language
WHERE language_id in(
    SELECT DISTINCT language_id
    FROM film
  );

 language_id |         name         |     last_update
-------------+----------------------+---------------------
           1 | English              | 2006-02-15 05:02:19
(1 row)

Here, the following statement is a subquery to find all languages ids used by films:

SELECT DISTINCT language_id
FROM film

Compare subqueries and values

For example, to count the number of films with a higher-than-average rent, you could use the following statement:

SELECT count(*)
FROM film
WHERE rental_rate > (
    SELECT avg(rental_rate)
    FROM film
  );

 count
-------
   659

Here, we use the following subquery to calculate the average rent of films:

SELECT avg(rental_rate)
FROM film

Then, compare the rent rental_rate and the result of the subquery in the WHERE clause.

Conclusion

PostgreSQL subqueries can help you build more complex SQL statements.