Lets take a look at the following table (the quotation marks "..." only indicate that we have a string).

User Table
id color
1 "red"
2 "blue"
3 "red"
4 null
5 ""
This table is messed up because it has null values mixed with empty strings. If your dealing with null variables, the following might be useful to know:
NULL has no value, and so cannot be compared using the scalar value operators.
In other words, no value can ever be equal to (or not equal to) NULL because NULL has no value.
Said by Barry Brown

Little MySQL Quiz

Lets check if you can solve how many rows the following queries will count on the above table


SELECT count(*) FROM user WHERE color != null 
Show AnswerAnswer: 0. Null has no value and cannot be used with an compared operator.

SELECT count(*) FROM user WHERE color == null 
Show AnswerAnswer: 0. Null has no value and cannot be catched with a compared operator.

SELECT count(*) FROM user WHERE color is null 
Show AnswerAnswer: 1. This is the right way to check for a null column.

SELECT count(*) FROM user WHERE color != '' 
Show AnswerAnswer: 3. Color 1-3 are all different to ''. Color 4 is not comparable since it is null.

Little Quiz for Laravel

Lets check if you can solve how many rows the following queries will count on the above table


$table->where('color,'!=','red')->count();
Show AnswerAnswer: Only 2. This is because only color 2 & 5 are different from red. Color 4 is null and thus not comparable.

$table->where('color,'!=','red')->orWhereNull('color')->count();
Show AnswerAnswer: 3

$table->where('color,'!=','')->count();
Show AnswerAnswer: 3. Color 1-3 are all different to ''. Color 4 is not comparable since it is null.

$table->where('color,'=', null)->count(); 
Show AnswerAnswer: 1. This is a little tricky. My own guess was 0 because I thought Eloquent resolves the query to


SELECT count(*) FROM user WHERE color == null 

which would return 0. However, Laravel is realizing what you are doing and compiles it to the following SQL query:


SELECT count(*) FROM user WHERE color is null 

and thus you actually get 1.

Leave a comment

Your email address will not be published. Required fields are marked *