[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

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

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.  

TIA!
0
ttist25
Asked:
ttist25
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl..
SELECT COUNT(DISTINCT a.Col1)
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

0
 
Scott PletcherSenior DBACommented:
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
WHERE
    NOT EXISTS(
        SELECT 1
        FROM Table_B b
        WHERE
            b.col1 = a.col1
        )
--ORDER BY a.col1
0
 
ttist25Author Commented:
Thanks guys - that did the trick!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now