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
– Mozahler, Commented Aug 23, 2018 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
– P.Salmon, Commented Aug 23, 2018 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
– user2653663, Commented Aug 23, 2018 at 15:27

krokodilko · Accepted Answer · 2018-08-23 19:40:37Z

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 · Accepted Answer · 2018-08-23 15:41:38Z

0

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.

edited Aug 23, 2018 at 15:41

answered Aug 23, 2018 at 15:17

M4HdYaR

1,16411 silver badges28 bronze badges

1 Comment

AKX Over a year ago

No, free != 1 will not be true when free is NULL.

Collectives™ on Stack Overflow

Query condition if NULL values available in column

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related