Subquery in PostgreSQL

A subquery is a SELECT statement nested inside another SQL statement, enclosed in parentheses. PostgreSQL evaluates the inner query first and passes its result to the outer query as a condition, a derived table, or a scalar value. Subqueries let you break a complex retrieval problem into composable steps without creating intermediate tables or materialized views.

Syntax

The most common placement is inside a WHERE clause:

SELECT select_list
FROM table_name
WHERE column operator (
  SELECT column
  FROM other_table
  WHERE condition
);

Subqueries can also appear in the FROM clause (derived table), the SELECT list (scalar subquery), and inside HAVING clauses.

Practical Example

Create two tables — products and warehouses — that represent an inventory domain:

CREATE TABLE warehouses (
  warehouse_id  SERIAL PRIMARY KEY,
  region        VARCHAR(50) NOT NULL,
  capacity      INT         NOT NULL
);

CREATE TABLE products (
  product_id    SERIAL PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  warehouse_id  INT REFERENCES warehouses (warehouse_id),
  stock_qty     INT         NOT NULL DEFAULT 0,
  unit_cost     NUMERIC(10, 2)
);

INSERT INTO warehouses (region, capacity) VALUES
  ('North', 500),
  ('South', 300),
  ('East',  800);

INSERT INTO products (name, warehouse_id, stock_qty, unit_cost) VALUES
  ('Widget A',    1, 120, 4.50),
  ('Widget B',    1,  80, 6.00),
  ('Gadget Pro',  2, 200, 22.00),
  ('Gadget Lite', 2,  40, 11.00),
  ('Component X', 3, 350, 1.75),
  ('Component Y', 3,  90, 3.25);

Single-value subquery with a comparison operator

Find all products stored in the highest-capacity warehouse without hard-coding its ID:

SELECT name, stock_qty
FROM products
WHERE warehouse_id = (
  SELECT warehouse_id
  FROM warehouses
  ORDER BY capacity DESC
  LIMIT 1
);

     name      | stock_qty
---------------+-----------
 Component X   |       350
 Component Y   |        90

PostgreSQL runs the inner query first (returning warehouse_id = 3), then filters the outer query with that value.

Multi-value subquery with IN

Find all products stored in warehouses with more than 400 units of capacity:

SELECT name, stock_qty, unit_cost
FROM products
WHERE warehouse_id IN (
  SELECT warehouse_id
  FROM warehouses
  WHERE capacity > 400
)
ORDER BY stock_qty DESC;

     name      | stock_qty | unit_cost
---------------+-----------+-----------
 Component X   |       350 |      1.75
 Widget A      |       120 |      4.50
 Component Y   |        90 |      3.25
 Widget B      |        80 |      6.00

The subquery returns a set of IDs; the outer query filters with IN.

Subquery Positions

Testing with Vela

Subqueries that reference large tables — especially correlated subqueries — can have unexpected performance characteristics in production once data grows. Vela’s database branching lets you clone production data to a branch, run EXPLAIN ANALYZE against the exact dataset, and tune the query before it ever touches the live database. Run the branch query during off-peak hours without any risk to production queries.

Production Tips

• Use a scalar subquery (returns one row, one column) with =; use IN or ANY when the subquery may return multiple rows.
• Correlated subqueries re-execute for every outer row. On large tables, replace them with a JOIN or a pre-aggregated CTE.
• Profile all non-trivial subqueries with EXPLAIN ANALYZE to see actual row counts and execution times before deploying.
• When a subquery appears more than once in a query, refactor it into a CTE (WITH name AS (...)) so PostgreSQL evaluates it once and the code is easier to maintain.
• In NOT IN patterns, be careful of NULL values in the subquery result — a single NULL in the set causes the entire NOT IN condition to return NULL for every row. Prefer NOT EXISTS when NULLs are possible.