Sqlite EXCEPT and ALTER TABLE

Question

I have 2 tables with same structure. these 2 tables are monthly snapshots: table1=january table2=february

As i want to compare rows added and removed, i use EXCEPT twice: to get rows added:

select * from table1 
EXCEPT
select * from table2

to get rows removed:

select * from table2 
EXCEPT
select * from table1

in two cases i want to ADD COLUMN the results table with Month/Add-Remove stamp and then UNION those two resulting tables.

i don't how to make it using sqlitebrowser. Could anybody help me? thx

Answer 1

To add a column to the result of a query, just add it to the SELECT clause. To prevent it from affecting the EXCEPT, move the EXCEPT into a subquery:

SELECT *, 'added'
FROM (select * from table1
      EXCEPT
      select * from table2)
UNION ALL
SELECT *, 'removed'
FROM (select * from table2
      EXCEPT
      select * from table1);