Records common in both tables

• curious_sqldba

  SSC-Dedicated

  Points: 36494

  More actions

  June 17, 2017 at 10:27 pm

  #402191

  
  panpk  is identity value. I am trying to how many records (count) are common between both the tables. How do i do that?
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Go

  SELECT COUNT_Big( c.panpk )
FROM dbo.c_scope c
JOIN dbo.a_scope a
     ON c.hfk = a.hfk
      AND a.panpk = c.panpk
     


• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  June 18, 2017 at 3:04 am

  #1947183

  curious_sqldba - Saturday, June 17, 2017 10:27 PM

  
  panpk  is identity value. I am trying to how many records (count) are common between both the tables. How do i do that?
  
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  Go

  SELECT COUNT_Big( c.panpk )
FROM dbo.c_scope c
JOIN dbo.a_scope a
     ON c.hfk = a.hfk
      AND a.panpk = c.panpk
     


  Quick question, why are you using TRANSACTION ISOLATION LEVEL READ UNCOMMITTED? The dirty read can easily produce incorrect results.
  😎

  Here are some examples on how you can do this
  USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_COMPARE_NUMBER') IS NOT NULL DROP TABLE dbo.TBL_COMPARE_NUMBER;
CREATE TABLE dbo.TBL_COMPARE_NUMBER
(
  N INT NOT NULL PRIMARY KEY CLUSTERED
);

  INSERT INTO dbo.TBL_COMPARE_NUMBER (N)
SELECT
  NM.N
FROM  dbo.TBL_NUMBERS NM
WHERE NM.N % 3 > 0;

  INSERT INTO dbo.TBL_COMPARE_NUMBER (N)
SELECT
  NM.N + 2000000
FROM  dbo.TBL_NUMBERS NM
WHERE NM.N % 13 = 0;

  -- #1
SELECT
  COUNT(NM.N)
FROM  dbo.TBL_COMPARE_NUMBER CN
INNER JOIN dbo.TBL_NUMBERS NM
ON    NM.N  = CN.N;

  -- #2
SELECT
  COUNT(CN.N)
FROM  dbo.TBL_COMPARE_NUMBER CN
WHERE CN.N IN (SELECT NM.N FROM dbo.TBL_NUMBERS NM );

  -- #3
SELECT
  COUNT(CN.N)
FROM  dbo.TBL_COMPARE_NUMBER CN
WHERE EXISTS (SELECT NM.N FROM dbo.TBL_NUMBERS NM
      WHERE CN.N = NM.N);

  -- #4
SELECT
  COUNT(CN.N)
FROM  dbo.TBL_COMPARE_NUMBER CN
LEFT OUTER JOIN dbo.TBL_NUMBERS NM
ON     NM.N  = CN.N
WHERE    NM.N  IS NOT NULL;

  -- #5
SELECT
  COUNT(CN.N)
FROM  dbo.TBL_COMPARE_NUMBER CN
RIGHT OUTER JOIN dbo.TBL_NUMBERS NM
ON     NM.N  = CN.N
WHERE    NM.N  IS NOT NULL;

  -- #6
SELECT
  COUNT(NM.N)
FROM  dbo.TBL_NUMBERS NM
CROSS APPLY dbo.TBL_COMPARE_NUMBER CN
WHERE    CN.N  = NM.N;

  -- #7
SELECT
  COUNT(CN.N)
FROM  dbo.TBL_COMPARE_NUMBER CN
OUTER APPLY dbo.TBL_NUMBERS NM
WHERE   CN.N   = NM.N;

  I get almost the same execution plan for all these queries so there shouldn't be any noticeable performance difference.

• mhtanner

  Mr or Mrs. 500

  Points: 552

  More actions

  June 19, 2017 at 4:30 am

  #1947223

  If the tables have multiple columns and you want to count where all columns match

  
select count(*) from (
select * from dbo.c_scope
intersect
select * from dbo.a_scope
)x