CODEWITHSUNDEEP
sqlsql-server
Johann
Johann

Reputation: 69

How to compare two different tables find out the rest of records

I have two different tables, called tableA and tableB, I want to compare these two tables. The structures as following:

tableA use following query has 511 records

SELECT count(*)
  FROM tableA
  WHERE [ID_NAME] = 'a06'

tableB use following query has 507 records

SELECT count(*)
  FROM tableB
  where [FILENAME] like 'a06%' and [TIME] = '201705'

I wanna to compare these two tables use the common column PNO, I use the following query can find these two tables intersection(507 records):

SELECT count(*)
FROM tableA
WHERE PNO IN (SELECT PNO
                 FROM tableB
                 WHERE [FILENAME] like 'a06%' and [TIME] = '201705')

Now, what should I do ? I think use left join can find out the rest of 4 records, but I tried many times it still didn't work, please help me.

Upvotes: 0

Views: 123

Answers (3)

Ian-Fogelman
Ian-Fogelman

Reputation: 1605

Assuming you are on SQL Server 2008 +

SELECT PNO FROM TABLE A 
INTO #TEMP_A
EXCEPT
SELECT PNO FROM TABLE B

Will return the PNO that are not in both tables

SELECT  *
FROM    tableA
WHERE   PNO (Select PNO from #TEMP_A)

Will return all rows in table A with the missing PNO.

Upvotes: 0

Chetan Kulkarni
Chetan Kulkarni

Reputation: 404

Basically You want to do A-B 

   SELECT *
    FROM tableA t1
    WHERE t1.PNO Not IN (SELECT PNO
                     FROM tableB
                     WHERE [FILENAME] like 'a06%' and [TIME] = '201705')

OR

   SELECT *
    FROM tableA t1
    WHERE t1.PNO EXCEPT
      (SELECT *
       FROM tableB
             WHERE [FILENAME] like 'a06%' and [TIME] = '201705')

Upvotes: 0

Stefano Zanini
Stefano Zanini

Reputation: 5926

You can do that in many ways, and using left join is one of them:

SELECT  ta.*
FROM    tableA ta
LEFT JOIN
        tableB tb
ON      ta.[PNO] = tb.[PNO] and
        ta.[ID_NAME] = 'a06' and
        tb.[FILENAME] like 'a06%' and tb.[TIME] = '201705'
WHERE   tb.[PNO] is null

Note that the conditions that were previously in your WHERE clauses are now moved in the join condition; that's because the LEFT JOIN will assign NULL values to the rows that aren't matched, but if you filter on some of the fields of the right table this feature gets lost.

Edit

Another option is following the same path you were using to find the intersection, just adding the condition on tableA and changing IN to NOT IN:

SELECT  *
FROM    tableA
WHERE   PNO NOT IN (
            SELECT  PNO
            FROM    tableB
            WHERE   [FILENAME] like 'a06%' and [TIME] = '201705'
        ) and
        [ID_NAME] = 'a06'

Upvotes: 1

Related Questions