enigma6205 enigma6205 - 5 months ago 8
SQL Question

return only values from the left column that do not exists in the right column. DO not need data matching with the right column

I need to return only values from the Left column that do not exist in the Right column. Do not need the data matching with the right column.

I have two tables with the same metadata. Left table has 11137 records, Right table - 9725. (FYI, Left table has 8508 unique ID_pk; and Right table has - 7194 unique ID_pk.) I only need to return all the rows from the left table that not in the Right table.

I've tried various joints and CTE, but it returns 94857 for just join or 96463 for the left join.

With CTE
AS
(Select * From ##GlobalTempTable_PW_2
Select CTE.ID_pk, CTE.[Dweling_fk], CTE.FieldID, CTE.House
From CTE Join ##GlobalTempTable_LA_2 LA on CTE.ID_pk = LA.ID_fk
);


I've also tried using NOT EXISTS, but not getting the desired result. May be I have a logic error.

Select * From ##GlobalTempTable_PW_2 PW
Where NOT EXISTS
(
Select ID_fk From ##GlobalTempTable_PW_2 WHERE NOT EXISTS (Select PW.ID_fk From ##GlobalTempTable_PW_2 PW
Join ##GlobalTempTable_LA_2 LA on PW.ID_fk = LA.ID_fk);

Answer

This task is usually accomplished using NOT EXISTS:

Select * 
From ##GlobalTempTable_PW_2 PW
WHERE NOT EXISTS (SELECT 1
                  FROM ##GlobalTempTable_LA_2 LA 
                  WHERE PW.ID_pk = LA.ID_fk)

This query returns all the records of table ##GlobalTempTable_PW_2 that don't have a matching record in table ##GlobalTempTable_LA_2.