How to Use Subqueries in PostgreSQL

How do I use subqueries effectively in PostgreSQL?

A subquery is a SELECT statement nested inside another SQL statement to filter, project, or derive data.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

PostgreSQL Subquery Guide

Use subqueries to nest SELECT statements inside another query for filtering rows, calculating values, and building derived tables.

What is a PostgreSQL subquery?

A subquery, also called an inner query, is a SELECT statement enclosed in parentheses that appears in SELECT, FROM, WHERE, or HAVING clauses of an outer query.PostgreSQL executes the subquery first, then feeds its result to the outer query.

What is the basic syntax?

(SELECT column_list FROM table_name WHERE conditions)

Place the parentheses exactly as shown.Alias the subquery when it appears in the FROM clause so the outer query can reference its columns.

Where can I place subqueries?

In the SELECT list

Use scalar subqueries to compute single values, like totals or flags, for every row returned by the outer query.

In the FROM clause (derived table)

Use a subquery as a temporary table to simplify complex joins or aggregation pipelines.Always give it an alias.

In the WHERE or HAVING clause

Filter rows with IN, EXISTS, = ANY (array comparators), or comparison operators. Correlated subqueries here can reference outer-query columns.

How to write a correlated subquery?

Include a reference to a column from the outer query inside the subquery’s WHERE clause.PostgreSQL runs the subquery once per outer-query row.

How to avoid performance issues?

Index the columns used to correlate or filter, prefer EXISTS over IN for large result sets, and consider rewriting to JOINs when row counts explode.Analyze the plan with EXPLAIN.

What are best practices?

Keep subqueries small, alias derived tables, return only needed columns, and move expensive calculations out of correlated subqueries when possible.

Common mistakes and how to fix them

Mistake 1 – Unaliased derived table: Every subquery in FROM must have an alias.Fix: add AS alias.

Mistake 2 – Subquery returns multiple rows where scalar expected: Use IN/= ANY or add aggregation/limit to force a single row.

Quick reference

Scalar subquery: returns a single value
Row subquery: returns a single row
Table (derived) subquery: returns a result set used like a table
Correlated subquery: references outer query columns

Why How to Use Subqueries in PostgreSQL is important

How to Use Subqueries in PostgreSQL Example Usage


-- Show customers whose total order amount exceeds the average order amount
SELECT c.customer_id,
       (SELECT SUM(o.amount) FROM orders o WHERE o.customer_id = c.customer_id) AS total_spent
FROM   customers c
WHERE  (SELECT SUM(o.amount)
        FROM   orders o
        WHERE  o.customer_id = c.customer_id) >
       (SELECT AVG(amount) FROM orders);

How to Use Subqueries in PostgreSQL Syntax


-- General form
SELECT outer_cols
FROM   outer_table AS o
WHERE  o.col OPERATOR (SELECT inner_col FROM inner_table WHERE condition);

-- Derived-table form
SELECT d.*
FROM   (SELECT col1, col2 FROM base WHERE condition) AS d
WHERE  d.col1 > 0;

Common Mistakes

Forgetting to alias a subquery in the FROM clause. Without an alias, the outer query cannot reference its columns. Fix: add AS my_alias after the closing parenthesis.
Using = with a subquery that returns multiple rows. PostgreSQL raises ERROR: more than one row returned by a subquery used as an expression. Fix: use IN, = ANY(array), LIMIT 1, or aggregate inside the subquery.

Frequently Asked Questions (FAQs)

Is a subquery slower than a JOIN?

Not inherently. PostgreSQL’s planner can often transform subqueries into JOINs. Still, correlated subqueries may run once per outer row, so test with EXPLAIN and compare to JOIN rewrites.

Can I nest subqueries multiple levels deep?

Yes. PostgreSQL supports arbitrary nesting depth, limited only by statement complexity and memory. Keep logic readable by refactoring into CTEs when depth grows.

When should I use EXISTS instead of IN?

EXISTS stops on the first match and performs better when the inner set is large and not indexed uniquely. IN can be faster for small, static lists returned by the subquery.