SQL NULLIF

The SQL NULLIF function is used to compare two expressions and return NULL if they are equal. Otherwise, it returns the first expression. This function is useful for handling divide-by-zero errors and managing conditional comparisons in SQL queries.

In this tutorial, we will explore the NULLIF function, its syntax, and practical examples demonstrating its usage.

Syntax of SQL NULLIF Function

The syntax for the NULLIF function is as follows:

</>
Copy
NULLIF(expression1, expression2)

Explanation:

  • expression1: The first value or expression to compare.
  • expression2: The second value or expression to compare with the first.
  • If both expressions are equal, NULLIF returns NULL.
  • If the expressions are different, it returns the first expression.

Step-by-Step Examples Using SQL NULLIF

1 Handling Divide-By-Zero Error

One of the most common use cases of NULLIF is to prevent divide-by-zero errors. Let’s consider a scenario where we have a sales table storing revenue and the number of sales transactions:

</>
Copy
CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_name VARCHAR(50),
    revenue DECIMAL(10,2),
    transactions INT
);

Insert some sample data:

</>
Copy
INSERT INTO sales (employee_name, revenue, transactions)
VALUES 
('Arjun', 5000.00, 10),
('Ram', 7000.00, 0),  -- Zero transactions
('Priya', 3000.00, 5);

Now, we want to calculate the average revenue per transaction. If we divide by zero, SQL will throw an error. Using NULLIF, we can prevent this:

</>
Copy
SELECT 
    employee_name, 
    revenue, 
    transactions,
    revenue / NULLIF(transactions, 0) AS avg_revenue_per_transaction
FROM sales;