table 'materials'
id sku content
10 IT2 Iron
11 IT3 Steel
12 IT4 Steel
13 IT5 NULL
14 IT6 Iron
15 IT7 Glass
select id, sku, content from materials where content !='Iron';
Returns Result:
id sku content
11 IT3 Steel
12 IT4 Steel
15 IT7 Glass
Why is id #13 with the NULL value not returned in the result set? Using MYSQL.
The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
In SQL null is not equal ( = ) to anything—not even to another null . According to the three-valued logic of SQL, the result of null = null is not true but unknown. SQL has the is [not] null predicate to test if a particular value is null .
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. Hence, SQL has special IS NULL and IS NOT NULL predicates for dealing with NULL.
If != and <> both are the same, which one should be used in SQL queries? Here is the answer – You can use either != or <> both in your queries as both technically same but I prefer to use <> as that is SQL-92 standard.
As per your whereclause it compares null != 'Iron', which evaluates to UNKNOWN which neither true nor false based on SQL's 3 way logic. Hence it is not returned by the query.
In SQL, the not equal to operator ( !=) compares the non-equality of two expressions. That is, it tests whether one expression is not equal to another expression. If either or both operands are NULL, NULL is returned.
Since NULL doesn’t equal 3 and that was the only new criterion this can be confusing to new SQL writers. The issue here is that the not equal operators cannot be used to compare to NULL values and will always evaluate as false when a NULL value is involved.
From the documentation, NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL. The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.
As per your where
clause it compares null != 'Iron'
, which evaluates to UNKNOWN which neither true nor false based on SQL's 3 way logic. Hence it is not returned by the query.
If you need the null
row to be resulted, you should use
where content !='Iron' or content is null
Edit: One more option is to use the relational null-safe equality operator <=>
with a negation.
not content <=> 'Iron'
From the documentation,
NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.
The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.
mysql>
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL
UNION
SELECT 1 = 1, NULL = NULL, 1 = NULL;
Returns
1 vs 1 | NULL vs NULL | 1 vs NULL | Comment
--------------------------------------------------
1 | 1 | 0 | for <=>
1 | NULL | NULL | for =
ISNULL(content, '') = 'Iron'
This will convert the null to an empty string.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With