scribase
All posts
·Scribasepostgressqlbranching

SQL subquery in Postgres: types, examples and safe testing

Scalar, IN, EXISTS and derived-table subqueries in Postgres, when a join or a CTE is the better tool, and how to test the SQL on a database branch.

A SQL subquery is a SELECT nested inside another SQL statement. Postgres will run the inner query, use its result in the outer query, and return one answer. You will meet subqueries in WHERE, FROM, SELECT, and sometimes in INSERT, UPDATE or DELETE.

This article stays with PostgreSQL, because that is the database Scribase gives every project. The examples use a small shop schema you can recreate on a disposable database branch. Production does not need to be involved.

If you already know you want a named intermediate result, skip ahead to the companion piece on common table expressions. If you only need the syntax on one screen, the SQL subquery quick reference is shorter. If you want the mechanics of a copy-on-write Postgres branch, see Postgres branching and branch per task.

A schema you can type in

CREATE TABLE customers (
  id          bigint PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id           bigint PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (id),
  status       text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
  total_cents  integer NOT NULL CHECK (total_cents >= 0),
  placed_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id    bigint NOT NULL REFERENCES orders (id),
  sku         text NOT NULL,
  qty         integer NOT NULL CHECK (qty > 0),
  price_cents integer NOT NULL CHECK (price_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

Nothing here is a Scribase system table. It is ordinary application SQL, which is the point: the same statements run on a hosted project, on a preview branch, and in local Postgres.

Four shapes of subquery

People say "sql subquery" as if it were one construct. In Postgres it is four common shapes, and they are not interchangeable.

1. A scalar subquery

A scalar subquery returns one column and at most one row. You can use it in a SELECT list or on the right-hand side of a comparison.

SELECT
  c.email,
  (
    SELECT count(*)
    FROM orders o
    WHERE o.customer_id = c.id
      AND o.status = 'paid'
  ) AS paid_orders
FROM customers c;

If the inner query ever returns two rows, Postgres raises an error: more than one row returned by a subquery used as an expression. That is a feature. If you meant several rows, use IN, EXISTS, or a join, not a scalar subquery.

A scalar subquery that returns no rows becomes NULL, not zero, unless you wrap it in COALESCE.

2. A subquery with IN (or NOT IN)

This inner query returns a column of values.

SELECT email
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'paid'
    AND placed_at >= now() - interval '30 days'
);

IN is straightforward when the list is small and not nullable in a confusing way. NOT IN and NULL do not mix well: if the inner query produces a single NULL, NOT IN will not return the rows you expect. Prefer NOT EXISTS for the negative case. A later article in this series covers EXISTS and IN side by side.

3. A subquery in FROM (a derived table)

Here the inner query acts as a table. You must give it an alias.

SELECT customer_id, order_count
FROM (
  SELECT customer_id, count(*) AS order_count
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
) AS paid
WHERE order_count >= 3;

This is often the first form people write, and then later rewrite as a common table expression so the inner SELECT has a name. Postgres can produce the same plan for both. The difference is mostly for the reader.

4. EXISTS and NOT EXISTS

EXISTS only cares whether the inner query would return a row. It does not need to count them.

SELECT c.email
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.status = 'cancelled'
);

This is the usual shape for "customers who have at least one cancelled order." It is a correlated subquery: the inner query refers to c.id from the outer row. Postgres can stop scanning as soon as it finds the first match.

Correlated and uncorrelated

An uncorrelated subquery does not mention the outer query. Postgres can run it once and reuse the result.

-- Uncorrelated: the average is computed once.
SELECT id, total_cents
FROM orders
WHERE status = 'paid'
  AND total_cents > (
    SELECT avg(total_cents)
    FROM orders
    WHERE status = 'paid'
  );

A correlated subquery is evaluated in the context of the current outer row. That is not automatically slow. On a well-indexed orders (customer_id, status) the EXISTS example above is often the plan you want. On a missing index it can become a nested loop over the whole table.

When a correlated subquery is hard to read, rewrite it as a join or a CTE and compare the plans with EXPLAIN (ANALYZE, BUFFERS). A later article in this series is reserved for reading those plans. Until then, the short version is: look at actual rows and whether an index was used, not at how the SQL is spelled.

When a subquery is the wrong tool

Use a join when you need columns from both tables in the result, not only a yes-or-no or a single aggregate.

-- Need the order id and the email? Join.
SELECT c.email, o.id AS order_id, o.total_cents
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

Use a CTE when the inner query is reused, or when a reviewer (human or agent) needs a name for the intermediate result. A nested derived table three layers deep is still valid SQL. It is also a common place for a mistaken WHERE or a doubled row count to hide.

Do not use a scalar subquery to fetch a column you could have joined. If the relationship is one-to-many and you forgot to aggregate, it will fail at runtime when a second child row appears.

Trying the SQL on a Scribase branch

A subquery that looks right on a handful of seed rows can be wrong on real data: doubled counts after a join, a scalar subquery that suddenly returns two rows, a NOT IN list that contains NULL. The safe place to find that out is a database branch, not production.

Scribase gives every project copy-on-write Postgres branches. A branch is a full, writable Postgres that starts as a copy of another database without copying every page first. Writes on the branch stay on the branch. The parent never sees them. Branches and preview environments are included in the project price, including the free project and Hobby at five dollars a month.

The branch-per-task loop is the one to use when a coding agent is making the change:

  1. Create a preview environment. The default data mode is sanitized: a copy of the parent, with configured columns anonymized. Use schema_only if you will load fixtures yourself, or snapshot only when you truly need exact rows.
  2. Point the app or psql at the preview URL and keys.
  3. Run the subquery, and EXPLAIN (ANALYZE, BUFFERS) if the statement is heavy.
  4. If you also changed schema (a new index that makes the subquery cheap, or a constraint that makes a scalar subquery safe), register the branch, review scribase branch diff, and dry-run the merge.
  5. Merge only after you approve it. Merges carry schema changes. They do not pour the branch's rows back into production. If you need to move data, write that script on purpose.
  6. Delete the preview, or let its TTL remove it.

In the CLI, a typical preview looks like the branch-per-task docs: scribase env create, scribase branch register, scribase migrate ... --apply, then scribase branch diff and scribase branch merge --dry-run before the real merge. Over MCP, the same steps are typed tools, and writes wait for an explicit confirm.

Locally, with no account, you can do the smaller version:

scribase branch new try-subqueries
psql "$(scribase branch url try-subqueries)"
# create the tables, run the statements, then:
scribase branch delete try-subqueries

That is enough for this article's examples.

Common mistakes

  • Using a scalar subquery for a one-to-many relationship, then hitting more than one row returned in production.
  • Filtering a LEFT JOIN in WHERE on the right-hand table, which is a join problem rather than a subquery problem, but often appears in the same generated SQL. A later article covers that directly.
  • Writing NOT IN (SELECT nullable_column ...) and getting an empty result.
  • Correlating on the wrong column, so every outer row sees the same inner set, or none of them do.
  • Shipping the query with no index on the correlation column, then discovering the cost only after launch.

A branch will not invent the right SQL for you. It will let you see the failure before a production role runs the statement.

The next article in this series is common table expressions, the named form of many of the derived tables above. After that come UNION and UNION ALL and ORDER BY DESC. For the database underneath the examples, start with Postgres branching, branch per task, and the Scribase guides.

To try this on a hosted project, start at scribase.com. One project is free, with no card and no time limit. A temporary project via npx scribase init --temp needs no account.