NULL values FAQS

 1. What does NULL mean in Oracle?

• In Oracle, NULL represents the absence of a value or an unknown value in a database column. It’s not the same as an empty string ('') or a zero (0); it is a distinct value indicating that the data is missing or undefined.

2. How do I check for NULL values in Oracle?

• To check for NULL values in Oracle, use the IS NULL or IS NOT NULL conditions in your WHERE clause:

SELECT * FROM employees WHERE salary IS NULL;

3. Can NULL be used in arithmetic operations in Oracle?

• No, when you perform arithmetic operations involving a NULL value, the result will be NULL. For example:

SELECT salary + bonus FROM employees;

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

4. How do I handle NULL values in calculations?

• You can use the NVL() function (or COALESCE()) to replace NULL with a default value:

SELECT NVL(salary, 0) + bonus FROM employees;

This will replace any NULL values in salary with 0.

5. What is the difference between NULL and an empty string in Oracle?

• An empty string ('') is a valid value, while NULL represents an absence of data. For example, a column with an empty string will still be treated as having a value, while a column with NULL is treated as having no value.

6. Do aggregate functions consider NULL values?

• Aggregate functions like COUNT(), SUM(), and AVG() ignore NULL values when performing calculations. However, COUNT(*) counts all rows, including rows with NULL values.

SELECT COUNT(salary) FROM employees; -- Ignores NULL salary values

7. Does Oracle treat NULL values as equal when performing comparisons?

• No, Oracle does not consider NULL to be equal to another NULL. For comparison, you need to use IS NULL or IS NOT NULL. For instance:

SELECT * FROM employees WHERE salary IS NULL;

8. How do I replace NULL with a default value in Oracle?

• You can use the NVL() function to replace NULL with a specified value:

SELECT NVL(salary, 0) FROM employees;

This will return 0 for rows where salary is NULL.

9. What happens if I try to use NULL in a GROUP BY clause?

• Oracle treats all NULL values in the same group in a GROUP BY clause. For example:

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

All employees with NULL in department_id will be grouped together.

10. Does Oracle use indexes for queries with NULL values?

• Oracle indexes do not store NULL values. Therefore, queries that involve NULL values in the WHERE clause might not use indexes effectively. This can impact performance when filtering on NULL values.

11. Can I store NULL values in columns with constraints?

• Yes, you can store NULL in a column unless it has a NOT NULL constraint. A column defined with NOT NULL cannot accept NULL values.

12. How does DISTINCT treat NULL values in Oracle?

• Oracle treats all NULL values as equal when using the DISTINCT keyword. If a column contains multiple NULL values, only one NULL will be returned in the result set.

13. Can I join tables on NULL values in Oracle?

• When joining tables, NULL values do not match each other. For example, in an INNER JOIN, rows with NULL values in the join column will not be included unless handled explicitly (e.g., with LEFT JOIN).

SELECT * FROM employees e

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

This will include rows where department_id is NULL in the employees table.

14. What is the difference between COALESCE() and NVL()?

• Both functions replace NULL values, but:
• NVL() accepts only two arguments, returning the second value if the first is NULL.
• COALESCE() accepts multiple arguments and returns the first non-NULL value from the list.

Example with COALESCE():

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

This will return the first non-NULL value among salary, bonus, or 0.

15. Can I use NULL in a WHERE clause with comparisons?

• No, comparisons like = or <> cannot be used directly with NULL. You need to use IS NULL or IS NOT NULL for filtering:

SELECT * FROM employees WHERE department_id IS NULL;