SQL Query Explainer

Reading Complex SQL: A Structured Approach

Most developers can read simple SQL fluently. Complex SQL, 200-line queries with CTEs, window functions, multiple subqueries, three different join types, and conditional aggregates, is another story. They take real effort to understand, often more than the code that calls them. This explainer parses any query and breaks it into a structural outline so you can navigate it like a table of contents rather than reading top to bottom.

The Anatomy of a SELECT

A complete SELECT statement has these clauses, in this order:

Logical execution order (different from write order):

1. FROM + JOIN, gather rows from tables.
2. WHERE, filter rows.
3. GROUP BY, group rows.
4. HAVING, filter groups.
5. SELECT, compute output columns.
6. DISTINCT, remove duplicates.
7. ORDER BY, sort.
8. LIMIT, restrict count.

Understanding this order resolves common confusions:

• "Why can't I reference a SELECT alias in WHERE?", WHERE runs before SELECT.
• "Why must my GROUP BY columns appear in SELECT?", convention plus SQL standard rules.
• "Why HAVING vs WHERE?", WHERE is pre-aggregation, HAVING is post-aggregation.

CTEs (WITH Clauses)

A CTE names a subquery for reuse:

The explainer treats each CTE as its own SELECT, with its own breakdown.

Recursive CTEs for trees:

The recursion base case is the first SELECT; the recursive case is after UNION ALL. The explainer flags recursive CTEs separately.

JOINs

Five JOIN types:

JOIN syntax variants:

The explainer normalizes all of these to a canonical form.

Subqueries

Three kinds:

Scalar subquery, returns one value

Table subquery, returns a virtual table

Correlated subquery, references outer row

Correlated subqueries can be slow (executed per outer row). The explainer flags them.

Aggregations

Aggregate functions reduce a set to one value:

With GROUP BY, the aggregation happens per group:

Common gotchas:

• Every non-aggregate column in SELECT must be in GROUP BY (SQL standard).
• MySQL's relaxed mode allows non-grouped columns (returns arbitrary row's value, usually a bug).
• HAVING filters AFTER aggregation; WHERE filters BEFORE.

Window Functions

Aggregations WITHOUT collapsing rows:

Each row keeps its detail; the window function adds a column based on a "window" of related rows.

Common window functions:

• ROW_NUMBER(), sequential row number per partition.
• RANK() / DENSE_RANK(), ranking with/without gaps for ties.
• LAG(col) / LEAD(col), value from previous/next row.
• FIRST_VALUE / LAST_VALUE, window edges.
• SUM / AVG / COUNT OVER (PARTITION BY ...), running aggregates.

Use cases: top-N per group, running totals, comparing each row to peers.

Set Operations

Both sides must have the same column count and compatible types.

CASE Expressions

Conditional logic in queries:

Often combined with aggregates:

What the Parser Surfaces

For each query, the explainer breaks down:

• Statement type: SELECT / INSERT / UPDATE / DELETE / DDL.
• Tables: every table referenced (FROM, JOIN, subqueries, CTEs).
• Columns: every column returned (with aliases, expressions).
• Joins: each join with type and ON condition.
• Filters: WHERE conditions, broken into AND/OR clauses.
• Aggregates: GROUP BY columns, aggregate functions, HAVING.
• Sorts: ORDER BY columns and directions.
• Limits: LIMIT / OFFSET.
• Subqueries: nested SELECTs flagged separately.
• CTEs: each as its own breakdown.
• Aliases: table and column aliases mapped.

Common Pitfalls Revealed

The explainer often surfaces issues that are hard to spot reading SQL top-to-bottom:

Implicit cross join

The breakdown shows two tables, no JOIN condition, flag.

Missing JOIN condition

Filter on outer-join nullable side

Filtering a LEFT JOIN's right side in WHERE eliminates the unmatched rows, usually wrong. Move to ON: ON o.user_id = u.id AND o.status = 'paid'.

Aggregation without GROUP BY

Some DBs error; some (MySQL relaxed mode) silently pick a row's value. Always GROUP BY.

Subquery instead of JOIN

Sometimes slower than a LEFT JOIN + GROUP BY. The explainer shows it as a correlated subquery; consider rewriting.

DML and DDL

The explainer also breaks down non-SELECT statements:

INSERT

Shows target table, columns being set, row count.

UPDATE

Shows target table, columns being set, WHERE clause.

DELETE

Shows target table, WHERE clause.

CREATE TABLE / ALTER TABLE

DDL statements show schema definition / changes.

What the Parser Doesn't Do

• Optimization, it doesn't simplify or rewrite queries.
• Execution planning, use EXPLAIN for that.
• Runtime estimation, no idea how many rows or how long.
• Constraint checking, no foreign-key/uniqueness validation.
• Dialect normalization, stays in the input's dialect.

For execution analysis, pair with EXPLAIN / EXPLAIN ANALYZE in your database.

Privacy

Parsing runs entirely in your browser using node-sql-parser bundled into the page. Your queries, which reveal table schemas, foreign-key relationships, business logic encoded in WHERE clauses, and sometimes proprietary data structures, stay in the tab. Open DevTools Network during use: zero outbound requests. Important because SQL queries are some of the most information-rich artifacts in a codebase; pasting them to a third-party service would leak a lot about your data model.