CODEWITHSUNDEEP
sql
jay
jay

Reputation: 1524

SQL how to simulate an xor?

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.

Upvotes: 9

Views: 29126

Answers (5)

LoztInSpace
LoztInSpace

Reputation: 5697

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

Upvotes: 7

t-clausen.dk
t-clausen.dk

Reputation: 44336

(
SELECT * FROM TABLE1
EXCEPT
SELECT * FROM TABLE2
)
UNION ALL
(
SELECT * FROM TABLE2
EXCEPT
SELECT * FROM TABLE1
)

Upvotes: 4

Jozef Benikovský
Jozef Benikovský

Reputation: 1141

This should work on most database servers

SELECT id, name 
  FROM table1
 WHERE NOT EXISTS(SELECT NULL FROM table2 WHERE table1.id = table2.id AND table1.name = table2.name)
 UNION ALL
SELECT id, name 
  FROM table2
 WHERE NOT EXISTS(SELECT NULL FROM table1 WHERE table1.id = table2.id AND table1.name = table2.name)

Upvotes: 0

redolent
redolent

Reputation: 4259

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 )

Upvotes: 16

Joe Enos
Joe Enos

Reputation: 40413

I'm sure there are lots of solutions, but the first thing that comes to mind for me is to union all the two tables, then group by name, filter with a having clause on the count.

Upvotes: 6

Related Questions