Mysql comparison with null value

Beyond the Basic NULLs

Understanding NULL and its oddities

In MySQL, NULL is an indication of a value being unknown, hence operations involving NULL do not result in conventional outcomes. A standard = or != comparison with NULL is not permissible.

The role of <=>: NULL-safe equal operator

When faced with a possible NULL comparison, the NULL-safe equal operator <=> is your go-to tool. The operator verifies if two expressions are identical or if both are NULL, returning 1, and 0 otherwise.

-- How a NULL-safe guy walks...
SELECT ('A' <=> 'A'), (NULL <=> NULL), ('A' <=> NULL);  
-- Spoiler: He's ultra careful🕵️
-- Returns: 1, 1, 0


Resorting to COALESCE function in NULL wilderness

The COALESCE() function ascends to the role of a Savior when dealing with NULLs. It allows you to suggest alternative values and returns the first non-NULL value from a list:

-- COALESCE: Serving the first non-NULL value since inception
SELECT COALESCE(NULL, NULL, 'available', 'not available');
-- Returns: 'available'


Weathering the NULL storm: Common pitfalls

When aggregate functions meet NULL

Aggregate functions like SUM() and AVG() tend to ignore NULL values, resulting in skewed computations. To include them as, say zero, use COALESCE().

-- NULLs ganging up to troll AVG function 
SELECT AVG(column) FROM table;   
-- NULLs are ignored in AVG, leading to a "meh" outcome 🤷‍♂️


Filtering through NULL

The condition WHERE column != 'value' does not include NULLs in the result set. To account for NULL values, append OR column IS NULL:

-- Include Mr. NULL in the party! 
SELECT * FROM table WHERE column != 'value' OR column IS NULL;


The saga of NULL and JOINS

When joining columns potentially containing NULLs, remember to invite <=> NULL-safe operator for better results:

-- How to include Mr. NULL in the expedition? Invite NULL-safe operator!
SELECT * FROM table1 t1
JOIN table2 t2 ON t1.maybe_null_column <=> t2.maybe_null_column;


Mastering NULL like a Pro

Dealing with NULL in conditional logic

Big Brain Move: Use CASE or IF to handle NULLs within complex conditional logic:

-- A "smart" SELECT using CASE to address NULLs
SELECT 
  CASE 
    WHEN column IS NULL THEN 'N/A'
    ELSE column
  END 
FROM table;


Not all exclusions are equal: NOT with NULL

To exclude a certain value from the result, use NOT (column <=> 'value'). This strategy accounts for NULL values in the result set:

-- Exclusivity at its best: Exclude 'value' while keeping Mr.NULL
SELECT * FROM table WHERE NOT (column <=> 'value');
