PostgreSQL

PostgreSQL Subquery

PostgreSQL is one of the most powerful and easy-to-use database management tools. It provides simple and intuitive queries, allowing you to master and recall SQL statements easily.

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:

SELECT col1, col2 FROM tb1 WHERE expr (SELECT col1, col2 FROM tb1);

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:

select name, two_letter, country_id FROM country WHERE country_id IN (SELECT country_id WHERE country_id > 800);

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:

Example 2

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:

select name, two_letter, country_id FROM country WHERE country_id IN (SELECT country_id WHERE country_id > 800 AND two_letter LIKE 'U%');

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.

Example 3

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:

select payment_id, amount <strong>FROM</strong> payment WHERE payment_id <strong>IN</strong> (SELECT payment_id <strong>FROM</strong> payment GROUP BY payment_id LIMIT 5);

The above query will return the results grouped by payment id.

Example 4

We can also use PostgreSQL with other statements. For example, we can delete specific records returned from a subquery.

DELETE FROM film WHERE EXISTS (SELECT film_id FROM film WHERE film_id > 100);
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.

Subquery Points

There are few points to keep in mind when working with PostgreSQL subqueries.

  1. Enclose the subqueries with parenthesis.
  2. Ensure the subquery returns a value because the outer query will function on the result of the subquery.
  3. You can use subqueries with various SQL operators as long as they do not return a null value.

Closing

In this guide, we discussed how to tie queries in PostgreSQL without using separate queries.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list