[SOLVED] Combining the result of two queries using SQLite

Combining the result of two queries using SQLite

Let's say I have the following tables:

Table A             Table B
| a | b | c |       | a | b | c |
|---|---|---|       |---|---|---|
| a | b | c |       | a | b | c |
| a | c | d |       | a | c | d |
| c | d | e |       | a | b | c |
| f | g | h |       | o | p | q |
| a | a | a |       | a | b | c |

and I want to find all the rows that differ from both tables.

This gives me the rows from A that are not in B:

SELECT a, b, c FROM A
EXCEPT 
SELECT a, b, c FROM B;

| a | b | c |
|---|---|---|
| a | a | a |
| c | d | e |
| f | g | h |

and this gives me the rows from B that are not in A:

SELECT a, b, c FROM B
EXCEPT 
SELECT a, b, c FROM A;

| a | b | c |
|---|---|---|
| o | p | q |

So to combine the two I tried using

SELECT a, b, c FROM A
EXCEPT 
SELECT a, b, c FROM B
UNION
SELECT a, b, c FROM B
EXCEPT 
SELECT a, b, c FROM A;

but it still only gives me the rows from B.

How do I get rows from both tables, like this?

| a | b | c |
|---|---|---|
| a | a | a |
| c | d | e |
| f | g | h |
| o | p | q |

Solution

Try enclosing the individual except in parentheses and then do the union:

select * from (
    select a, b, c from a
    except 
    select a, b, c from b
)
union
select * from (
    select a, b, c from b
    except 
    select a, b, c from a
);