MUCH%20ADO%20ABOUT%20NOTHING

1
MUCH ADO ABOUT NOTHING

• Walter Schenk
• SoluTech Consulting Services Inc

2
AGENDA

• What it is and what is not
• NULL in functions, expressions, comparisons and
  conditional control
• NULL in Indexes
• NULL in programming languages

3
WHAT A NULL IS NOT!

• A NULL is NOT 0!
• A NULL is NOT Nothing!
• A NULL is NOT an empty string (although Oracle
  does treat it as such but that may change in the
  future!)

4
THEN WHAT IS A NULL?

• When a column in a row has no value
• The value is not known or meaningful

5
WHY DO WE NEED NULL VALUES?

• Often real-world information is incomplete
• It is a way of handling this unknown

6
NULL IN FUNCTIONS

• Normal scalar functions will return NULL when
  given a NULL argument
• All aggregate functions except COUNT() and
  GROUPING ignore nulls.
• You can use the NVL in the argument to an
  aggregate function to substitute a value for a
  null.
• If a query with an aggregate function returns no
  rows or only rows with nulls for the argument to
  the aggregate function, the aggregate function
  returns null.

7
FUNCTIONS THAT HANDLE NULLS

• NVL
• CONCAT
• REPLACE
• NULLIF (9i)
• COALESCE (9i)
• DECODE

8
NVL
NVL ( expr1 , expr2 )

• If expr1 is null, returns expr2 if expr1 is not
  null, returns expr1.
• Can be any datatype

9
CONCAT
CONCAT ( char1 , char2 )
SELECT CONCAT(job,NULL) "Job" FROM
DUAL Job ----------------- job

• Returns char1 concatenated with char2.

10
REPLACE
REPLACE ( char , search_string,
replacement_string)

• Returns char with every occurrence of
  search_string replaced with replacement_string.
• If replacement_string is omitted or null, all
  occurrences of search_string are removed.
• If search_string is null, char is returned.

11
REPLACE
SELECT REPLACE(Hello there,l,NULL)
"Changes" FROM DUAL Changes -------------- Heo
there SELECT REPLACE(Hello there,NULL,xxx)
"Changes" FROM DUAL Changes -------------- Hello
there
12
NULLIF
A NULLIF(B,C)

• If the values match, then the result is NULL

13
COALESCE
COALESCE (expr1, expr2, ..., expr n)

• COALESCE returns the first non-null expr in the
  expression list.
• At least one expr must not be the literal NULL.
  If all occurrences of expr evaluate to null, then
  the function returns null.

14
DECODE

• NULL NULL !!!!

DECODE (deptno, 10, ACCOUNTING,
20, RESEARCH,
30, SALES,
NULL, OPERATION,
NONE)
15
NULLS AND COMPARISONS

• Only compare NULLs with IS NULL or IS NOT NULL
• Use of any other operator and the result will be
  NULL!
• NULL ltgt NULL (except in the DECODE expression
  and compound keys)

16
NULLS IN CONDITIONS

• Always use variable IS NULL
• Never user variable NULL

17
NULLS IN LOGICAL EXPRESSIONS
18
HANDLING NULLS

• Avoid common mistakes by keeping the following in
  mind
• Comparisons involving NULLs always yield NULL
• Applying the logical operator NOT to a NULL
  yields NULL
• In conditional control statements, if the
  condition yields NULL, its associated sequence of
  statements is NOT executed!

19
NULL AND CONDITIONAL CONTROL
MOST COMMON MISTAKE In conditional control
statements, if the condition yields NULL, its
associated sequence of statements is NOT executed!
20
NULL AND CONDITIONAL CONTROL
IF x gt y THEN high x ELSE high y END
IF
IF NOT x gt y THEN high Y ELSE high
x END IF
21
PROGRAMMING GUIDELINE
Always account for NULL in applications even if
the underlying database objects are defined as
NOT NULL.
22
NULL AND INDEX ENTRIES

• Oracle does NOT enter an index value if the
  ENTIRE key is NULL
• Consequences
• An index can NOT be used in a search criteria for
  NULL values
• A UNIQUE constraint on a column that can be NULL
  will allow multiple NULL values

23
NULLS IN PROGRAMMING LANGUAGES

• PL/SQL
• C/C
• VB.NET and C.NET

24
PL/SQL

• Full support of NULL

25
C/C

• C/C does NOT support NULL
• Variables are passed on to a C/C application
  through host variables for both input as well as
  output
• Host variables are prefixed with a colon () to
  set them apart from Oracle objects

26
C/C

• Any host variable can be associated with an
  indicator variable
• An indicator variable is a short integer variable
  that indicates the condition of its host variable

27
C/C host variable on Input

• If indicator variable 1 then the variable is a
  NULL and Oracle ignores the value of the host
  variable
• If indicator variable gt0 Oracle will assign the
  value of the host variable to the column

28
C/C host variable on Output

• If indicator variable -1 then the column is
  NULL and the value of the host variable is
  indeterminate
• If indicator variable 0 then value of the host
  variable is assigned

29
C/C
EXEC SQL SELECT SAL, COMM INTO
salary,commissionind_com FROM EMP WHERE
EMPNO emp_number if (ind_comm -1) pay
salary else paysalary commission
30
C/C
Set ind_comm -1 EXEC SQL INSERT INTO emp
(empno,comm) VALUES (emp_number,commisionind_c
omm)
31
VB

• In VB6 only Variant data types could support NULL
• The NULL keyword indicated that a variable
  contained the NULL value
• The IsNull function was used to test for NULL

32
VB.NET

• During a migration from VB6 to VB.NET
• Null is converted to DBNull
• IsNull is converted to IsDBNull
• The Variant data type is converted to Object
• In VB6 Null could be used in functions and
  assignments DBNull cannot!
• Consider using the Nothing keyword in .NET
  instead of Null.

33
VB.NET IsDBNull function

• Returns TRUE if the expression evaluates to the
  DBNull type otherwise returns FALSE
• The System.DBNull value indicates that the object
  represents missing or nonexistent data
• It is NOT the same as Nothing which indicates
  that a variable has not yet been initialized

34
VB.NET DBNull class

• The DBNull class is used to indicate the absence
  of a known value
• The class differentiates between a null value and
  an uninitialized value

35
PROGRAMMING GUIDELINE

• Do not circumvent the use of NULLs by assigning
  meaningless or out-of-range values
• Example a column EndDate is often assigned a
  far fetched date in the future to avoid use of
  NULL

36
SQL STANDARDS AND NULLS
FIPS 127-2 (1993) The following features have
"preliminary" syntax and semantics available in
Working Draft form as part of an on-going ANSI
and ISO/IEC standardization effort for further
development of the SQL language. Features
specified in preliminary form include   17.
Multiple null states. A facility that allows user
definitions for an arbitrary number of
application specific Null values, such as
"Unknown", "Missing", "Not Applicable",
"Pending", etc. Each such Null value would have a
different representation in the database so that
they could be distinguished during retrieval or
update.
37
SQL STANDARDS AND NULLS
FIPS 193-7 (1995) If an SQL/ERI Server
implementation at the Minimal SDL level or below
chooses not to provide support for null values
(see item 4 of Section 4.1), then it may raise an
implementation-defined exception in any SQL
statement that attempts to process null values.
If an SQL/ERI Server implementation at the
Minimal SDL level or below chooses not to provide
support for null values (see item 4 of Section
4.1), then it shall provide an implementation-defi
ned conversion of would-be null values in
Information Schema tables to an appropriate
non-null value. If an SQL/ERI Server
implementation at the Minimal SDL level or below
chooses not to provide support for null values
(see item 4 of Section 4.1), then it may raise an
implementation- defined exception in any SQL
statement that attempts to process null values.
38
SQL STANDARDS AND NULLS

• The concept of NULL is subject to change!
• Various implementations may vary.

39
DEPARTING WORDS

• Never ignore NULL
• Use NULL properly

40
QUESTIONS?

• NoCOUG, February 20, 2003