SQL query returns wrong results

Question

I have one column in my table named 'process' which is a BOOLEAN type and has a value NULL by default. When I set the values of 'process' of all rows to NULL and do the query

SELECT * FROM `tablename` WHERE `process` != 1

MySQL returns an empty result set. I also get the same result when I do

SELECT * FROM `tablename` WHERE `process` = NULL

Furthermore, when I use PHP to print out the rows like

$result = mysqli_query($conn, "SELECT * FROM log");
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
    if ($row['process'] != 1)
        echo $row['time']." ".$row['event']."\n";
}

I don't get an empty result set. Can anyone explain to me where the problem is?

Thanks.

i believe the second query should be something like `SELECT * FROM tablename WHERE process IS NULL` — Jhecht, Jul 08 '19 at 09:13
Possible duplicate of [Example of three valued logic in SQL Server](https://stackoverflow.com/questions/30539243/example-of-three-valued-logic-in-sql-server) — Serg, Jul 08 '19 at 09:21

Geee · Accepted Answer · 2019-07-08T10:00:02.437

2

When you fire the SQL:

SELECT * FROM `tablename` WHERE `process` != 1

The result set is returned empty because this will look for the records which do not have value '1'. NULL is not a value

SELECT * FROM `tablename` WHERE `process` = NULL

This sounds completely wrong, As I have informed 'NULL' is not a value. Off couse, this is the wrong method you're trying to apply.

In nutshell, NULL is not a value, therefore it cannot equal = or not equal != anything. It is not the same as zero 0 which is a value.

Check the like for better understand.

Hope this help you!

edited Jul 08 '19 at 10:00

answered Jul 08 '19 at 09:17

Geee

2,217
15
30

Why doesn't `process != 1` work? I think the only difference between the two symbols is <> is standard while != is not. – Cassie Liu Jul 08 '19 at 09:26
@CassieLiu In fact `!=` and `<>` are logically equivalent; there is no difference. – Tim Biegeleisen Jul 08 '19 at 09:28
`NULL` is an absence of a value in terms of the database field, you can not compare null value itself. Does this make sense? – Geee Jul 08 '19 at 09:31
[Here](https://gis.stackexchange.com/questions/203463/what-is-the-difference-between-null-and-is-not-null-in-qgis-filter-expression) is your answer for more difference. – Geee Jul 08 '19 at 09:33
@GhanshyamBhava I just edited the question. Could you please take a look at it again? – Cassie Liu Jul 08 '19 at 09:41

score 0 · Answer 2 · answered Jul 08 '19 at 09:16

0

To get Record which has NULL value,

you should Use isnull() instead of column=NULL

SELECT * FROM `tablename` WHERE ISNULL(`process`);

answered Jul 08 '19 at 09:16

Mohideen bin Mohammed

18,813
10
112
118

score 0 · Answer 3 · answered Jul 08 '19 at 09:16

0

you can use IS NULL

SELECT * FROM tablename WHERE process IS NULL

answered Jul 08 '19 at 09:16

Ali Faris

17,754
10
45
70

score 0 · Answer 4 · answered Jul 08 '19 at 09:17

If you are defining logical false as the boolean column being not equal to 1 (meaning 0 or NULL), then you should use this logic:

SELECT *
FROM tablename
WHERE process <> 1 OR process IS NULL;

You could also slightly consolidate the WHERE clause using COALESCE:

SELECT *
FROM tablename
WHERE COALESCE(process, 0) <> 1;

score -1 · Answer 5 · answered Jul 08 '19 at 09:18

-1

Null is not a Value a column can have. Its more like a state, therefor you need to write

SELECT * FROM `tablename` WHERE `process` IS NULL

Also you can't reverse a clause with !=, you need to use <> in that case.

answered Jul 08 '19 at 09:18

Kevin Böhmer

462
4
21

Could someone please explain what is wrong with this answer? - Whoever downvoted it? – Kevin Böhmer Jul 08 '19 at 09:25

SQL query returns wrong results

5 Answers5