CODEWITHSUNDEEP
sqlsql-server
Dassina
Dassina

Reputation: 162

Issues with SQL Select utilizing Except and UNION All

Select *
From (
Select a
    Except
Select b
) x

UNION ALL

Select *
From (
Select b
    Except
Select a
) y

This sql statement returns an extremely wrong amount of data. If Select a returns a million, how does this entire statement return 100,000? In this instance, Select b contains mutually exclusive data, so there should be no elimination due to the except.

Upvotes: 0

Views: 141

Answers (2)

FrankPl
FrankPl

Reputation: 13315

As already stated in the comment, EXCEPT does an implicit DISTINCT, according to this and the ALL in your UNION ALL cannot re-create the duplicates. Hence you cannot use your approach if you want to keep duplicates.

As you want to get the data that is contained in exactly one of the tables a and b, but not in both, a more efficient way to achieve that would be the following (I am just assuming the tables have columns id and c where id is the primary key, as you did not state any column names):

SELECT CASE WHEN a.id IS NULL THEN 'from b' ELSE 'from a' END as source_table
      ,coalesce(a.id, b.id) as id
      ,coalesce(a.c, b.c) as c
  FROM a
       FULL OUTER JOIN b ON a.id = b.id AND a.c = b.c -- use all columns of both tables here!
 WHERE a.id IS NULL OR b.id IS NULL

This makes use of a FULL OUTER JOIN, excluding the matching records via the WHERE conditions, as the primary key cannot be null except if it comes from the OUTER side. If your tables do not have primary keys - which is bad practice anyway - you would have to check across all columns for NULL, not just the one primary key column.

And if you have records completely consisting of NULLs, this method would not work. Then you could use an approach similar to your original one, just using 

SELECT ...
 FROM a
WHERE NOT EXISTS (SELECT 1 FROM b WHERE <join by all columns>)

UNION ALL

SELECT ...
 FROM b
WHERE NOT EXISTS (SELECT 1 FROM a WHERE <join by all columns>)

Upvotes: 1

Ben
Ben

Reputation: 784

If you're trying to get any data that is in one table and not in the other regardless of which table, I would try something like the following:

select id, 'table a data not in b' from a where id not in (select id from b)
union
select id, 'table b data not in a' from b where id not in (select id from a)

Upvotes: 0

Related Questions