Select all data from one table that does not exists in another table given 3 key columns

Question

I would like to identify records that are in table #1 but not in table #2 in a situation where there are three composite keys to determine if a record is unique. The below code is as close as I get but the where clause needs work.

In this test case the results includes:

(2, 3, 2, null)
(4, 4, 1, null)
(7, 2, 2, null)
(8, 1, 2, null)

CREATE TABLE OldReports (
   REPORT_ID int,
   USER_ID int,
   CLIENT_ID int,
   MY_DATA varchar(100)
)

INSERT INTO OldReports
   (REPORT_ID, USER_ID, CLIENT_ID, MY_DATA)
VALUES
   (1, 1, 2, null),
   (6, 3, 3, null),
   (6, 4, 4, null),
   (5, 1, 2, null),
   (5, 1, 3, null),
   (7, 2, 1, null),
   (8, 1, 1, null)

CREATE TABLE NewReports (
   REPORT_ID int,
   USER_ID int,
   CLIENT_ID int,
   MY_DATA varchar(100)
)

INSERT INTO NewReports
   (REPORT_ID, USER_ID, CLIENT_ID, MY_DATA)
VALUES
   (1, 1, 2, null),
   (2, 3, 2, null),
   (4, 4, 1, null),
   (5, 1, 2, null),
   (5, 1, 3, null),
   (7, 2, 2, null),
   (8, 1, 2, null)

DROP TABLE IF EXISTS #ReportDifferences

SELECT DISTINCT
    REPORT_ID,
    USER_ID,
    CLIENT_ID
INTO #ReportDifferences
FROM NewReports n
WHERE (IF NOT EXISTS (
        SELECT * 
        FROM OldReports o 
        WHERE 
            n.REPORT_ID = o.REPORT_ID and
            n.USER_ID = o.USER_ID and
            n.CLIENT_ID = o.CLIENT_ID)
)

-- tell me what lives in NewReports but not in OldReports
SELECT * FROM #ReportDifferences

I attempted this solution (How to select all records from one table that do not exist in another table?) but on a large data set, the results are not mutually exclusive. Something like:

LEFT JOIN OldReports o ON u.CLIENT_ID = o.CLIENT_ID AND u.REPORT_ID = o.REPORT_ID AND u.USER_ID = o.USER_ID
WHERE
    u.CLIENT_ID is null
    AND u.REPORT_ID is null
    AND u.USER_ID is null

Your second query is looking for all the keys to be null? Which doesn't sound like what you want? — Dale K, Aug 17 '23 at 22:49

Dale K · Accepted Answer · 2023-08-17T21:59:42.270

I suggest using the EXCEPT operator

select *
from NewReports
except
select *
from OldReports;

Or if by data you mean multiple columns which aren't actually null, then you can join on the result of EXCEPT e.g.

select nr.*
from NewReports nr
join (
    select REPORT_ID, USER_ID, CLIENT_ID
    from NewReports
    except
    select REPORT_ID, USER_ID, CLIENT_ID
    from OldReports
) er on er.REPORT_ID = nr.REPORT_ID
    and er.USER_ID = nr.USER_ID
    and er.CLIENT_ID = nr.CLIENT_ID;

Returns

REPORT_ID	USER_ID	CLIENT_ID	MY_DATA
2	3	2	null
4	4	1	null
7	2	2	null
8	1	2	null

https://dbfiddle.uk/tyk4Rlbl

Select all data from one table that does not exists in another table given 3 key columns

1 Answers1