How to SUBQUERY in PostgreSQL

What is a subquery in PostgreSQL?

A subquery is a SELECT statement enclosed in parentheses that returns a result set consumed by the outer query. You can place it in SELECT, FROM, WHERE, or HAVING clauses to compute values, filter rows, or build derived tables.

Why choose a subquery over a JOIN?

Use a subquery when the nested logic is easier to read, when you need aggregate results in a single column, or when the outer query depends on the result of the inner query. Use JOINs for set-based combination of rows to avoid unnecessary nesting.

What is the basic subquery syntax?

Enclose the inner SELECT in parentheses. It may be correlated (referencing outer columns) or uncorrelated. Place it where a value list, table, or scalar expression is expected.

Syntax Patterns

-- In WHERE
SELECT *
FROM Orders o
WHERE o.customer_id IN (SELECT id FROM Customers WHERE created_at > now() - interval '30 days');

-- In FROM (derived table)
SELECT s.customer_id, s.recent_total
FROM (
SELECT customer_id, SUM(total_amount) AS recent_total
FROM Orders
WHERE order_date > now() - interval '30 days'
GROUP BY customer_id
) AS s;

-- Scalar subquery in SELECT
SELECT id,
(SELECT COUNT(*) FROM OrderItems oi WHERE oi.order_id = o.id) AS item_cnt
FROM Orders o;


How do I filter rows with a subquery?

Use IN, EXISTS, =, <>, >=, or < operators. EXISTS is fastest when the inner query only needs to check existence.

SELECT *
FROM Products p
WHERE EXISTS (
SELECT 1 FROM OrderItems oi WHERE oi.product_id = p.id AND oi.quantity > 5
);


How can I use scalar subqueries in SELECT?

Return a single value to enrich each row. Ensure the subquery returns at most one row or use aggregation to avoid errors.

Can I UPDATE or DELETE using subqueries?

Yes. Use a subquery to compute the filter set or new values.

UPDATE Products p
SET stock = stock - s.qty
FROM (
SELECT product_id, SUM(quantity) AS qty
FROM OrderItems
GROUP BY product_id
) AS s
WHERE p.id = s.product_id;


Best practices for writing subqueries

1) Prefer EXISTS over IN for correlated checks. 2) Index columns referenced in subqueries. 3) Alias derived tables clearly. 4) Validate that scalar subqueries return one row.

Common mistakes to avoid

Misusing subqueries where JOINs are simpler, or forgetting to correlate inner queries leading to cartesian results.