Sunday, 24 September 2017

Home » , , , , , » How To Treat The NULL Vacuum
September 24, 2017

How To Treat The NULL Vacuum

 , , , , ,  1 comment

It make some sense as the vacuum is something devoid of matter, something we do not really know about its contents, As does the NULL in a database.

Here are a few points that need consideration as NULL plays the role of a double-edged sword in a database, providing a very efficient way to store unknown values , however not treated wisely could have a nastier effect especially, during data manipulation:

  • The Term null value is inaccurate as null indicates the lack of a value.
  • A null is as being a marker for a missing value that might never be known, might be determined later, or is not applicable. 
  • Nulls can be stored in columns of any data type that are not restricted by NOT NULL or PRIMARY KEY
  • Nulls aren't equal (or unequal) to each other. You can't determine whether a null matches any other value, including another null, so the expressions NULL=any_value, NULL<>any_value, NULL=NULL, and NULL<>NULL are neither true nor false but unknown;
  • Use IS [NOT] NULL to detect a null;
  • Oracle treats an empty string ('') as a null. 
Select DECODE('',NULL, 'TRUE', 'FALSE') IS_NULL
From dual

With test as (Select '' as col from dual)
Select 'TRUE' IS_NULL
From test t
Where t.col is NULL

  • DISTINCT Treats all the nulls in a particular column as duplicates
  • For Sorting you can specify Order by col [NULLS (FIRST|LAST)]. If you don not specify and Sort by a column that contains nulls, the nulls will be either NULLS LAST if ASC , also remember ASC is the Default if not specified, and NULL FIRST if desc
  • Any computations involving a Null can result in Null
Select 12+3+NULL Sum
from dual
so better to use always
Select 12+3+NVL(NULL,0) Sum
from dual
  • Aggregate functions, such as COUNT(COLUM_HAVING_NULLS),SUM, MAX, MIN, AVG, etc., all ignore NULLs while  COUNT(*) and GROUPING doesn't ignore NULLS 
  • If the grouping column in a GROUP BY clause contains nulls, all the nulls are put in a single group
  • Nulls affect the results of joins, the Inner Joins and The Left and Right Outer Joins. 
  • Nulls can cause problems in subqueries. I  suggest to do some googling on the topic as you can get some weird results just by placing keywords in different positions in your query. For example,
    WHERE NOT col IN (SELECT col FROM table1); can yeild different results from WHERE  col NOT IN (SELECT col FROM table1);  
  • In Set Operations, UNION,MINUS,INTERSECT will treat nulls as duplicates, Except for the UNION ALL which treats each null as different.
select 1, NULL from dual 
Union 
select 2, NULL from dual
Union
Select 1, NULL from dual;

select 1, NULL from dual 
Union
select 2, NULL from dual
Minus
Select 1, NULL from dual;

select 1, NULL from dual 
Union 
select 2, NULL from dual
Intersect
Select 1, NULL from dual;

  • Sometimes we call the same function twice in our query without any need thereof.Here is a tip when NVL is used in the where clause.
NVL(PRIMARY_QTY,0) > 0
Means 3 Things
  1. Nulls  converted to Zero(0) and
  2. PRIMARY_QTY is > 0
  3. so in the Select we can just use  PRIMARY_QTY no need to use NVL(PRIMARY_QTY,0) PRIMARY_QTY , since it's already filtered out

Some Resources

1 comment: