CODEWITHSUNDEEP
sqlsql-serversql-server-2005select
Dibstar
Dibstar

Reputation: 2364

What's the best way to select data only appearing in one of two tables?

If I have two tables such as this:

CREATE TABLE #table1 (id INT, name VARCHAR(10))
INSERT INTO #table1 VALUES (1,'John')
INSERT INTO #table1 VALUES (2,'Alan')
INSERT INTO #table1 VALUES (3,'Dave')
INSERT INTO #table1 VALUES (4,'Fred')
CREATE TABLE #table2 (id INT, name VARCHAR(10))
INSERT INTO #table2 VALUES (1,'John')
INSERT INTO #table2 VALUES (3,'Dave')
INSERT INTO #table2 VALUES (5,'Steve')

And I want to see all rows which only appear in one of the tables, what would be the best way to go about this?

All I can think of is to either do:

SELECT * from #table1 except SELECT * FROM #table2
UNION
SELECT * from #table2 except SELECT * FROM #table1

Or something along the lines of:

SELECT id,MAX(name) as name  FROM
(
SELECT *,1 as count from #table1 UNION ALL
SELECT *,1 as count from #table2
) data 
group by id
HAVING SUM(count) =1

Which would return Alan,Fred and Steve in this case.

But these feel really clunky - is there a more efficient way of approaching this?

Upvotes: 5

Views: 225

Answers (3)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

select id, name
from
 (select *, count(*) over(partition by checksum(*)) as cc
  from (select *
        from #table1
        union all
        select *
        from #table2
       ) as T
 ) as T
where cc = 1

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

select coalesce(t1.id, t2.id)     id,
       coalesce(t1.name, t2.name) name
from   #table1 t1
       full outer join #table2 t2
         on t1.id = t2.id
where  t1.id is null
        or t2.id is null

The full outer join guarantees records from both sides of the join. Whatever record that does not have in both sides (the ones you are looking for) will have NULL in one side or in other. That's why we filter for NULL.

The COALESCE is there to guarantee that the non NULL value will be displayed.

Finally, it's worth highlighting that repetitions are detected by ID. If you want it also to be by name, you should add name to the JOIN. If you only want to be by name, join by name only. This solution (using JOIN) gives you that flexibility.

BTW, since you provided the CREATE and INSERT code, I actually ran them and the code above is a fully working code.

Upvotes: 6

Oded
Oded

Reputation: 499062

You can use EXCEPT and INTERSECT:

-- All rows
SELECT * FROM #table1 
UNION
SELECT * FROM #table2
EXCEPT -- except
(
  -- those in both tables
  SELECT * FROM #table1 
  INTERSECT
  SELECT * FROM #table2
)

Not sure if this is any better than your EXCEPT and UNION example...

Upvotes: 2

Related Questions