🧾 MySQL ISNULL() Function – Complete Study Note

📘 1. Purpose of ISNULL() in MySQL

In MySQL, the ISNULL() function is used to check whether a value is NULL. It returns 1 (true) if the expression is NULL, and 0 (false) if it is not.

✅ Why is this important?

• NULL values represent missing, undefined, or unknown data.
• You often need to filter out or detect rows where data is missing.
• ISNULL() is a handy tool for conditionally identifying such values.

📌 2. Syntax of ISNULL()

sql
Copy code
ISNULL(expression)

• expression: Any column, value, or result of an operation.
• Returns:
• 1 if the expression is NULL
• 0 if the expression is not NULL

🧪 3. Basic Examples and Explanation

sql
Copy code
SELECT ISNULL(NULL);       -- Output: 1
SELECT ISNULL(5);          -- Output: 0
SELECT ISNULL(5 + NULL);   -- Output: 1
SELECT ISNULL(1 / 0);      -- Output: 1 (division by zero treated as NULL)

📝 Explanation:

• NULL is obviously NULL → returns 1.
• 5 is a number → not NULL → returns 0.
• 5 + NULL results in NULL → returns 1.
• 1 / 0 normally raises a division error, but MySQL returns NULL in this context → returns 1.

🔄 4. Difference Between ISNULL() and IS NULL Operator

There are two ways to check for NULL in MySQL:

✅ Example using IS NULL:

sql
Copy code
SELECT * FROM customers WHERE state IS NULL;

✅ Equivalent using ISNULL():

sql
Copy code
SELECT * FROM customers WHERE ISNULL(state);

✅ Both return the same result, but the IS NULL syntax is more commonly used in filters.

🏁 5. Special Use Case with DATE and '0000-00-00'

In MySQL, a date field declared as NOT NULL can still contain a "zero" date: '0000-00-00'.

🧪 Example:

sql
Copy code
CREATE TABLE special_isnull (
    start_date DATE NOT NULL
);

INSERT INTO special_isnull(start_date)
VALUES ('2000-01-01'),
       ('0000-00-00');

Although start_date is NOT NULL, MySQL allows the special "zero date" '0000-00-00' for compatibility with ODBC.

🔍 Querying This Table:

sql
Copy code
SELECT *
FROM special_isnull
WHERE ISNULL(start_date);

Expected: No rows (because NOT NULL constraint)

Actual: Returns one row where start_date = '0000-00-00'

📝 Explanation:

• ISNULL(start_date) returns 1 for '0000-00-00'
• MySQL treats '0000-00-00' as a special case in some settings (especially for ODBC compliance)

🔄 6. Negating ISNULL() and IS NULL

🚧 7. MySQL vs. SQL Server: ISNULL() Confusion

⚠️ Important distinction:

• In SQL Server, ISNULL(expr1, expr2) is a substitute function, equivalent to MySQL’s IFNULL().
• In MySQL, ISNULL() is a boolean function used to check for NULL.

🧠 So if you come from SQL Server:

✅ 8. Summary of Differences

🧠 Final Takeaways

• Use ISNULL(expr) when you want a binary result: 1 for NULL, 0 for non-NULL.
• Use IS NULL or IS NOT NULL when writing conditions in WHERE, CASE, or JOIN clauses.
• Do not confuse ISNULL() in MySQL with SQL Server’s version—use IFNULL() for substitution logic instead.
• Remember that MySQL treats '0000-00-00' as a special default date, which may cause ISNULL() to behave unexpectedly.