SQL readability rules for joins and subqueries

A formatter fixes layout: line breaks, indentation, keyword case. It can't choose your join syntax, name your aliases or decide whether a subquery should be a CTE. Those choices decide whether the next person can read the query, and a few of them decide whether it returns the right rows.

This guide applies those rules to one query. Every complete query below is real output from the SQL formatter with default settings, so the before and after differ only in choices a formatter can't make. Short fragments are excerpts of that output or, where the text says so, hand layout.

The query before

It lists paid orders above the average paid order, with the customer's region, for customers who have never had a refund:

SELECT
  a.id,
  a.name,
  b.total,
  d.name
FROM
  customers a,
  orders b,
  regions d
WHERE
  a.id = b.customer_id
  AND a.region_id = d.id
  AND b.status = 'paid'
  AND a.id NOT IN (
    SELECT
      customer_id
    FROM
      refunds
  )
  AND b.total > (
    SELECT
      AVG(total)
    FROM
      orders
    WHERE
      status = 'paid'
  );

It's formatted and still hard to read. The joins hide among the filters in WHERE. The aliases a, b and d say nothing about their tables. Two output columns are called name. The paid filter is written twice. And the NOT IN has a bug that depends on your data.

Use explicit JOIN syntax

Comma joins put join conditions in WHERE, mixed in with filters. Explicit JOINs keep each condition next to the table it joins and leave WHERE for filtering. They also make a forgotten condition harder to miss. Drop a.region_id = d.id from the comma version and the query still runs: every order is paired with every region, and the row count multiplies by the number of regions. In PostgreSQL, a JOIN without ON or USING is a syntax error. Some databases, MySQL among them, accept it as a cross join, so explicit syntax helps everywhere but only guarantees an error in some.

Don't mix the two styles. JOIN binds more tightly than the comma, so here the ON clause can only see customers and regions, and PostgreSQL and MySQL both reject the reference to o:

SELECT *
FROM orders o, customers c
JOIN regions r ON r.id = o.region_id;  -- error: o is not visible here

Lay out ON clauses with the join key first

Start the ON clause with the join key, with the joined table's column on the left: ON o.customer_id = c.id reads as "orders belonging to this customer". Extra conditions come after the key.

For outer joins, where a condition goes changes the result. This counts each customer's paid orders, or tries to:

SELECT
  c.id,
  COUNT(o.id) AS paid_orders
FROM
  customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE
  o.status = 'paid'
GROUP BY
  c.id;

Customers with no orders get NULL for o.status, NULL = 'paid' isn't true, and WHERE drops them, along with customers whose orders are all unpaid. The LEFT JOIN has quietly become an inner join. Put a filter on the optional side of an outer join into ON, and every customer stays, with a count of 0 where there are no paid orders:

LEFT JOIN orders o
  ON o.customer_id = c.id AND o.status = 'paid'

The formatter keeps all of an ON clause's conditions on one line. With three or more, break them by hand, one per line:

JOIN order_items oi
  ON oi.order_id = o.id
  AND oi.tenant_id = o.tenant_id
  AND oi.deleted_at IS NULL

Name aliases after their tables

An alias should remind you which table it stands for: c for customers, o for orders, oi for order_items, po for a paid_orders CTE. Aliases handed out by position (a, b, d) make the reader look back at FROM for every column.

Once a query has more than one table, qualify every column. An unqualified status in a query joining orders and customers works until someone adds a status column to customers; then the query fails as ambiguous. Give clashing output columns distinct aliases with AS, such as c.name AS customer_name and r.name AS region_name. Two columns called name are legal, but code that maps rows to objects by column name will often keep only one. For table aliases, AS is optional in most dialects and not allowed in Oracle, so many teams leave it out.

Prefer CTEs to deeply nested subqueries

Nested subqueries read inside out. To follow this one you start at the innermost SELECT and work outwards:

SELECT
  x.customer_id,
  x.order_count
FROM
  (
    SELECT
      o.customer_id,
      COUNT(*) AS order_count
    FROM
      (
        SELECT
          *
        FROM
          orders
        WHERE
          status = 'paid'
      ) o
    GROUP BY
      o.customer_id
  ) x
WHERE
  x.order_count > 5;

A CTE turns the same logic into named steps you read top to bottom:

WITH paid_orders AS (
  SELECT
    *
  FROM
    orders
  WHERE
    status = 'paid'
), order_counts AS (
  SELECT
    o.customer_id,
    COUNT(*) AS order_count
  FROM
    paid_orders o
  GROUP BY
    o.customer_id
)
SELECT
  oc.customer_id,
  oc.order_count
FROM
  order_counts oc
WHERE
  oc.order_count > 5;

Named steps also make simplifications visible: this one could be a single GROUP BY with HAVING COUNT(*) > 5. Keep short, single-use subqueries inline, like the AVG comparison in the rewrite below, and promote a subquery to a CTE when it's long, used twice, or worth a name.

On performance, a CTE is neither free nor a penalty by definition. Since PostgreSQL 12, a non-recursive, side-effect-free CTE referenced once is inlined into the main query (earlier versions always materialised it), and MATERIALIZED or NOT MATERIALIZED overrides that. Other databases have their own rules, so check the plan with EXPLAIN.

EXISTS vs IN, and the NOT IN trap

Both keep an outer row when a match exists, so pick the one that reads like the question. IN fits "is this value in that set" for a single column. EXISTS fits "is there a related row", especially when the match spans several columns:

WHERE
  EXISTS (
    SELECT
      1
    FROM
      support_tickets t
    WHERE
      t.customer_id = c.id
      AND t.tenant_id = c.tenant_id
      AND t.severity = 'high'
  );

The select list inside EXISTS is ignored, and SELECT 1 is a convention that says so. Many optimizers plan IN and EXISTS subqueries the same way, so choose for readability and check the plan if speed matters.

The negated forms are not interchangeable. x NOT IN (subquery) means x <> v for every value v the subquery returns, all ANDed together. If one value is NULL, that comparison is UNKNOWN, and the whole condition can never be TRUE:

-- 3 <> 1 AND 3 <> 2 AND 3 <> NULL
-- TRUE   AND TRUE   AND UNKNOWN  =>  UNKNOWN
SELECT 3 NOT IN (1, 2, NULL);  -- PostgreSQL returns NULL, not true

WHERE keeps only rows where the condition is TRUE. So in the original query, a single refund with a NULL customer_id makes a.id NOT IN (SELECT customer_id FROM refunds) reject every customer, and the query silently returns nothing. NOT EXISTS doesn't have this problem because it only asks whether a matching row exists. If you keep NOT IN, filter customer_id IS NOT NULL inside the subquery or make sure the column is declared NOT NULL.

The query after

WITH paid_orders AS (
  SELECT
    o.id,
    o.customer_id,
    o.total
  FROM
    orders o
  WHERE
    o.status = 'paid'
)
SELECT
  c.id AS customer_id,
  c.name AS customer_name,
  po.total,
  r.name AS region_name
FROM
  customers c
JOIN paid_orders po
  ON po.customer_id = c.id
JOIN regions r
  ON r.id = c.region_id
WHERE
  po.total > (
    SELECT
      AVG(p.total)
    FROM
      paid_orders p
  )
  AND NOT EXISTS (
    SELECT
      1
    FROM
      refunds rf
    WHERE
      rf.customer_id = c.id
  );

Joins carry their own conditions, aliases match their tables, output columns have unique names, and the paid filter lives in one place. Swapping NOT IN for NOT EXISTS is a behaviour change, not a style change: if refunds.customer_id ever holds a NULL, the old query returned no rows and the new one returns the rows you meant. Say so in the review rather than folding it into a "readability" commit. Pasting both versions into the diff checker makes it easy to walk a reviewer through each change.

Keep naming consistent

Readable joins depend on predictable names. Pick one convention and hold it across the schema: snake_case, foreign keys named after the table they point to (orders.customer_id to customers.id), _at for timestamps, is_ or has_ for booleans. Then a correct ON o.customer_id = c.id looks right at a glance, and a wrong one stands out.

Avoid naming tables and columns after SQL keywords. order and user are reserved in PostgreSQL and need quoting every time, and keyword-based formatters treat words like date and key as keywords: the DevPrix formatter uppercases a column called date because it can't tell it from the type.

Next steps

Run your next query through the SQL formatter so the layout is settled, then apply these rules to what's left. For how the layout rules themselves work, read how to format complex SQL queries.