SQL how to simulate an xor?

Question

I'm wondering if anybody can help me solve this question I got at a job interview. Let's say I have two tables like:

table1                 table2
------------           -------------
id | name              id | name
------------           -------------
 1 | alpha              1 | alpha
 3 | charlie            3 | charlie
 4 | delta              5 | echo
 8 | hotel              7 | golf
 9 | india

The question was to write a SQL query that would return all the rows that are in either table1 or table2 but not both, i.e.:

result
------------
id | name
------------
 4 | delta
 5 | echo
 7 | golf
 8 | hotel
 9 | india

I thought I could do something like a full outer join:

SELECT table1.*, table2.* 
FROM table1 FULL OUTER JOIN table2 
ON table1.id=table2.id 
WHERE table1.id IS NULL or table2.id IS NULL

but that gives me a syntax error on SQL Fiddle (I don't think it supports the FULL OUTER JOIN syntax). Other than that, I can't even figure out a way to just concatenate the rows of the two tables, let alone filtering out rows that appear in both. Can somebody enlighten me and tell me how to do this? Thanks.

Answer 1

Well, you could use UNION instead of OUTER JOIN.

SELECT * FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
UNION
SELECT * FROM table1 t1
RIGHT JOIN table2 t2 ON t1.id = t2.id

Here's a little trick I know: not equals is the same as XOR, so you could have your WHERE clause something like this:

WHERE ( table1.id IS NULL ) != ( table2.id IS NULL )

Answer 2

select id,name--,COUNT(*)
from(
select id,name from table1 
union all 
select id,name from table2
) x
group by id,name
having COUNT(*)=1