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) — user330315
– user330315, Commented Jul 25, 2019 at 12:32

Zaynul Abadin Tuhin · Accepted Answer · 2019-07-25 12:29:15Z

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, 2019 at 12:29

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL: NOT IN doesn't return expected rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related