Why am I unable to select all records in one table that don't exist in another table?-CodePudding

I have two tables like:

table_1:

col1
A
B
C
D


table_2

col1
B
D
F
E

Desired output:

A
C

I want to select all records in col1 of table_1 that aren't in col1 of table_2. In actuality, table_1 has 65000 rows and table_2 has around 2000 rows. My query:

SELECT col1
FROM table_1
WHERE col1 NOT IN (SELECT col1 FROM table_2)

This returns no records. However, the reverse works as intended when I select all records in table_2 that don't exist in table_1. I have double checked that I do have records in both tables and when manually checking if values exist or not by doing:

SELECT * FROM table_2 WHERE col1 = 'C'

And this produces no records as expected. I have also done:

SELECT col1
FROM table_1
WHERE col1 IN (SELECT col1 FROM table_2)

And this produces all mutual records as intended. I have also done:

SELECT col1
FROM table_2
WHERE col1 NOT IN (SELECT col1 FROM table_1)

And this produces all records in table_2 that aren't in table_1 as intended.

Why is my query just not working for records that are in one table but not the other?

CodePudding user response：

From what you've shown you could see if a not exists criteria serves the purpose. If this doesn't work as expected then almost certainly there's something different, eg collation, that we don't know about.

select col1
from table_1 t1
where not exists (select * from table_2 t2 where t2.col1 = t1.col1);