常见的 SQL 可读性错误

Twelve habits that make SQL harder to read, review and trust, each with a "before" and a clearer rewrite. Some are pure layout and a formatter fixes them in one click; others need a human decision, and a few of them also change what the query returns or how fast it runs.

Why readability is worth the effort

A query is written once and read many times: in code review, during an incident, when someone adds a filter two years later. Most bugs that reach production in SQL are not syntax errors, they are queries that do something slightly different from what the author meant, and the reviewer did not notice because the query was hard to read. The mistakes below are the ones that show up most often in real review queues. Mistakes 1 to 9 are about structure and layout; 10 to 12 are readability problems that also hide a correctness or performance trap.

For the layout conventions referred to here, see the SQL style guide. All "after" blocks marked as formatter output were produced by SQL Format with the default options.

1. Implicit comma joins

Listing tables in FROM separated by commas and joining them in WHERE mixes join conditions with filters. With three or more tables it becomes hard to see which condition joins what, and if one join condition is forgotten you get a silent Cartesian product instead of an error.

SELECT c.name, o.id, o.total
FROM customers c, orders o, shipments s
WHERE o.customer_id = c.id
  AND s.order_id = o.id
  AND s.shipped_at IS NULL
  AND o.status = 'paid'

Use explicit JOIN … ON. Each join condition sits next to the table it brings in, and WHERE holds only the filters. The formatter indents each JOIN under FROM and its ON one level further:

SELECT c.name,
    o.id,
    o.total
FROM customers c
    JOIN orders o
        ON  o.customer_id = c.id
    JOIN shipments s
        ON  s.order_id = o.id
WHERE s.shipped_at IS NULL
AND o.status = 'paid'

2. SELECT * in application and view code

SELECT * is fine at an interactive prompt. In code it hides which columns the caller depends on, transfers columns nobody reads, and changes behaviour when someone adds or reorders a column: a view, an INSERT … SELECT or positional result mapping can break or, worse, keep working with the wrong data.

SELECT *
FROM orders o
WHERE o.customer_id = 42

Name the columns. The formatter keeps the first one on the SELECT line and puts each of the others on its own line, so a reviewer sees an added or removed column as a one-line diff:

SELECT o.id,
    o.created_at,
    o.status,
    o.total
FROM orders o
WHERE o.customer_id = 42

3. Aliases that mean nothing

Aliases such as a, b, c or t1, t2 force the reader to scroll back to FROM every time a column is mentioned. In the example below, c.amount reads like a customer column but comes from payments.

SELECT a.name, b.number, c.amount
FROM customers a
JOIN invoices b ON b.customer_id = a.id
JOIN payments c ON c.invoice_id = b.id
WHERE c.received_at >= DATE '2026-01-01'

Pick aliases that echo the table name. In a short query an initial (o for orders) is enough; when initials collide or the query is long, use a short word:

SELECT cust.name,
    inv.number,
    pay.amount
FROM customers cust
    JOIN invoices inv
        ON  inv.customer_id = cust.id
    JOIN payments pay
        ON  pay.invoice_id = inv.id
WHERE pay.received_at >= DATE '2026-01-01'

4. Unqualified columns in joins

When a query joins two tables, an unqualified status or name forces the reader to know both schemas by heart. It is also fragile: if someone later adds a status column to the second table, the query fails with an "ambiguous column" error, or a correlated subquery may silently bind to the outer table.

SELECT id, created_at, name, email
FROM orders o
JOIN customers c ON c.id = customer_id
WHERE status = 'open'
  AND country = 'BR'

Qualify every column with its alias as soon as there is more than one table:

SELECT o.id,
    o.created_at,
    c.name,
    c.email
FROM orders o
    JOIN customers c
        ON  c.id = o.customer_id
WHERE o.status = 'open'
AND c.country = 'BR'

5. Mixing AND and OR without parentheses

AND binds more tightly than OR. The condition below is evaluated as (country = 'BR' AND status = 'active') OR (status = 'trial' AND deleted_at IS NULL), so it returns trial customers from every country. That is almost never what the author meant, and the flat layout hides it.

SELECT id, name, status
FROM customers
WHERE country = 'BR' AND status = 'active' OR status = 'trial' AND deleted_at IS NULL

Add parentheses whenever AND and OR appear in the same condition, even when precedence happens to give the right answer. The formatter puts each top-level condition on its own line and indents the parenthesised group, so the structure is visible at a glance:

SELECT id,
    name,
    status
FROM customers
WHERE country = 'BR'
AND (
    status = 'active'
    OR status = 'trial'
)
AND deleted_at IS NULL

6. Deeply nested subqueries instead of CTEs

Derived tables nested two or three levels deep have to be read from the inside out, and their names (p, t) appear only after the code that defines them.

SELECT c.name, t.revenue
FROM (SELECT customer_id, revenue
      FROM (SELECT customer_id, SUM(total) AS revenue
            FROM orders
            WHERE status = 'paid'
            GROUP BY customer_id) p
      WHERE revenue > 10000) t
JOIN customers c ON c.id = t.customer_id
ORDER BY t.revenue DESC

A common table expression (WITH) lets you name each step and read the query top to bottom. Every database covered in our dialect comparison supports CTEs in current versions. The formatter lays out each CTE body like any other subquery, indented inside its parentheses:

WITH paid_orders AS (
    SELECT customer_id,
        SUM(total) AS revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
),
top_customers AS (
    SELECT customer_id,
        revenue
    FROM paid_orders
    WHERE revenue > 10000
)
SELECT c.name,
    t.revenue
FROM top_customers t
    JOIN customers c
        ON  c.id = t.customer_id
ORDER BY t.revenue DESC

More techniques for large queries are in formatting complex queries.

7. Magic numbers without explanation

A bare status_id = 4 or - 90 tells the reader nothing. Someone has to look up what status 4 is, and nobody knows whether 90 is a business rule or a guess.

SELECT id, name
FROM employees
WHERE status_id = 4
  AND hired_at < CURRENT_DATE - 90

Join to the lookup table and filter by a readable code, and put a comment next to any constant that encodes a rule. Comments are preserved by the formatter, in place:

SELECT e.id,
    e.name
FROM employees e
    JOIN employee_status s
        ON  s.id = e.status_id
WHERE s.code = 'active'
-- probation period is 90 days (HR policy)
AND e.hired_at < CURRENT_DATE - INTERVAL '90 days'

8. Inconsistent keyword case

SELECT in one line, from in the next and order BY after that is not wrong, but it makes keywords and identifiers harder to tell apart and turns every edit into noise in the diff. Pick one convention for the team and let a tool apply it. With the keyword case option set to upper, the formatter rewrites the keywords and leaves identifiers such as Name and Customers as they were typed:

SELECT id, Name, email
from Customers
Where status = 'active'
order BY created_at DESC

SELECT id,
    Name,
    email
FROM Customers
WHERE status = 'active'
ORDER BY created_at DESC

9. The whole query on one line

Queries copied from logs, ORMs or chat often arrive as a single line of several hundred characters. Reviewing them means scrolling sideways, and a diff on that line shows the entire query as changed. This is the case where a formatter helps most:

select p.sku, p.name, sum(i.quantity) as units, sum(i.quantity * i.unit_price) as revenue from order_items i join products p on p.id = i.product_id join orders o on o.id = i.order_id where o.status = 'paid' and o.created_at >= date '2026-01-01' group by p.sku, p.name having sum(i.quantity) > 100 order by revenue desc

SELECT p.sku,
    p.name,
    SUM(i.quantity) AS units,
    SUM(i.quantity * i.unit_price) AS revenue
FROM order_items i
    JOIN products p
        ON  p.id = i.product_id
    JOIN orders o
        ON  o.id = i.order_id
WHERE o.status = 'paid'
AND o.created_at >= DATE '2026-01-01'
GROUP BY p.sku,
    p.name
HAVING SUM(i.quantity) > 100
ORDER BY revenue DESC

If some lines are still too long after the layout pass, the maximum columns option breaks them at a space.

10. ORDER BY and GROUP BY by position

ORDER BY 1, 3 is quick to type but forces the reader to count select items, and it silently changes meaning when someone inserts a column at the start of the select list. Positional GROUP BY is not supported everywhere, and the computed column below has no name at all.

SELECT region, product_line, SUM(amount)
FROM sales
GROUP BY 1, 2
ORDER BY 1, 3 DESC

Group by the expressions and order by column names or aliases:

SELECT region,
    product_line,
    SUM(amount) AS revenue
FROM sales
GROUP BY region,
    product_line
ORDER BY region,
    revenue DESC

11. NOT IN with a subquery that can return NULL

This one looks harmless and is a correctness bug. x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL, and x <> NULL is never true, it is unknown. So if the subquery returns a single NULL, the whole predicate is never true and the query returns no rows.

-- returns no rows at all if any blocked_emails.email is NULL
SELECT c.id, c.name
FROM customers c
WHERE c.email NOT IN (SELECT email FROM blocked_emails)

Use NOT EXISTS, which compares row by row and is not affected by NULL values in the other table. It also states the intent, "customers with no matching blocked e-mail", more directly. The formatter keeps NOT EXISTS ( on the WHERE line and indents the correlated subquery below it:

SELECT c.id,
    c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_emails b
    WHERE b.email = c.email
)

An anti-join with LEFT JOIN … IS NULL gives the same result and is a matter of team preference; test the column that cannot be NULL on a matched row, usually the join key:

SELECT c.id,
    c.name
FROM customers c
    LEFT JOIN blocked_emails b
        ON  b.email = c.email
WHERE b.email IS NULL

12. Functions on indexed columns in WHERE

Wrapping a column in a function, as in EXTRACT(YEAR FROM created_at) = 2026, reads naturally but usually prevents the database from using a plain index on that column, because the index stores created_at, not the year. The predicate is said to be non-sargable, and the query falls back to scanning the table. It is also harder to read than it looks: two separate conditions describe what is really one date range.

-- an index on created_at cannot be used for this range
SELECT id, total
FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2026
  AND EXTRACT(MONTH FROM created_at) = 3

Write the range on the bare column, with an inclusive start and an exclusive end. This works for any column type, including timestamps with a time part:

SELECT id,
    total
FROM orders
WHERE created_at >= DATE '2026-03-01'
AND created_at < DATE '2026-04-01'

An expression index can make the function form fast again, but the range is simpler and does not depend on an index someone may drop.

What a formatter fixes and what it does not

A formatter fixes layout: mistakes 8 and 9 entirely, and the visual side of the others, such as one column per line and one condition per line. It does not change what your query means, so it will not add parentheses, replace NOT IN or rename aliases. Formatting first still helps with those, because a well laid out query makes the remaining problems easier to spot. To keep that consistent across a team, see SQL formatting in code review and version control.

Frequently asked questions