SQL Not Displaying Null Values on a Not Equals Query

SQL not displaying null values on a not equals query?

In several languages NULL is handled differently: Most people know about two-valued logic where true and false are the only comparable values in boolean expressions (even is false is defined as 0 and true as anything else).

In Standard SQL you have to think about three-valued logic. NULL is not treated as a real value, you could rather call it "unknown". So if the value is unknown it is not clear if in your case state is 0, 1, or anything else. So NULL != 1 results to NULL again.

This concludes that whereever you filter something that may be NULL, you have to treat NULL values by yourself. Note that the syntax is different as well: NULL values can only be compare with x IS NULL instead of x = NULL. See Wikipedia for a truth table showing the results of logic operations.

SQL Where not equal does not return NULL results

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  =

Not equal != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not null value, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.

Not equal query excludes NULL values

Nulls are not counted in comparison, if you want null values to be returned then you need to execute the following query:

SELECT * FROM MasterList WHERE Requested <> "Yes" OR Requested IS NULL;

SQL not equals & null

In Oracle, there is no difference between an empty string and NULL.

That is blatant disregard for the SQL standard, but there you go ...

In addition to that, you cannot compare against NULL (or not NULL) with the "normal" operators: "col1 = null" will not work, "col1 = '' " will not work, "col1 != null" will not work, you have to use "is null".

So, no, you cannot make this work any other way then "col 1 is null" or some variation on that (such as using nvl).

T-SQL: select rows not equal to a value, including nulls

Try checking for NULL explicitly:

SELECT TOP 30 col1, col2, ..., coln
FROM Mails
WHERE (assignedByTeam <> 'team01' OR assignedByTeam IS NULL)

MYSQL syntax not evaluating not equal to in presence of NULL

use IS NULL or IS NOT NULL to compare NULL values because they are simply unknown.

SELECT name 
from   products p
WHERE  backorder IS NULL OR backorder <> 2

• SQLFiddle Demo

• SQLFiddle Demo (added some records)

• Working with NULL Values

test on a value and not (test on a value) are false

NULL is the unknown value. You cannot compare it.

• NULL = 1 results in "unknown"
• NULL <> 1 results in "unknown"
• NOT (NULL = 1) results in "unknown"

In a WHERE clause the condition must be true for the row to be returned, though.

One way to go about this is to explicitely consider nulls:

WHERE (c <> 1 OR c IS NULL)

Or (for the lack of standard SQL's IS DISTINCT FROM) with Oracle's DECODE:

WHERE DECODE(c, 1, 'same', 'different') = 'different'

NULL values not filtered out with WHERE statement

use this for only want null recode

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME IS NULL
or
SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE ISNULL(VOLUME,'') = ''

if you get not null value then use

SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE ISNULL(VOLUME,'') <> ''
or 
SELECT ID, VOLUME,  TYPEOF(VOLUME) FROM DBT.BASE WHERE VOLUME IS NOT NULL

---

Related Topics