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;

Explanation:

  • NULLIF(transactions, 0) checks if transactions is 0. If it is, it returns NULL, avoiding division by zero.
  • If transactions is not zero, the division occurs normally.
  • For Ram, where transactions are 0, the result will be NULL instead of an error.

2 Comparing Two Columns

Another practical use of NULLIF is in comparing two columns. Consider a students table where we store exam scores:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    theory_score INT,
    practical_score INT
);

Insert sample data:

</>
Copy
INSERT INTO students (name, theory_score, practical_score)
VALUES 
('Arjun', 85, 85),
('Ram', 90, 85),
('Priya', 80, 80);

Now, let’s use NULLIF to identify students whose theory and practical scores are identical:

</>
Copy
SELECT 
    name, 
    theory_score, 
    practical_score,
    NULLIF(theory_score, practical_score) AS difference
FROM students;

Explanation:

  • If theory_score and practical_score are equal, NULLIF returns NULL.
  • If they are different, it returns the theory_score.
  • For Arjun and Priya, where both scores are identical, the difference column will be NULL.
  • For Ram, where scores are different, the result will be 90 (his theory score).

Conclusion

In this SQL NULLIF function tutorial, we covered:

  1. The syntax and working of NULLIF.
  2. Using NULLIF to prevent divide-by-zero errors.
  3. Applying NULLIF to compare two columns and detect identical values.