SQL Interview Questions

Handling NULL values in SQL is crucial for ensuring accurate data retrieval and manipulation. NULL represents the absence of a value or unknown value in a database. Here are several techniques and functions to handle NULL values effectively in SQL:

1. Using IS NULL and IS NOT NULL

To check for NULL values, use the IS NULL or IS NOT NULL operators.

Example

• Select rows with NULL values:

  SELECT * FROM employees WHERE middle_name IS NULL;

• Select rows without NULL values:

  SELECT * FROM employees WHERE middle_name IS NOT NULL;

2. Using COALESCE Function

The COALESCE function returns the first non-NULL value from a list of expressions. It’s useful for providing default values for NULL.

Syntax

COALESCE(expression1, expression2, ..., expressionN)

Example

• Provide a default value for NULL:

  SELECT first_name, 
         COALESCE(middle_name, 'N/A') AS middle_name, 
         last_name
  FROM employees;

3. Using IFNULL or ISNULL Functions (Database-specific)

Some databases have functions like IFNULL or ISNULL to handle NULL values.

Example for MySQL (IFNULL)

• Replace NULL with a default value:

  SELECT first_name, 
         IFNULL(middle_name, 'N/A') AS middle_name, 
         last_name
  FROM employees;

Example for SQL Server (ISNULL)

• Replace NULL with a default value:

  SELECT first_name, 
         ISNULL(middle_name, 'N/A') AS middle_name, 
         last_name
  FROM employees;

4. Using NULLIF Function

The NULLIF function returns NULL if two expressions are equal; otherwise, it returns the first expression. It’s useful for avoiding division by zero and other operations that might result in an error.

Syntax

NULLIF(expression1, expression2)

Example

• Avoid division by zero:

  SELECT total_sales / NULLIF(number_of_sales, 0) AS average_sale
  FROM sales_data;

5. Using CASE Statement

The CASE statement allows for more complex logic to handle NULL values.

Example

• Custom handling of NULL values:

  SELECT first_name, 
         CASE 
             WHEN middle_name IS NULL THEN 'N/A'
             ELSE middle_name
         END AS middle_name, 
         last_name
  FROM employees;

6. Aggregating with NULL Values

When using aggregate functions, NULL values are generally ignored. However, you can use functions like COUNT to count NULL values.

Example

• Count all rows including NULLs:

  SELECT COUNT(*) AS total_rows
  FROM employees;

• Count only non-NULL values:

  SELECT COUNT(middle_name) AS middle_name_count
  FROM employees;

• Count NULL values:

  SELECT COUNT(*) - COUNT(middle_name) AS null_middle_name_count
  FROM employees;

7. Ordering with NULL Values

By default, NULL values are sorted last in ascending order and first in descending order. You can change this behavior using NULLS FIRST or NULLS LAST.

Example

• Order by middle_name with NULLs last:

  SELECT first_name, middle_name, last_name
  FROM employees
  ORDER BY middle_name ASC NULLS LAST;

Conclusion

Handling NULL values in SQL is essential for ensuring data integrity and accuracy in your queries. Techniques like using IS NULL, COALESCE, IFNULL, ISNULL, NULLIF, and CASE statements, as well as understanding their behavior in aggregation and ordering, allow you to manage NULL values effectively. By applying these methods, you can ensure that your SQL queries handle NULL values in a way that meets your specific requirements and avoids potential pitfalls.