NULL values

NULL is a special marker used to represent missing or undefined values. It indicates the absence of a value in a column, which can be due to various reasons such as:

• A value not being provided during an insert operation.
• A value being explicitly set to NULL.
• A value being unknown or undefined.

Key Points about NULL in Oracle:

1. NULL is Not Equal to Any Value

• NULL is not equal to any other value, even to another NULL.
• For comparison, Oracle uses the IS NULL or IS NOT NULL conditions rather than using = or <>.
• Example:

SELECT * FROM employees WHERE salary IS NULL;

2. NULL in Queries and Operations

·        In SQL, performing arithmetic or logical operations on NULL will result in NULL.

·        Example:

SELECT salary + bonus FROM employees;

If either salary or bonus is NULL, the result will be NULL.

·        Use the NVL() or COALESCE() function to replace NULL values with a specified value.

Example with NVL():

SELECT NVL(salary, 0) FROM employees;

Example with COALESCE() (works with multiple arguments):

SELECT COALESCE(salary, bonus, 0) FROM employees;

3. NULL and Aggregate Functions

• Aggregate functions (e.g., COUNT(), SUM(), AVG(), etc.) generally ignore NULL values.
• COUNT(*) counts all rows, including those with NULLs. However, COUNT(column_name) counts only the rows where the column value is not NULL.
• Example:

·        SELECT COUNT(salary) FROM employees; -- Does not count rows where salary is NULL

4. NULL in WHERE Clause

• When filtering for NULL values, you must use IS NULL or IS NOT NULL in the WHERE clause.
• Example:

·        SELECT * FROM employees WHERE salary IS NULL;

5. NULL in Joins

·        In a join, NULL values may cause unexpected results because NULLs do not match each other. When you perform a join that includes NULLs, rows with NULL values in the join condition may not appear unless you specifically handle them.

·        Use OUTER JOINs to include rows with NULL values from either or both tables.

Example:

SELECT * FROM employees e

LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

6. NULL in Data Type

• A NULL can be stored in any column, regardless of its data type (e.g., NUMBER, VARCHAR, DATE, etc.).
• If a column is defined with a NOT NULL constraint, it cannot contain NULL values.

7. NULL and Functions

• Some built-in functions handle NULL values in specific ways:
• NVL(): Replaces NULL with a specified value.
• COALESCE(): Returns the first non-NULL value from a list of expressions.
• NULLIF(): Compares two expressions and returns NULL if they are equal; otherwise, it returns the first expression.
• IS NULL and IS NOT NULL: Used for checking NULL values.

Example of NULLIF():

SELECT NULLIF(salary, 0) FROM employees;

This returns NULL if salary is 0.

8. Behavior in Indexes

• Oracle indexes do not store NULL values for indexed columns. Therefore, a query with IS NULL may not use an index efficiently.

9. NULL and Subqueries

• Subqueries involving NULLs can behave differently than expected. For instance, subqueries might return NULL if no matching rows are found or when comparing NULL values.
• Example:

SELECT * FROM employees WHERE salary = (SELECT salary FROM employees WHERE department_id = 10);

If the subquery returns NULL, the main query will not return any rows.

10. NULL in DISTINCT

·        In Oracle, the DISTINCT keyword treats NULL values as equal, meaning if a column contains multiple NULL values, only one NULL will be returned.

Example:

SELECT DISTINCT department_id FROM employees;

If multiple rows have NULL for department_id, only one NULL will be displayed in the result set.

11. NULL in GROUP BY

·        When grouping data, all rows with NULL values in the column being grouped are treated as belonging to a single group.

Example:

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

All employees with NULL as department_id will be grouped together.

12. NULL and String Comparison

• When comparing a string with NULL, the result will be unknown because NULL is not considered equal to any string.
• Example:

·        SELECT * FROM employees WHERE name = NULL;  -- This will return no rows

Instead, use IS NULL for such cases.

13. NULL in Logical Operations

• In logical operations (AND, OR), NULL values yield uncertain or unknown results:
• TRUE AND NULL results in NULL.
• FALSE AND NULL results in FALSE.
• TRUE OR NULL results in TRUE.
• FALSE OR NULL results in NULL.