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

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?

Answer

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);