CODEWITHSUNDEEP
sqldatabasejoin
William
William

Reputation: 8067

Check two tables and find values not in both using SQL

I have two tables which I am returning a reference code from. I need to compare both these lists to find references that exists only in tableA or tableB, NOT in both.

As an example, if table A and table B had the following data

TABLE A References
01
02
04

TABLE B References
01
22

I would expect to return a table with the following

TABLE C References
02
04
22

The SQL for this has me stumped. Any suggestions?

Upvotes: 1

Views: 6861

Answers (7)

Tenenberg Tal
Tenenberg Tal

Reputation: 1

Explanation:

SELECT ISNULL(TABLE_A.Col1,TABLE_B.Col1) 
FROM TABLE_A
LEFT JOIN TABLE_B ON TABLE_A.Col1 = TABLE_B.Col1 
WHERE TABLE_A.Col1<> TABLE_B.Col1

The Join Part Of The Query Will Give Us The Table like this

01 Null
02 02
null 03

The Where part will remove Duplicated values

01 Null
null 03

The IsNull part will select the value that is not null from the table

01
03

Upvotes: -1

TechDo
TechDo

Reputation: 18659

Please check:

select a.*
FROM
    tblA a LEFT JOIN tblB b on a.ColRef=b.ColRef
where b.ColRef IS NULL
UNION ALL
select b.*
FROM
    tblB b LEFT JOIN tblA a on b.ColRef=a.ColRef
where a.ColRef IS NULL

OR

select * from(
    select * from tblA
    union
    select * from tblB
)x where x.ColRef NOT IN (select a.ColRef From tblA a JOIN tblB b on a.ColRef=b.ColRef)

Upvotes: 0

wildplasser
wildplasser

Reputation: 44250

Self anti-join on a CTE:

WITH two AS (
        SELECT val AS val, 'A' AS flag FROM lutser_a
        UNION ALL
        SELECT val AS val, 'B' AS flag FROM lutser_b
        )
SELECT *
FROM two t
WHERE NOT EXISTS (
        SELECT * FROM two nx
        WHERE nx.val = t.val
        AND nx.flag <> t.flag
        );

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107816

SQL Server:

(  select 'A' as source, reference
   from tableA
   EXCEPT
   select 'A' as source, reference
   from tableB)
UNION ALL
(  select 'B' as source, reference
   from tableB
   EXCEPT
   select 'B' as source, reference
   from tableA)


==================
source | reference
==================
 A       02
 A       04 
 B       22

Find everything in A except those that are in B, and add also (UNION ALL) what is in B but not (except) in A. This identifies where each reference is from, but you can of course drop the "Source" column from each of the 4 component queries.

Upvotes: 1

Maximus
Maximus

Reputation: 812

try this,

  SELECT COL1 FROM
    (
      SELECT Col1 FROM TABLE_A
      UNION ALL
      SELECT COL1 FROM TABLE_B
    ) X
     GROUP BY COL1
     HAVING COUNT(*) =1

This query will also eliminate if any value comes twice in the same table.

Upvotes: 5

mojtaba
mojtaba

Reputation: 339

Select Distinct ( isnull(A.References,B.Referecces))   
from A full join B on A.References<>B.References  
where ( not exists ( Select * from B as B1 where B1.References=A.References )  and A.References is not null )  or  
( not exists ( Select * from A as a1  where a1.References=B.References )  and B.References is not null )

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

select
    COALESCE(a.Value,b.Value)
FROM
    a
        full outer join
    b
        on
           a.Value = b.Value
WHERE
    a.Value is null or
    b.Value is null

Should do the trick. The FULL OUTER JOIN attempts to match up all rows from the two tables. The WHERE clause then removes those rows where a match was found, leaving (as a result) only those rows where a row only existed in a or b.

The COALESCE() is then used to give the result as a single column, as per your expected output. If you preferred, you could remove the COALESCE and have SELECT a.Value,b.Value which will have NULLs but will make it immediately obvious which table contains the value.

Upvotes: 3

Related Questions