SQL: NOT IN doesn't return expected rows

Question

I have table A

A

+--------+------+
| values | data |
+--------+------+
| 11     | 4    |
+--------+------+
| 22     | 5    |
+--------+------+
| 33     | qwe  |
+--------+------+
| 44     | 7    |
+--------+------+
| 55     | zui  |
+--------+------+

and this SQL

SELECT * FROM A WHERE data NOT IN (4,5,7)

So the expected result is

33
55

because qwe and zui are NOT IN (4,5,7).

But the result is empty. No rows are returned.

How can I fix this?

Which DBMS are you using? Postgres for one would flat out refuse to run that statement because you are trying to compare apples to oranges (strings with numbers) — a_horse_with_no_name, Jul 25 '19 at 12:32
[Works fine in sqlite](https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=992e224a65cc3068e1254803f6cc67d4); Yay dynamic typing. — Shawn, Jul 25 '19 at 12:39
Seems to work in MySQL: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=a421aba7b1b3a49001e7bc58cb1df950 — a_horse_with_no_name, Jul 25 '19 at 12:53

score 3 · Accepted Answer · answered Jul 25 '19 at 12:29

3

as data is varchar column so quote them as a like string

SELECT * FROM A WHERE data NOT IN ('4','5','7')

answered Jul 25 '19 at 12:29

Zaynul Abadin Tuhin

28,879
5
20
49

SQL: NOT IN doesn't return expected rows

1 Answers1