CODEWITHSUNDEEP
sql-server
durga siva kishore mopuru
durga siva kishore mopuru

Reputation: 1347

Query to get no of non matching rows count for two tables using Sql Server?

I have Two Tables.

Table1 :-

Col1

AAA

BBB

CCC

Table2 :-

Col1

AAA

BBB

ZZZ

I want to get output :- 2 in above two tables contains 2 matching rows and two non maching rows. How to write Sql query to get count for non matching rows.

Upvotes: 0

Views: 1310

Answers (1)

Phylyp
Phylyp

Reputation: 1689

To get the number of non-matching rows, one simple way of doing it would be as shown below. We get the number of rows in Table1 that are not present in Table2, we get the number of rows in Table2 not in Table1, and we add both. UNION ALL combines the results of both queries (we use this instead of UNION because UNION discards duplicates). 

SELECT SUM(NonMatchingRows) AS TotalNonMatchingRows 
FROM ( 
  -- Rows from Table1 that don't have a match in Table2 
  SELECT COUNT(*) AS NonMatchingRows 
  FROM Table1 
  WHERE Col1 NOT IN ( SELECT DISTINCT Col1 FROM Table2 ) 
  UNION ALL 
  -- Rows from Table2 that don't have a match in Table1 
  SELECT COUNT(*) AS NonMatchingRows 
  FROM Table2 
  WHERE Col1 NOT IN ( SELECT DISTINCT Col1 FROM Table1 ) 
) AS X

To get the number of matching rows, you'd run: 

SELECT COUNT(*) AS MatchingRows 
FROM Table1 
INNER JOIN Table2 
  ON Table1.Col1 = Table2.Col1

This inner join returns only the rows that have the same values for Col1 in both tables. Note that in case one single row in Table 1 matches 2 rows in Table 2, this query will return 2.

Upvotes: 1

Related Questions