mysql: select query when the column that has condition on has NULL value

Question

This is my example table:

CREATE TABLE test(
name VARCHAR(35),
age INT(3))

And some values:

insert into test values ('alex', 13);
insert into test values ('dan', 17);
insert into test (name) values ('pete');
insert into test (name) values ('david');

When I use SELECT query with the condition on column 'age':

select * from test where age!=13;

The result I got is:

+------+------+
| name | age  |
+------+------+
| dan  |   17 |
+------+------+

But I want all the record with age!=13 which includes the record with age IS NULL too:

+-------+------+
| name  | age  |
+-------+------+
| dan   |   17 |
| pete  | NULL |
| david | NULL |
+-------+------+

How can I get what I wants? Thanks for any responding.

Nico · Accepted Answer · 2013-09-11 18:43:14Z

2

SELECT * FROM test WHERE age!=13 OR age IS NULL;

Keep in mind that NULL is not really a value but more like a state. NULL = NULL is always false as NULL != NULL. Whatever you use with NULL will always be false. So when it evaluates age != 13 to NULL != 13, it is the expected behavior to not show the row.

answered Sep 11, 2013 at 18:43

Nico

7,0435 gold badges27 silver badges35 bronze badges

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

3 Comments

echo_Me Over a year ago

did you give something new or just repeat my answer ? are you a doctor in copy past things ?

Nico Over a year ago

@echo_Me You updated your answer by copypasting my answer. Are you a doctor in copy paste things too?

echo_Me Over a year ago

i didnt copy paste i changed =to is.

echo_Me · Accepted Answer · 2013-09-11 18:48:37Z

0

try this

     select * from test where age !=13  or age is NULL;

edited Sep 11, 2013 at 18:48

answered Sep 11, 2013 at 18:42

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Comments

Joshua Pinter · Accepted Answer · 2023-03-07 16:59:02Z

0

Use the `<=>` operator.

SELECT 
  * 
FROM 
  test
WHERE
  NOT( age <=> 13 );

Result:

+-------+------+
| name  | age  |
+-------+------+
| dan   |   17 |
| pete  | NULL |
| david | NULL |
+-------+------+

answered Mar 7, 2023 at 16:59

Joshua Pinter

47.9k23 gold badges261 silver badges258 bronze badges

Collectives™ on Stack Overflow

mysql: select query when the column that has condition on has NULL value

3 Answers 3

3 Comments

Comments

Use the `<=>` operator.

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Use the <=> operator.

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Use the `<=>` operator.