How to Use Subqueries in PostgreSQL

Galaxy Glossary

How do I write and optimize subqueries in PostgreSQL?

A subquery is a SELECT statement nested inside another SQL statement to filter, join, or compute derived data.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why use a subquery instead of multiple queries?

Subqueries let you keep logic in one statement, reduce network round-trips, and push computation to the database. You can filter, aggregate, or join derived data without temporary tables.

What is the basic subquery syntax?

Place a SELECT inside parentheses where a value list is accepted. Common spots are the SELECT list, FROM clause, and WHERE filters.

How does a correlated subquery work?

A correlated subquery references columns from the outer query and runs once per outer row.Use it when each row needs a different derived value.

How do I filter rows with an IN subquery?

Use WHERE column IN (SELECT …) to keep rows whose value exists in the subquery result.

How can I join a subquery for derived tables?

Put the subquery in the FROM clause, give it an alias, and join it like a regular table.This is great for pre-aggregated data.

How do I update rows using a subquery?

Combine UPDATE with SET column = (SELECT …) or use FROM to join to a subquery that returns the new values.

What are best practices for subqueries?

Select only needed columns, add proper indexes, avoid SELECT *, and favor EXISTS over IN for large result sets to improve performance.

.

Why How to Use Subqueries in PostgreSQL is important

How to Use Subqueries in PostgreSQL Example Usage


-- Find products that sold more than the average quantity per order
SELECT p.id, p.name, p.price
FROM Products p
WHERE p.id IN (
    SELECT oi.product_id
    FROM OrderItems oi
    GROUP BY oi.product_id
    HAVING SUM(oi.quantity) > (
        SELECT AVG(q.sum_qty)
        FROM (
            SELECT order_id, SUM(quantity) AS sum_qty
            FROM OrderItems
            GROUP BY order_id
        ) q
    )
);

How to Use Subqueries in PostgreSQL Syntax


-- Filter customers who placed at least one order
SELECT id, name, email
FROM Customers
WHERE id IN (SELECT customer_id FROM Orders);

-- Correlated subquery to get last order date per customer
SELECT c.id,
       c.name,
       (SELECT MAX(order_date)
        FROM Orders o
        WHERE o.customer_id = c.id) AS last_order
FROM Customers c;

-- Derived table for total spent per customer
SELECT t.customer_id, t.total_spent
FROM (
    SELECT o.customer_id, SUM(total_amount) AS total_spent
    FROM Orders o
    GROUP BY o.customer_id
) t;

Common Mistakes

Using SELECT * in subqueries: pulling unnecessary columns bloats memory and slows execution. Always select only the columns you need.
Relying on IN with large result sets: IN materializes all rows before comparison. Switch to EXISTS or JOIN for better performance.

Frequently Asked Questions (FAQs)

Can a subquery return multiple columns?

Yes, but only when used in the FROM clause or with row-valued comparisons. Single-value contexts like WHERE IN need one column.

Are subqueries slower than JOINs?

Not necessarily. The planner often rewrites subqueries as joins. Performance depends on indexes, row counts, and whether the subquery is correlated.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie