Compare Two SQL Queries - SQL Diff Online

Why compare queries by token

A query that went through a formatter, or through a colleague's editor, rarely keeps a line of the original. Keywords move to capitals, a join is split over two lines, a comment is reworded and a semicolon appears at the end. A line diff reports every one of those lines, and the one filter that changed looks no different from them.

This page reads both queries as SQL tokens. Keywords and unquoted names are compared without regard to letter case, line breaks and spaces between tokens never count, and comments and the semicolon that ends the last statement are set aside as formatting. A string literal, a number, an operator or a name that differs is a real change. It opens on a revenue report written by hand and then formatted: of its seven changes, three are real, and one of those is a status literal that only changed case.

What it reports

Formatted, not changed

-- active users
select id, name from users
where active = 1 order by name

-- users who are active
SELECT id, name
FROM users
WHERE active = 1
ORDER BY name;

One change, labelled formatting. The keywords went to capitals, the query was split over four lines, the comment was reworded and a semicolon was added, and none of that changes what the query does.

A literal that only changed case

SELECT * FROM orders WHERE status = 'paid'

select * from orders where status = 'Paid'

One real change. The keywords changing case would be cosmetic on its own, but 'paid' and 'Paid' are different strings: PostgreSQL, Oracle and SQLite match them against different rows by default.

An operator next to a re-indent

SELECT id
FROM orders
WHERE total > 500
  AND status = 'paid'

SELECT id
FROM orders
WHERE total >= 500
AND status = 'paid'

Two changes, one of them real: > became >=, so an order of exactly 500 is now included. The line under it only lost its indent, and is labelled whitespace.

Options

Letter case: Keywords and unquoted names are compared without regard to case, so select and SELECT, or Orders and orders, count as case. String literals and quoted identifiers keep their case: 'Paid' is not 'paid', and in PostgreSQL "UserId" and "userid" are different columns.
Layout: Line breaks, indentation and the spaces between tokens never count, so a query squeezed onto one line and the same query spread over twenty compare as one. The spacing inside a string literal still counts: 'a b' and 'a b' are different values.
Comments and semicolons: -- and /* */ comments are set aside, so a comment that was only reworded is labelled formatting rather than hidden. The ; that ends the last statement is cosmetic too. A ; between two statements is not, because taking it out joins them into one.
Numbers and lists: Numbers are compared as written, so 1.0 and 1.00 stay different, as they can change a result's type or scale. IN (1,2,3) and IN (1, 2, 3) differ only in spacing, while IN (123) is one value and a real change.
Any fragment: The page applies these rules to whatever you paste, not only to whole statements, so two WHERE clauses or two column lists compare the same way a full query does.

In a terminal

```
diff <(sqlformat -r -k upper -i lower --strip-comments old.sql) <(sqlformat -r -k upper -i lower --strip-comments new.sql)
```
sqlformat re-indents both queries, upper-cases keywords, lower-cases names and drops comments, then diff compares what is left. On this page's sample that is the WHERE block and the LIMIT line.
```
diff -i -w old.sql new.sql
```
Quicker, and a trap for SQL: -i ignores letter case everywhere, so on this page's sample 'paid' becoming 'Paid', which changes the result, is missing from the output.

What the page does that these do not: Neither says which change is real: after sqlformat the diff still mixes date against DATE and the added semicolon in with the three real edits, where the page labels all seven changes and counts three as real.
What the terminal does better: Both run unattended from a script over as many files as it gives them, and sqlformat --in-place rewrites files in one style, so a repository can keep every query formatted before anyone compares it.

sqlformat comes with the Python package sqlparse (pip install sqlparse); these flags are from version 0.6.0.

Questions

Does letter case matter in SQL?

Not for keywords, and seldom for unquoted names: PostgreSQL and Oracle read Orders and orders as one table, and so does SQL Server under its default collation, though MySQL on Linux compares table names with case by default. This page labels a keyword's or a name's case change as case, dimmed but visible. A string literal is different: whether 'Paid' equals 'paid' depends on the collation, so a literal's case change is always reported as real.

Is a formatted query the same query?

When only the layout, the case of keywords and names, the comments or the final semicolon changed, yes, and every change the page finds is labelled cosmetic. It compares tokens, not results, so it cannot prove that two differently written queries return the same rows: x IN (1, 2) and x = 1 OR x = 2 are reported as different.

Which dialects does it read?

The parts they share: -- and /* */ comments, single-quoted strings with '' escapes, double-quoted, backquoted and bracketed names, and the usual operators. It does not parse any one dialect's grammar, so PostgreSQL, MySQL, SQL Server, Oracle and SQLite queries all work. A MySQL # comment is compared as ordinary text.

Can it compare two database schemas?

No. It compares two pieces of SQL text: two versions of a query, a view or a migration script. Comparing the tables and columns of two live databases needs a tool that connects to them, and this page never connects to anything.

Do my queries leave the browser?

No. Queries often carry table names, customer ids and values from production, and both stay on this device: the comparison runs in your browser. An optional AI explanation is the one exception, and it sends only the changes you tick, with secrets, email addresses and phone numbers masked, after showing you the text.