How do you handle NULL values?

-- These return no rows!
SELECT * FROM users WHERE phone = NULL;    -- Wrong
SELECT * FROM users WHERE phone != NULL;   -- Wrong

-- Correct way
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- NULL in arithmetic = NULL
SELECT 100 + NULL;  -- NULL

-- NULL in string concatenation = NULL
SELECT 'Hello ' || NULL;  -- NULL (in most databases)

-- NULL in conditions
SELECT * FROM products WHERE price > 50;
-- Rows where price IS NULL are NOT included

SELECT
  name,
  COALESCE(phone, mobile, email, 'No contact') AS contact
FROM users;

-- Default value
SELECT name, COALESCE(nickname, name) AS display_name
FROM users;

-- Avoid division by zero
SELECT total / NULLIF(count, 0) AS average
FROM stats;

-- Convert empty string to NULL
SELECT NULLIF(name, '') AS name FROM users;

-- MySQL
SELECT IFNULL(phone, 'N/A') FROM users;

-- SQL Server
SELECT ISNULL(phone, 'N/A') FROM users;

-- Oracle
SELECT NVL(phone, 'N/A') FROM users;

-- MySQL: <=> operator
SELECT * FROM t1, t2 WHERE t1.value <=> t2.value;

-- Standard SQL
SELECT * FROM t1, t2
WHERE t1.value = t2.value OR (t1.value IS NULL AND t2.value IS NULL);