Conditional Logic and NULL Handling

#Introduction

So far our queries have only retrieved data. Now you'll learn how to transform data while querying it — one of the most common topics in dashboards, reports, Spring Boot APIs, JPA native queries, banking projects and 5+ years interviews.

#Sample Employee Table

#Why Do We Need Conditional Logic?

Your manager asks: show salary grades. Instead of Rahul | 70000, they want Rahul | Medium — no Java code, no Spring Boot code, everything inside SQL.

if (salary >= 80000)       grade = "High";
else if (salary >= 60000)  grade = "Medium";
else                       grade = "Low";

SQL equivalent:

CASE
    WHEN salary >= 80000 THEN 'High'
    WHEN salary >= 60000 THEN 'Medium'
    ELSE 'Low'
END

#Understanding NULL

One of the most misunderstood topics in SQL interviews. Many developers think NULL = empty. ❌ Wrong.

Comparing with NULL

WHERE bonus = NULL;     -- ❌ returns no rows
WHERE bonus IS NULL;    -- ✅

#CASE Expression

The most important topic in this chapter.

CASE
    WHEN condition THEN result
    WHEN condition THEN result
    ELSE result
END

Example 1 — Salary Grade

SELECT name,
       salary,
       CASE
           WHEN salary >= 80000 THEN 'High'
           WHEN salary >= 60000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_grade
FROM employees;

How CASE Executes

MySQL checks conditions from top to bottom:

(Without an ELSE, a row matching nothing gets NULL.)

Example 2 — Simple CASE

SELECT name,
       CASE department
           WHEN 'IT'      THEN 'Technology'
           WHEN 'HR'      THEN 'Human Resources'
           WHEN 'Finance' THEN 'Accounts'
           ELSE 'Other'
       END
FROM employees;

This form compares a single expression (department) against different values.

#IF()

A MySQL-specific function: IF(condition, true_value, false_value).

SELECT name,
       IF(salary >= 70000, 'Eligible', 'Not Eligible')
FROM employees;

Java equivalent: salary >= 70000 ? "Eligible" : "Not Eligible".

CASE vs IF

#IFNULL()

Replace NULL with another value:

SELECT name,
       IFNULL(bonus, 0)
FROM employees;

Amit's bonus shows 0 instead of NULL. Very useful for reports.

#NULLIF()

Returns NULL if two expressions are equal; otherwise the first:

SELECT NULLIF(10, 10);   -- NULL
SELECT NULLIF(10, 20);   -- 10

Why is NULLIF Useful? — Division by Zero

-- Risky if bonus can be 0
SELECT salary / bonus
FROM employees;

-- Explicit and portable
SELECT salary / NULLIF(bonus, 0)
FROM employees;

If bonus is 0, NULLIF returns NULL, so the division yields NULL instead of dividing by zero.

#COALESCE()

One of the most asked interview functions. Returns the first non-NULL value:

SELECT COALESCE(NULL, NULL, 100, 200);   -- 100

Real Example

SELECT COALESCE(mobile, office_phone, home_phone)
FROM users;   -- 9876543210

MySQL checks left to right and returns the first value that isn't NULL.

IFNULL vs COALESCE

#Nested CASE

SELECT name,
       CASE
           WHEN department = 'IT' THEN
               CASE
                   WHEN salary >= 80000 THEN 'Senior IT'
                   ELSE 'Junior IT'
               END
           ELSE department
       END
FROM employees;

Useful, but use it sparingly. If the logic becomes too complex, consider handling part of it in the application layer.

#CASE in ORDER BY

Business wants IT first, then Finance, then HR:

SELECT *
FROM employees
ORDER BY CASE
             WHEN department = 'IT'      THEN 1
             WHEN department = 'Finance' THEN 2
             ELSE 3
         END;

This creates a custom sort order.

#Conditional Aggregation (Preview)

Aggregation is covered in detail later, but here's a sneak peek — total salary only for IT:

SELECT SUM(CASE
               WHEN department = 'IT' THEN salary
               ELSE 0
           END)
FROM employees;

This pattern appears frequently in dashboards and reporting.

#Real Spring Boot Examples

Status Mapping

Stored statuses are 1, 2, 3. Instead of converting in Java:

SELECT CASE status
           WHEN 1 THEN 'Pending'
           WHEN 2 THEN 'Approved'
           WHEN 3 THEN 'Rejected'
       END
FROM requests;

Default Value

SELECT IFNULL(city, 'Unknown')
FROM users;

Optional Contact Number

SELECT COALESCE(mobile, office_phone, home_phone, 'No Contact')
FROM users;

#Performance Tips

✅ Using CASE in the SELECT list is fine — it doesn't affect how rows are filtered.

❌ Avoid wrapping indexed columns in functions in WHERE:

-- ❌ The function hides the column from the index
WHERE IFNULL(status, 0) = 1;

-- ✅ Same result, index-friendly (NULL becomes 0, which is not 1)
WHERE status = 1;

-- If NULL should count as 1, i.e. IFNULL(status, 1) = 1:
WHERE status = 1 OR status IS NULL;

Wrapping an indexed column in a function often prevents MySQL from using the index efficiently.

#Hands-On Assignment

Using the employees table:

1. Classify salaries as Low, Medium and High.
2. Display "No Bonus" if the bonus is NULL.
3. Display "No Manager" if manager_id is NULL.
4. Show "Technology" instead of "IT".
5. Display the first available phone number using COALESCE() (create sample columns if needed).
6. Sort employees with IT first, Finance second and everyone else last.
7. Write a query that avoids division by zero using NULLIF().
8. Calculate the total salary paid only to IT employees using CASE.

Challenge Problems

1. Create five salary bands using CASE.
2. Display "Experienced" if an employee has worked for more than three years; otherwise "New".
3. Replace every NULL salary with the department average (hint: you'll need subqueries — think about the approach first).
4. Use nested CASE to classify employees by both department and salary.
5. Return the first available contact from mobile, office, home, or "Not Available".