In this tutorial, we will discuss how to use PostgreSQL subqueries. Subqueries allow you to create complex SQL queries that are heavily readable and easy to use.
What is a Subquery?
In PostgreSQL, a subquery refers to a query within a query. You can tie subqueries to typical queries such as SELECT, WHERE, FROM, HAVING, and more.
A subquery is also called an INNER Query.
The basic syntax for a PostgreSQL Subquery is:
In this case, the statements inside the parenthesis represent the subquery. They execute before the main or outer query executes.
Once the subquery returns the result, the main query processes it and returns the results to the user.
In PostgreSQL, the ORDER BY cannot be used inside a Subquery.
Example PostgreSQL Subquery
Let us illustrate the use of a subquery using the where clause. Suppose we have a database of the countries with columns: name, country_id, and two-letter representations.
We can use a subquery to get the countries where the id is greater than 800.
An example query is below:
In the above example, the subquery filters the countries with an id of greater than 800 and returns the result to the main query.
An example result is below:
Suppose we want to get countries with an id greater than 800 and start with the letter U.
We can tie the query inside a subquery as:
The above query works similarly to the one above. However, it checks for two conditions at once and returns the result to the outer query.
Although we cannot use the ORDER BY clause inside a PostgreSQL subquery, we can use a GROUP BY query to perform a similar function.
For example, consider the payment table in the sakila database. We can perform an action as:
The above query will return the results grouped by payment id.
We can also use PostgreSQL with other statements. For example, we can delete specific records returned from a subquery.
SELECT * from film LIMIT 5;
In the above query, we delete the film where the film_id is greater than 100.
It is good to ensure that other people using the database have not referenced the records you wish to delete.
There are few points to keep in mind when working with PostgreSQL subqueries.
- Enclose the subqueries with parenthesis.
- Ensure the subquery returns a value because the outer query will function on the result of the subquery.
- You can use subqueries with various SQL operators as long as they do not return a null value.
In this guide, we discussed how to tie queries in PostgreSQL without using separate queries.