MUCH ADO ABOUT NOTHING

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 ) • Returns char1 concatenated with char2. SELECT CONCAT(‘job’,NULL) "Job" FROM DUAL; Job ----------------- job

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 • If the values match, then the result is NULL A := NULLIF(B,C);

13. COALESCE • 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. COALESCE (expr1, expr2, ..., expr n)

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 <> 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 > y THEN high := x; ELSE high := y; END IF; IF NOT x > 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 >=0 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,:commission:ind_com FROM EMP WHERE EMPNO = :emp_number; if (ind_comm == -1) pay = salary else pay=salary + commission;

30. C/C++ Set ind_comm = -1; EXEC SQL INSERT INTO emp (empno,comm) VALUES (:emp_number,:commision:ind_comm);

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-defined 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