Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Where not equal does not return NULL results

Tags:

sql

mysql

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.

like image 596
Kevin Avatar asked Sep 04 '15 19:09

Kevin


People also ask

Do not return NULL values in SQL?

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.

IS NOT NULL equal to != In SQL?

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 .

Can we use != NULL in SQL?

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.

Is != and <> the same in SQL?

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.

Why is the value of Null is not returned by the query?

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.

What does not equal to mean in SQL?

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.

Why is null not equal to 3 in SQL?

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.

What is null-safe equal in SQL?

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.


Video Answer


2 Answers

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  =
like image 153
Vamsi Prabhala Avatar answered Oct 23 '22 02:10

Vamsi Prabhala


ISNULL(content, '') = 'Iron'

This will convert the null to an empty string.

like image 22
Shanuke Dissanayake Avatar answered Oct 23 '22 01:10

Shanuke Dissanayake