Find rows that exist in one table and not the other

CREATE TABLE final(
  FileID int,
  rate varchar(50),
  groupID varchar(50),
  destination varchar(50),
  Rate int,
  bDate date,
  eDate date,
  billID varchar(50) NULL
 );
 
 CREATE TABLE temp(
  FileID int,
  rate varchar(50),
  groupID varchar(50),
  destination varchar(50),
  Rate int,
  bDate date,
  eDate date,
  billID varchar(50) NULL
 );
 
 
 INSERT final VALUES (1,'123','1033','987',2,'2020-01-01','2020-12-31',NULL);
 INSERT final VALUES (1,'234','1052','985',2,'2020-01-01','2020-12-31',NULL);
 INSERT final VALUES (1,'999','1033','988',1,'2021-01-01','2021-12-31','0/1/1');
 INSERT final VALUES (2,'222','1033','989',2,'2022-01-01','2022-12-31',NULL);
 
 INSERT temp VALUES (1,'123','1033','987',2,'2020-06-06','2020-12-31','0/1/1');
 INSERT temp VALUES (1,'999','1033','988',1,'2021-01-01','2021-12-31','0/1/1');
 INSERT temp VALUES (2,'222','1033','989',2,'2022-01-01','2022-12-31','0/1/1');

Assuming I have these two tables, I want to do 2 queries.

Find the rows that are in final table but NOT in temp table based on their FileID Assuming we’re only looking at FileID = 1

FileID	rate	groupID	destination	Rate	bDate	eDate	billID
1	123	1033	987	2	2020-01-01	2020-12-31	NULL
1	234	1052	985	2	2020-01-01	2020-12-31	NULL

Find all rows that are in the temp table but NOT in the final table based on their FileID. Assuming we’re only looking at FileID = 1

FileID	rate	groupID	destination	Rate	bDate	eDate	billID
1	123	1033	987	2	2020-06-06	2020-12-31	0/1/1

Is there an efficient query that can do this?

Answer

Left join, then select rows with null for the other table.

Here is an example. I’ve put in [etc] instead of typing out all the field names.

SELECT final.fileId, final.rate [etc]
FROM
( 
SELECT final.fileId, final.rate [etc] temp.FileId
FROM final LEFT JOIN temp
ON final.FileId = temp.FileId, final.rate = temp.rate [etc] 
)
WHERE
temp.FileId IS NULL

Advertisement

Answer