Query condition if NULL values available in column

Question

I don't understand difference between two queries, see images. Attention on last condition. Mysql version is 5.7 What's the magic?

select distinct(pc) as aggregate 
from `installers` 
where 
    `success` =1 
    and 
    date(created_at) >= '2018-08-15' 
    and 
    date(created_at) <= '2018-08-21' 
    and 
    (free=0 or free is null)

(free is null or free=0)

select distinct(pc) as aggregate 
from `installers` 
where 
    `success` =1 
    and 
    date(created_at) >= '2018-08-15' 
    and 
    date(created_at) <= '2018-08-21' 
    and 
    free!=1

free!=1

Table structure

    CREATE TABLE `installers` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `driver_id` BIGINT(20) UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `success` TINYINT(4) NULL DEFAULT NULL,
    `version` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `pc` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `status` VARCHAR(180) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `error` VARCHAR(180) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `free` INT(11) NULL DEFAULT NULL,
    `time` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`),
    INDEX `installers_created_at_index` (`created_at`),
    INDEX `installers_updated_at_index` (`updated_at`),
    INDEX `installers_driver_id_foreign` (`driver_id`),
    INDEX `installers_success_index` (`success`),
    INDEX `installers_version_index` (`version`),
    INDEX `installers_pc_index` (`pc`(191)),
    INDEX `installers_status_index` (`status`),
    INDEX `installers_error_index` (`error`),
    INDEX `installers_free_index` (`free`),
    INDEX `installers_time_index` (`time`),
    CONSTRAINT `installers_driver_id_foreign` FOREIGN KEY (`driver_id`) REFERENCES `drivers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4709971
;

Distinct values of 'free' is NULL,0,1

Please replace the images of code with code. It makes it easier to cut and paste into potential answers - people may need to run your code to understand or fix it... — Mozahler, Aug 23 '18 at 15:06
So, (free is null or free=0) - is right query? - only you are in a position to make that decision. — P.Salmon, Aug 23 '18 at 15:14
The code in the images doesn't always match the linked code. Could you verify that they match and remove the images? — user2653663, Aug 23 '18 at 15:27
Possible duplicate of [what is "=null" and " IS NULL"](https://stackoverflow.com/questions/2749044/what-is-null-and-is-null) — Tab Alleman, Aug 23 '18 at 19:43
Note about `DISTINCT` : your use of it as `distinct(pc)` betrays a possible misunderstanding that is pretty common. It is not a function or a modifier applied to a column in parens `(pc)`. `DISTINCT` is rather a keyword applied to the entire `SELECT` query, all rows deduplicated and it should be written without the parens `()` as `SELECT DISTINCT pc...`. In this case with only one column selected, it makes no difference. But I commonly see patterns like `SELECT distinct(col1), col2, col3` with an incorrect expectation for what the result will be... — Michael Berkowski, Aug 23 '18 at 19:49
Your title isn't related to your question at all. You should consider changing it to something relevant to what you're asking about. — , Aug 23 '18 at 19:53

krokodilko · Accepted Answer · 2018-08-23T19:40:37.490

NULL is not the value, NULL (according to WIKI - NULL):

Null (or NULL) is a special marker used in Structured Query Language to indicate that a data value does not exist in the database.

This should not be confused with a value of 0. A null value indicates a lack of a value — a lack of a value is not the same thing as a value of zero in the same way that a lack of an answer is not the same thing as an answer of "no". For example, consider the question "How many books does Adam own?" The answer may be "zero" (we know that he owns none) or "null" (we do not know how many he owns). In a database table, the column reporting this answer would start out with no value (marked by Null), and it would not be updated with the value "zero" until we have ascertained that Adam owns no books.

SQL null is a state, not a value. This usage is quite different from most programming languages, where null value of a reference means it is not pointing to any object.

Since Null is not a member of any data domain, it is not considered a "value", but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown.

That is:

1 = 0 is FALSE, but 1 = NULL is UNKNOWN
1 != 0 is TRUE but 1 != NULL is also UNKNOWN

The UNKNOWN state in WHERE clause is equivalent to FALSE.

This is intuitive - since x is NULL (unknown), then we cannot say whether x = 1 nor x != 1 is true - in both cases the comparison result is not known.

Because of the above, there are special operators in SQL that check whether the column is null or not null -x IS NULL and x IS NOT NULL.

You can see this behaviour in this simple demo: http://www.sqlfiddle.com/#!9/9f78b0/5

SELECT * FROM t;
| id |      x |
|----|--------|
|  1 |      1 |
|  2 |      0 |
|  3 | (null) |

SELECT * FROM t WHERE x =1;
| id | x |
|----|---|
|  1 | 1 |

SELECT * FROM t WHERE x != 1;
| id | x |
|----|---|
|  2 | 0 |

Please note that the above query returned ony the record with x = 2 but skipped the record with x = NULL because the comparison x != NULL evaluated to UNKNOWN, which is equivalent to FALSE.

SELECT * FROM t WHERE x IS NULL;

| id |      x |
|----|--------|
|  3 | (null) |

SELECT * FROM t WHERE x IS NOT NULL;
| id | x |
|----|---|
|  1 | 1 |
|  2 | 0 |

SELECT * FROM t WHERE x = 1 OR x IS NULL;
| id |      x |
|----|--------|
|  1 |      1 |
|  3 | (null) |

SELECT * FROM t WHERE x != 1 OR x IS NULL;
| id |      x |
|----|--------|
|  2 |      0 |
|  3 | (null) |

M4HdYaR · Answer 2 · 2018-08-23T15:41:38.760

As you are using INT(11) your value in free column can be between -2147483648 and 2147483647

The default value of free column is NULL (It means unknown or not set , It does not mean 0 or any known number, Attention : NULL=NULL is always false because NULL is not equal NULL)

So when you use free=0 or free is null statement it means when free only equals to 0 or free is not set(NULL) and otherwise it is false

But when you use free!=1 it is true when free is 0 , -1 , 4145 and ... Every known number but 1 AND NULL

PS: Both of them can be true , It depends on you what you want from your query If the free is 1 only when it is free and otherwise it is 0 or NULL, then free=0 or free is null is correct statement But for Boolean values according to the MySQL manual you can use Bool and Boolean which are at the moment aliases of tinyint:

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

No, `free != 1` will _not_ be true when `free` is `NULL`. – AKX Aug 23 '18 at 15:30 — AKX, Aug 23 '18 at 15:30

Query condition if NULL values available in column

2 Answers2