POSTGRESQL
SUBQUERY

PostgreSQL Subquery - A Guide

intro

Subqueries make up an integral part of the work of every database developer. It is widely known that subqueries are just queries within queries, but they’re so much more than that. This article will take you through everything you need to know – this is worth checking out.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

Subqueries are queries within queries. In other words, they‘re nested queries – one query is called the inner query, while another query (the one inside the inner query) is called an outer query. In the majority of the cases, here‘s how subqueries look like:

A Subquery in DbVisualizer
A Subquery in DbVisualizer

In other words, a subquery is a SELECT statement inside of another SELECT statement. Subqueries often are used to select data from another table, and they also start with the IN clause. The syntax for the majority of subqueries is as follows (colX is the column that is used for search results - where we run search queries on):

Copy
        
1 SELECT t1.colA, t1.colB 2 FROM table1 AS t1 3 WHERE t1.colX IN ( 4 SELECT t2.colC, t2.colD 5 FROM table2 AS t2 6 WHERE t2.colY = 'Tutorial' 7 );

Similar to the query above, subqueries are helpful when we wish to search for data in a more sophisticated manner than is often provided by our preferred database management system. Subquery inquiries typically:

  • Determine what column is the most important (WHERE t1.colx IN) to search for.
  • Run another query on that column (SELECT … WHERE.)

Finally, databases are able to create "exotic" results—results that address more challenging issues than those answered by conventional queries—as a result of subqueries. Let us now look at some types of subqueries.

Types of Subqueries in PostgreSQL

There are types of subqueries in Postgres. The various types of subqueries are as follows:

  • Single-row Subqueries: A single-row subquery returns only one row. It is often used to retrieve a single value that will be compared with other values in a built query. When it does otherwise, our database management system with throw an error. Invariably, when it results in multiple rows, you’ll need to refactor your code to produce a single-row result set.
Single-row Subquery in DbVisualizer
Single-row Subquery in DbVisualizer

For example, the subquery we’ve built above, (SELECT column3 FROM table2 WHERE condition), returns a single row and a single column value. The outer query then compares column2 from table1 with the value returned by the subquery.

  • Multi-row Subqueries: Multi-row subqueries, on the other hand, return more than one row. The comparison operators used with a multi-row subquery, such as IN, ANY, or ALL, allow you to compare a single value or a set of values with the result of the subquery.
Multi-row Subquery in DbVisualizer
Multi-row Subquery in DbVisualizer

In the example above, the subquery (SELECT column3 FROM table2 WHERE condition) returns multiple rows and a single column value. The outer query uses the IN operator to compare column2 from table1 with the set of values returned by the subquery.

  • Correlated Subqueries: The other type of subquery is the correlated type which is focused on the columns of the outer query within the subquery. In the Correlated subquery, the subquery is executed for each row processed by the outer query. Let’s look at an example:
A Correlated Subquery in DbVisualizer
A Correlated Subquery in DbVisualizer

In this example, the subquery (SELECT MAX(column2) FROM table2 t2 WHERE t2.column3 = t1.column3) is correlated to the outer query by referencing t1.column3 from the outer query within the subquery. For each row that is processed in the outer query, the subquery is executed, and the result is based on the specific value of t1.column3 for that row.

Distinctions of Subqueries

  • Postgres supports the use of mathematical operators like =, !=, >, <, <=, >=, <>, and <=>
  • Subqueries in PostgreSQL can utilize the LIKE operator to perform pattern matching. Wildcards such as % (matches any sequence of characters) and _ (matches any single character) can be used within the subquery's LIKE statement.
  • The ability to use aggregate functions like MIN, MAX, and COUNT(*) within subqueries. These functions can be applied to a column or an expression in the subquery to calculate the minimum, maximum, or count of values, respectively.
  • And lastly, the ability to use subqueries like shown below:
Running Subqueries in DbVisualizer
Running Subqueries in DbVisualizer

The query we’ve built above is supposed to return a row, if it does otherwise, the database management system with throw an error. Let’s note that the query below would throw an error too:

A Problematic Query
A Problematic Query

However, if we want to send a request like “find all rows in a tutorial table that overlap with the rows in a tutorial_2 table”, we can use subqueries as well. See the example below:

Subqueries within a Subquery
Subqueries within a Subquery

When to Use A Subquery

By now you should have gained an understanding of subqueries. But when exactly should we use a subquery? This is a very important question. Let’s look at some specific use cases:

  • Comparing Data: Subqueries can be used to compare data between different tables or datasets. For example, you can retrieve records from one table based on values retrieved from another table by using a subquery for comparison.
  • Readability: In situations where your code is difficult to understand, subqueries can be used to break down complex logic into smaller subqueries to make your code more manageable and easier to understand.
  • Nesting Queries: Subqueries can be nested within other queries to build more complex and dynamic queries. You can use the results of one subquery as input for another subquery. They can be nested within many different queries like INSERTs, UPDATEs, INSERTs, DELETEs, and WHERE EXISTS operators to figure out if a column or a row exists.

Summary

In this blog, we’ve gone through the implications, upsides, and downsides of using subqueries in database management systems. We’ve told you what they are, how they work, and how best to utilize subqueries. We hope you’ve enjoyed reading this blog and that you’ll stay around our blog to learn more about databases in the future.

FAQs

What is a Subquery?

Subqueries are just queries within queries – subqueries or nested queries.

When do I use Subqueries?

Use a subquery when you need to select data and perform multiple operations on it at the same time. By way of explanation, use subqueries whenever the result that you wish to achieve requires you to use a subset of more than one table.

Can a subquery return multiple columns?

Yes. Subqueries can. This can be done by ensuring that the columns and their respective data types match their usage in the context of the subquery.

How do I use Subqueries?

To use subqueries is quite simple. Let’s say that a, b, x, d, e, and f are columns, a query like this would do:

Copy
        
1 SELECT a,b from my_table 2 WHERE x IN (SELECT d, e FROM my_table2 WHERE f = 'Tutorial');

Dbvis download link img
About the author
Leslie S. Gyamfi.
Leslie S. Gyamfi
Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

A Guide to the Postgres Not Null Constraint

author Leslie S. Gyamfi tags POSTGRESQL 3 min 2024-09-25
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

PostgreSQL NULLIF: Conditional Logic Made Easier

author Antonello Zanini tags NULLIF POSTGRESQL 6 min 2024-09-11
title

The Postgres UPDATE Statement: A Deep Dive

author Leslie S. Gyamfi tags POSTGRESQL 4 min 2024-09-05
title

PostgreSQL Sequence: A Beginner’s Guide

author Ochuko Onojakpor tags POSTGRESQL 11 min 2024-09-04
title

Cursors in PostgreSQL - A Guide

author Leslie S. Gyamfi tags Cursors POSTGRESQL 7 min 2024-09-03
title

Everything You Need to Know About the Postgres JSONB Data Type

author Antonello Zanini tags JSON POSTGRESQL 5 min 2024-09-02
title

PostgreSQL ADD COLUMN: Add One or More Columns to a Table

author Leslie S. Gyamfi tags ALTER POSTGRESQL 6 min 2024-08-22
title

Index Creation in SQL

author Lukas Vileikis tags MySQL POSTGRESQL SQL SERVER 7 min 2024-08-15

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗