Meritshot Tutorials

  1. Home
  2. »
  3. SQL NULL Functions
SQL

SQL Tutorial

SQL NULL Functions

SQL NULL functions are used to handle NULL values in SQL queries. NULL represents the absence of a value or unknown data. Handling NULL values is essential for ensuring data integrity and accurate results in queries and calculations.

Common NULL Functions

1 IS NULL and IS NOT NULL

These functions are used to test whether a value is NULL or not NULL.

Syntax:

SELECT column_name

FROM table_name

WHERE column_name IS NULL;

SELECT column_name

FROM table_name

WHERE column_name IS NOT NULL;

Example:

Find Employees with Missing Phone Numbers:

SELECT employee_id, first_name

FROM employees

WHERE phone_number IS NULL;

Output:

employee_id

first_name

3

Ravi

5

Sneha

 

Find Employees with Provided Emails:

SELECT employee_id, first_name

FROM employees

WHERE email IS NOT NULL;

Output:

employee_id

first_name

1

Rajesh

2

Priya

 

2. COALESCE

The COALESCE function returns the first non-NULL value from a list of expressions.

Syntax:

SELECT COALESCE(expression1, expression2, …, default_value) AS result

FROM table_name;

Example:

Provide Default Value for Missing Email:

SELECT employee_id, first_name, COALESCE(email, ‘noemail@example.com’) AS email

FROM employees;

Output:

employee_id

first_name

email

1

Rajesh

rajesh@example.com

2

Priya

priya@example.com

3

Ravi

noemail@example.com

 

Choose the First Non-NULL Salary:

SELECT employee_id, COALESCE(bonus, salary, 0) AS final_amount

FROM employees;

Output:

employee_id

final_amount

1

55000

2

5000

3

0

 

3. NULLIF

The NULLIF function returns NULL if two expressions are equal; otherwise, it returns the first expression.

Syntax:

SELECT NULLIF(expression1, expression2) AS result

FROM table_name;

Example:

Avoid Division by Zero:

SELECT order_id, amount / NULLIF(discount, 0) AS adjusted_amount

FROM orders;

Output:

order_id

adjusted_amount

101

1000

102

2000

103

NULL

    • Handle Default Values in Data:

SELECT employee_id, NULLIF(salary, 50000) AS adjusted_salary

FROM employees;

Output:

employee_id

adjusted_salary

1

NULL

2

48000

3

70000

 

4. IFNULL or ISNULL

These functions are used to replace NULL with a specified value. IFNULL is used in MySQL, while ISNULL is used in SQL Server.

Syntax:

SELECT IFNULL(column_name, default_value) AS result

FROM table_name;

SELECT ISNULL(column_name, default_value) AS result

FROM table_name;

Example:

Replace Missing Phone Numbers with Default:

SELECT employee_id, first_name, IFNULL(phone_number, ‘N/A’) AS phone_number

FROM employees;

Output:

employee_id

first_name

phone_number

1

Rajesh

123-456-7890

2

Priya

N/A

3

Ravi

987-654-3210

Handle NULL Salaries in SQL Server:

SELECT employee_id, ISNULL(salary, 0) AS salary

FROM employees;

Output:

employee_id

salary

1

55000

2

48000

3

0

 

Tips to Remember

  1. Check for NULL Explicitly: Always check for NULL values using IS NULL or IS NOT NULL as NULL cannot be compared using standard operators like =.
  2. Use COALESCE for Default Values: Use COALESCE to provide default values for NULL columns in queries and calculations.
  3. Avoid NULL Division Errors: Use NULLIF to prevent errors in calculations where division by zero might occur.
  4. Understand Function Differences: Be aware of differences between IFNULL, ISNULL, and COALESCE in different SQL dialects.

Power of NULL Functions

  • Data Cleaning: Helps in managing and transforming data where NULL values are prevalent.
  • Error Prevention: Prevents errors in calculations and aggregations due to NULL values.
  • Enhanced Data Handling: Improves the accuracy and reliability of query results by handling NULL values effectively.

Frequently Asked Questions

Q1: How can I handle NULL values in aggregate functions?
A1: Aggregate functions like SUM, COUNT, and AVG generally ignore NULL values. Use COALESCE to replace NULL with default values before aggregation if needed.

Example:

SELECT COALESCE(SUM(salary), 0) AS total_salary

FROM employees;

Q2: Can I use NULLIF with text data?
A2: Yes, NULLIF can be used with text data to return NULL if two text expressions are equal.

Example:

SELECT NULLIF(employee_name, ‘John Doe’) AS valid_name

FROM employees;

Q3: What is the difference between ISNULL and COALESCE?
A3: ISNULL is specific to SQL Server, while COALESCE is a standard SQL function supported by most databases. COALESCE can take multiple arguments and return the first non-NULL value, whereas ISNULL takes only two arguments.

Example with COALESCE:

SELECT COALESCE(column1, column2, ‘default_value’) AS result

FROM table_name;

Q4: How do I handle NULL in JOIN operations?
A4: Use outer joins (LEFT JOIN, RIGHT JOIN) to include rows with NULL values in the join condition. You can also use COALESCE to provide default values in the result.

Example:

SELECT a.id, COALESCE(b.value, ‘default’) AS value

FROM table_a a

LEFT JOIN table_b b ON a.id = b.id;

Q5: How do I perform conditional replacements for NULL values in a CASE statement?
A5: Use CASE statements to handle NULL values conditionally and provide alternative values or transformations.

Example:

SELECT employee_id,

       CASE

           WHEN salary IS NULL THEN ‘No Salary Info’

           ELSE CAST(salary AS VARCHAR)

       END AS salary_info

FROM employees;

❮ Previous
Next ❯
ads website