How to retrieve only those rows of a table (db1) which are not in another table (db2)

Question

I have a table t1 in db1, and another table t2 in db2. I have the same columns in both tables.

How do I retrieve only those rows which are not in the other table?

select id_num 
from [db1].[dbo].[Tbl1]

except 

select id_num
from [db2].[dbo].[Tb01]

Possible duplicate of https://stackoverflow.com/questions/4076098/how-to-select-rows-with-no-matching-entry-in-another-table — Harshit kyal, Dec 26 '17 at 05:37
You could consider creating database like between these two database and then use the approach mentioned in the reference pasted by "Harshit kyal". — pratik garg, Dec 26 '17 at 05:42
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. — , Dec 26 '17 at 09:11
for just id_num it would be... select ID_NUM from a exception join b on a.id_num = b.idnum ... for all columns I have answer below. Maybe its a duplicate question. — danny117, Dec 26 '17 at 17:02

jun drie · Answer 1 · 2017-12-26T06:00:03.200

0

You can use LEFT JOIN or WHERE NOT IN functions.

Using WHERE NOT IN:

select dbase1.id_num from [db1].[dbo].[Tbl1] as dbase1 where dbase1.id_num not in (select dbase2.id_num from [db2].[dbo].[Tb01] as dbase2)

Using LEFT JOIN (recommended as this is much faster)

SELECT dbase1.id_num FROM [db1].[dbo].[Tbl1] as dbase1 LEFT JOIN [db2].[dbo].[Tb01] as dbase2 ON dbase2.id_num COLLATE Latin1_General_CI_A = dbase1.id_num COLLATE Latin1_General_CI_A WHERE dbase2.id_num IS NULL

edited Dec 26 '17 at 06:00

answered Dec 26 '17 at 05:44

jun drie

860
7
14

getting the error :Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. – Md Umar Dec 26 '17 at 05:54
That error means that you're 2 databases are using different colaltions. I'll update my answer. – jun drie Dec 26 '17 at 05:58
Try to use again the LEFT JOIN function – jun drie Dec 26 '17 at 06:00

score 0 · Answer 2 · answered Dec 26 '17 at 17:01

Compare tables with DB2 other databases may have a select a - b statement or similar. Because at the time my database also didn't have a-b I use the following. Wrap the statement in a create table statement to dig into the results. No rows and the tables are identical. I've added in a column BEFORE|AFTER which makes the results easy to read.

SELECT 'AFTER', A.* FROM      
(SELECT * FROM &AFTER         
EXCEPT                        
 SELECT * FROM &BEFORE) AS A  
UNION                         
SELECT 'BEFORE', B.* FROM     
(SELECT * FROM &BEFORE        
EXCEPT                        
 SELECT * FROM &AFTER) AS B

How to retrieve only those rows of a table (db1) which are not in another table (db2)

2 Answers2

Linked