Count distinct values in Table_A.Col1 that don't exist in Table_B.Col1

Posted on 2014-08-07
Last Modified: 2014-08-07
Good morning!  

Working in MS SQL 2008.  

Hopefully the title is self-explanatory but, I'm trying to find the count of distinct values in Table_A.col1 that don't exist in Table_B.col1.  

Question by:ttist25
    LVL 65

    Assisted Solution

    by:Jim Horn
    Give this a whirl..
    FROM Table_A a
       -- LEFT means include all rows in Table_A...
      LEFT JOIN Table_B b ON a.col1 = b.col1
      -- ... where there is no matching Table_B row
    WHERE b.Col1 IS NULL

    Open in new window

    LVL 68

    Accepted Solution

    An alternate method.  This might run faster, depending on the specifics of the data in the two tables.

    SELECT a.col1
    FROM (
        SELECT DISTINCT col1
        FROM Table_A
    ) AS a
        NOT EXISTS(
            SELECT 1
            FROM Table_B b
                b.col1 = a.col1
    --ORDER BY a.col1
    LVL 1

    Author Closing Comment

    Thanks guys - that did the trick!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now