Last active
September 8, 2023 03:13
-
-
Save cmcaine/7d5e05eaf0ef49bc06a111094ba8ad10 to your computer and use it in GitHub Desktop.
Diff SQL Select statements
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Show row differences between SELECT statements `new` and `old`. | |
The first column ("diff") will contain a '+' for rows that are in new and not in old | |
and a '-' for rows that are in old and not in new. The rest of the columns are whatever | |
new and old return. | |
If the rows in new and old share ids, then you may want to add an "ORDER BY" at the end | |
of the outer SELECT to make the two versions of the rows appear next to each other. | |
The example below works on recent versions of SQLite, PostgreSQL, MySQL/MariaDB, MS SQL, and Oracle 23c. | |
If it doesn't work on your database you may just need to change "EXCEPT" to "MINUS". | |
select * from | |
( | |
select '+' as diff, t.* from (select * from new except select * from old) as t | |
union all | |
select '-' as diff, t.* from (select * from old except select * from new) as t | |
) as t | |
*/ | |
-- Complete example | |
with | |
new (id, val) as (select 1, 'a' union select 2, 'x' UNION select 3, 'y'), | |
old (id, val) as (select 1, 'a' union select 2, 'b') | |
select * from | |
( | |
select '+' as diff, t.* from (select * from new except select * from old) t | |
union all | |
select '-' as diff, t.* from (select * from old except select * from new) t | |
) t order by 2, 1 | |
/* Example returns this: | |
diff id val | |
+ 2 x | |
- 2 b | |
+ 3 y | |
Which you can read in a similar way to the posix diff tool: | |
- the row with id 2 has been modified with val changed from b to x in new | |
- the row with id 3 is in new and not in old. | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment