How to Return non matching records from two tables

Hi Friends,

Last week, I was in an assignment and one of the guys asked this question: “How to Return non matching records from two tables?”

So, here goes the scenario. There are two tables, say, Table1 & Table2 and both of them have a column, say col1. And you want to return records that are unique to each table (non-matching) based on col1.

Table1

Col1

AAA

BBB

CCC

Table2

Col1

AAA

BBB

ZZZ

So, you need to write a query that returns CCC & ZZZ.

Here is the solution:

(
select col1 from table1 t1
EXCEPT
Select col1 from table2 t2
)
UNION
(
select col1 from table2 t2
EXCEPT
Select col1 from table1 t1
)

There are other techniques too to solve the above problem. Do post comments and let the readers know about your solution.

 

 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

7 Comments on “How to Return non matching records from two tables”

  1. You can try this too(i have used temporary tables just for my comfort) :

    SELECT a.*, b.*
    FROM #ABC a
    full join #XYZ b ON a.aid = b.bid
    WHERE b.bid IS NULL or a.aid IS NULL

  2. You should look at the generic SP CompareRows in this month SQL Server Magazine article. This one show also differences on both the source and the target and properly identify them, and scan source only once.

    http://www.sqlmag.com/article/sql-server/improve-sql-dynamic-coding-practices-141080

    This procedure is able to compare Tables/Query results across tables in the same databases or different databases or differents servers (provided that linked server are defined to access remote server).

    It also demonstrate great ways to generate dynamic sql code.

    You can get the code at : http://www.sqlmag.com/content/content/141080/141080.zip

  3. use madworks
    go

    create table #t1
    (
    col1 char(3)
    )

    create table #t2
    (
    col2 char(3)
    )

    insert into #t1
    select ‘AAA’
    UNION ALL
    select ‘BBB’
    UNION ALL
    select ‘CCC’

    Insert into #t2
    select ‘AAA’
    UNION ALL
    select ‘BBB’
    UNION ALL
    select ‘ZZZ’
    ;
    with cte as (

    select Col1 as Col from #t1
    Union all
    select Col2 from #t2
    )

    select col from cte
    group by col Having Count(*) =1

    Drop table #t1

    Drop table #t2

  4. I definitely prefer the solution using the FULL JOIN, as it’s portable (lucky you if you can really focus only on SQL Server, I never had such luck in 14 years of career). Besides, unless requirements are more complex than just returning the unique rows, its performance is not so bad.

  5. The solution with the CTE is compatible with all DB that support ANSI99. This includes Oracle, SQL Server, DB2 and probably Informix and PostGres even in no so recent versions. Don’t know for the others.

    Full join solution may be cumbersome to code when you have two records with the same key (both record exists) and you want to find a difference on a set of columns to understand on what they differ.

    By the way you can may this compatible to old versions by replacing

    “from cte”

    by

    from (select Col1 as Col from #t1
    Union all
    select Col2 from #t2
    ) as Cte

    This is called a derived table and this work on most of old versions of rdbms and many low end ones.

  6. –Could you not also do this?(
    select col1 from table1 t1
    UNION
    Select col1 from table2 t2
    )
    EXCEPT
    (
    select col1 from table2 t2
    INTERSECT
    Select col1 from table1 t1
    )

Leave a Reply

Your email address will not be published. Required fields are marked *