Using Subqueries in PostgreSQL

I will be using PostgreSQL and pgAdmin4 for this blog post.
I’ve chosen to use the Northwind database mainly because it’s well-known and it’s available for free!

What is a Subquery?

Why Should You Use a Subquery?

  • Subqueries can divide a complex query into isolated logical parts
  • It is easier to understand and code maintenance is also easier.
  • Subqueries allow you to use the results of another query in the outer query.
  • In some cases, subqueries can replace complex joins and unions.

When Should You Use a Subquery?

Different Types of Subqueries

What Do Subqueries Return?

Scalar Subqueries

Here is an example of a scalar subquery.
We will only run the inner query. In pgAdmin4 you can highlight any portion of a query and run it on its own.
As expected, 1 column and 1 row.

Column Subqueries

Here is a column subquery using an IN operator.
Let’s just run the inner query and see what we get.
As expected, one column and multiple rows.

Table Subqueries

Here is a table subquery with a CROSS JOIN clause.
Let’s just run the inner query and see what we get.
As expected, multiple columns and multiple rows.

Subquery Placement

Subquery in the SELECT clause

A sample query with a subquery nested in the SELECT clause.

Subquery in the FROM clause

A sample query with a subquery nested in the FROM clause. Any subqueries in the FROM clause must have an alias.

Subquery in the WHERE clause

A sample query with a subquery nested in the WHERE clause.

Final Thoughts

  • You must enclose a subquery in parenthesis.
  • A subquery must include a SELECT clause and a FROM clause.
  • A subquery can include optional WHERE, GROUP BY, and HAVING clauses.
  • A subquery can be used in almost any SQL clause.
  • If a JOIN seems like a better choice, go for it. JOIN holds certain advantages over subqueries. For example, JOIN executes faster than subqueries and the retrieval time of the query using joins almost always will be faster than that of a subquery.

--

--

--

Data Analyst | Data Enthusiast | Problem-Solver | Brooklyn NY https://www.linkedin.com/in/christian-rios-chambi/

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Marketing 101: Vanity Links

Agile World S1 E16 on meetup crashing, celebrating our amazing donors and the Agile20Reflect Red…

Cassandra 101 (Introduction)

Updates to Entry Definitions, Conductor Network Configuration, and More

How to Keep Software Dependencies Up-to-Date with Renovate

Palindrome in programming — Ruby

Beginner’s Introduction to Git for testers.

3 things that tell Product Owners that they need an Agile product roadmap

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Christian Rios-Chambi

Christian Rios-Chambi

Data Analyst | Data Enthusiast | Problem-Solver | Brooklyn NY https://www.linkedin.com/in/christian-rios-chambi/

More from Medium

Let’s Analyze IPL statistics using MongoDB Charts

Data Modeling Series: Relational Databases(SQL Databases)

AWS Athena vs Redshift: How to differentiate and leverage them

Migrate data from one schema/table to another in Amazon Redshift