Null Values Are Excluded. Why

NULL values are excluded. Why?

This is actually a common mistake made with SQL Server in treating NULL as a value. By default, it's treated as UNKNOWN, as documented here. So, in your view, you also need to include an OR t1.[Column1] IS NULL.

You can change this behavior by calling SET ANSI_NULLS OFF. It is not recommended to use this, however, as the feature is deprecated as pointed out by @Martin Smith.

This is not a SQL Server specific issue, however. It's part of the ANSI SQL standard.

Why NULL values are excluded while selecting values from table

You need to explicitly specify that you want NULL values:

from tb in _entities.Table1
where tb.Column3 == null || tb.Column3 != 201

When null values are present in data, logical and comparison operators
can potentially return a third result of UNKNOWN instead of just TRUE
or FALSE. This need for three-valued logic is a source of many
application errors.

Transact-SQL also offers an extension for null processing. If the
option ANSI_NULLS is set to OFF, comparisons between nulls, such as
NULL = NULL, evaluate to TRUE. Comparisons between NULL and any data
value evaluate to FALSE.

NOT condition excluding NULL values

Almost any comparison to NULL returns NULL, which is treated as false.

One rather expensive method is to use EXCEPT:

select t.*
from #temp t
except
select t.*
from #temp t
where (Field1 = 'Test1' or Field1 = 'Test10') and
      Field2 = 'Test2' and  
      (Field3 = 'Test3' or Field3 = 'Test30');

Note that this eliminates duplicate rows.

I would simplify the clause to:

where Field1 in ('Test1', 'Test10') and
      Field2 = 'Test2' and  
      Field3 in ('Test3', 'Test30');

Obviously, you can reconstruct the where clause to take null values into account. That is really cumbersome and prone to error. So, an alternative is to create a flag and just use the flag:

select t.*
from #temp t cross apply
     (values (case when (Field1 = 'Test1' or Field1 = 'Test10') and
                        Field2 = 'Test2' and  
                        (Field3 = 'Test3' or Field3 = 'Test30')
                   then 1 else 0
              end)
      ) v(flag)

Then:

 where v.flag = 1   -- for inclusion
 where v.flag = 0   -- for exclusion

Why does Snowflake exclude matching NULL values in WHERE clause?

All databases handle NULLs the same way (well, pretty much so). Almost any comparison involving NULL -- with the notable exceptions of IS NULL and IS NOT NULL and a few others -- returns NULL. WHERE clauses and CASE expressions treat NULL values the same as "false".

Fortunately, Snowflake also implements the standard SQL NULL-safe operator, IS DISTINCT FROM. So you can write the logic as:

SELECT p.*
FROM people p
WHERE Name IS DISTINCT FROM 'John'

Why does WHERE text != something also exclude all rows with NULL values?

Almost all comparisons with NULL return NULL (the most common excepts are IS NULL and IS NOT NULL). And in WHERE clauses, NULL is treated the same as "false" -- that is, the rows are filtered.

MySQL offers a NULL-safe comparison operator. You can use:

where not textfield <=> 'Word'

<=> returns true or false -- never NULL -- so it does what you expect.

Let me add: The SQL Standard has a NULL-safe operator. So, in Standard SQL, this would be:

where textfield is distinct from 'Word'

However, not many databases support the standard syntax -- yet.

How to exclude null value rows from the result set of CASE statement oracle SQL

A simple option - if query you have returns desired result set - is to use it as a CTE (or a subquery) and filter rows out:

    with temp as
    -- your current query begins here
    (SELECT DISTINCT td.task_id,td.cntr_nbr,lh.dsp_locn AS pull_locn,td.orig_reqmt,td.qty_pulld,
        (
        CASE 
        WHEN
        ((SUM(td.qty_pulld) over (partition by td.pull_locn_id)) < td.orig_reqmt)  and ((SUM(td.qty_pulld) over (partition by td.pull_locn_id))-td.orig_reqmt <> 0) 
        THEN (td.orig_reqmt- td.qty_pulld)
        END) AS units_shorted
        FROM wm14.task_dtl td 
        INNER JOIN wm14.locn_hdr lh ON lh.locn_id = td.pull_locn_id
        INNER JOIN wm14.order_line_item oli ON oli.item_id = td.item_id
        WHERE  EXISTS (SELECT 1 FROM wm14.msg_log ml WHERE ml.user_id = td.user_id AND ml.msg_id IN ('1060','1034') AND module = 'CTRLKEY' 
        AND TRUNC(td.mod_date_time) = TRUNC(ml.create_date_time)) AND td.invn_need_type IN ('53','54')
        AND td.stat_code >= '90' 
        and td.task_genrtn_ref_nbr NOT IN (SELECT  ml.ref_value_1 FROM wm14.msg_log ml WHERE  ml.msg ='Undo Wave completed')
        group by td.task_id,td.cntr_nbr,lh.dsp_locn,td.task_genrtn_ref_nbr,td.pull_locn_id,td.item_id,td.qty_pulld,td.orig_reqmt
    -- your current query ends here; only the ORDER BY clause is moved out
    )
    select *
    from temp
    where units_shorted is not null      --> filter
    ORDER BY task_id, dsp_locn DESC;

PostgreSQL: Exclude null value from comparison across columns

You could transform the columns to rows, the count the distinct values. That will automatically ignore NULL values:

select t.*,
       (select count(distinct x.col)
        from (
          values (t.col1), 
                 (t.col2), 
                 (t.col3), 
                 (t.col4)
        ) as x(col)
        where x.col is not null) = 1 as is_a_match
from the_table t

If you don't want to list all columns manually, you can use some JSON magic to turn the columns to rows in order to count the distinct values:

select t.*, 
       (select count(distinct x.val)
        from jsonb_each_text(to_jsonb(t) - 'id') as x(col, val)
        where x.val is not null) = 1
from the_table t

Filter out specific value without excluding the NULL rows

If you have this schema:

create table transaction(
  category varchar(15),
  sell int
)

insert into transaction(category, sell)
values('Apple',1), ('Orange',3), ('Banana',2), (null,1), ('Orange',2), (null,1)

You could use:

select *
from transaction
where category != 'Orange'
   or category is null

Which results in:

category    sell
Apple       1
Banana      2
null        1
null        1

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;

How to exclude null values when using DECODE in SELECT query

Add some more conditions into CASE. Why CASE and not DECODE? Because it allows flexibility.

SQL> select e_id,
  2    sum(case when q_id = 13 and (a_value is not null or r_pos_a_id is not null) then 1 else 0 end) src_cnt,
  3    sum(case when q_id = 15 and (a_value is not null or r_pos_a_id is not null) then 1 else 0 end) tgt_cnt,
  4    sum(case when q_id = 14 and (a_value is not null or r_pos_a_id is not null) then 1 else 0 end) src_oth,
  5    sum(case when q_id = 16 and (a_value is not null or r_pos_a_id is not null) then 1 else 0 end) tgt_oth
  6  from e_table
  7  group by e_id;

      E_ID    SRC_CNT    TGT_CNT    SRC_OTH    TGT_OTH
---------- ---------- ---------- ---------- ----------
        11          2          1          1          0

SQL>

---

Related Topics