SQL not equal query not returning rows as expected

Question

I have a table in a database that looks as follows (parent_id is a fk to the same table):

+----+-----------+---------+
| id | parent_id | enabled |
+----+-----------+---------+
|  1 | null      |       1 |
|  2 | 1         |       1 |
|  3 | null      |       1 |
+----+-----------+---------+

The query:

SELECT * FROM category WHERE parent_id = 1

returns rows successfully as I would expect, however

SELECT * FROM category WHERE parent_id != 1

returns nothing.

You should read this and then you'll have a better understanding on handling nulls. — benscabbia
– benscabbia, Commented Feb 3, 2017 at 10:43

Joe Taras · Accepted Answer · 2017-02-03 10:42:49Z

3

The correct query about NULL value is the following:

SELECT * FROM category
WHERE parent_id IS NULL OR parent_id != 1

If you try to check is a NULL field is != by a value, it return always false (unknown for precision)

answered Feb 3, 2017 at 10:42

Joe Taras

15.4k7 gold badges45 silver badges60 bronze badges

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

2 Comments

Peppermintology Over a year ago

For goodness sake, I totally forgot you cannot use equality on null. I knew it was something daft I had overlooked. #facepalm

Joe Taras Over a year ago

@Unflux: It happens ;) Have a nice day

Collectives™ on Stack Overflow

SQL not equal query not returning rows as expected

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related