CODEWITHSUNDEEP
mysqljoinduplicatesouter-join
envysea
envysea

Reputation: 1031

MySQL join to find non-matches or possibly a full outer join with exclusion

I have 2 identical dead-simple tables in a MySQL database with different data. I need a single query that will return all the results that aren't a duplicate.

Here's an example:

Table 1. (column "item")

a
b
c
d

Table 2. (column "item")

a
b
e
f
x

Wanted Result

c
d
e
f
x

Upvotes: 0

Views: 137

Answers (5)

Strawberry
Strawberry

Reputation: 33945

A lot like xQbert's, but with the assumption that item is UNIQUE/PRIMARY...

SELECT a.*
  FROM
     ( SELECT item FROM table1
        UNION ALL
       SELECT item FROM table2
     ) a
 GROUP 
    BY item
HAVING COUNT(*) = 1;

Upvotes: 0

Cody
Cody

Reputation: 149

SELECT * FROM TABLE1 WHERE ITEM NOT IN (SELECT ITEM FROM TABLE2)
union
SELECT * FROM TABLE2 WHERE ITEM NOT IN (SELECT ITEM FROM TABLE1)

Or another method would be to use an inner join to grab all the duplicate data and then pull all the data that isn't in your duplicate dataset.

SELECT * into #temptable FROM TABLE1 INNER JOIN TABLE2 on table2.x = table1.x
union
SELECT * into #temptable FROM TABLE2 INNER JOIN TABLE1 on table2.x = table1.x

SELECT * FROM Table1 WHERE NOT IN (SELECT * FROM #temptable)
UNION
SELECT * FROM Table2 WHERE NOT IN (SELECT * FROM #temptable)

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

Try this - 

SELECT * FROM TABLE1 WHERE ITEM NOT IN (SELECT ITEM FROM TABLE2)
UNION
SELECT * FROM TABLE2 WHERE ITEM NOT IN (SELECT ITEM FROM TABLE1)

Upvotes: 4

xQbert
xQbert

Reputation: 35343

We could join them using a distinct an union ALL. then count and having.

the distinct is needed since we care about unique accross the sets.

SELECT item FROM (
    SELECT distinct item
    FROM tbl1
    UNION ALL
    SELECT distinct item
    FROM tbl2) B
GROUP BY item
HAVING count(*) =1

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30839

You can use NOT EXISTS, e.g.:

SELECT item
FROM table1 t1 
WHERE NOT EXISTS (
    SELECT item FROM table2 WHERE item = t1.item
);

Upvotes: 0

Related Questions