Diff SQL Select statements

diff-example.sql

/*
	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.
*/