MySQL の NULL

SELECT NULL = NULL;    -- NULL (not 1)
SELECT NULL != NULL;   -- NULL (not 0)
SELECT NULL = 0;       -- NULL
SELECT NULL = '';      -- NULL
SELECT 1 + NULL;       -- NULL
SELECT 'hello' || NULL; -- NULL (logical OR with NULL is unknown, not concatenation — see below)

CREATE TABLE users (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    email    VARCHAR(255),
    referrer VARCHAR(255)  -- nullable
);

INSERT INTO users (email, referrer) VALUES
    ('a@example.com', 'google'),
    ('b@example.com', NULL),
    ('c@example.com', 'twitter');

-- This returns only 1 row — both the 'google' row and the NULL row are excluded
SELECT * FROM users WHERE referrer != 'google';

SELECT * FROM users WHERE referrer != 'google' OR referrer IS NULL;

-- Wrong — always returns no rows
SELECT * FROM users WHERE referrer = NULL;

-- Correct
SELECT * FROM users WHERE referrer IS NULL;
SELECT * FROM users WHERE referrer IS NOT NULL;

SELECT NULL <=> NULL;  -- 1 (true)
SELECT NULL <=> 0;     -- 0 (false)

SELECT
    COUNT(*)        AS total_rows,      -- counts all rows including NULLs
    COUNT(referrer) AS has_referrer,    -- counts only non-NULL referrer values
    AVG(referrer)   AS avg_referrer     -- ignores NULLs in the average
FROM users;

total_rows | has_referrer
-----------+-------------
3          | 2

-- Returns the first non-NULL value in the list
SELECT email, COALESCE(referrer, 'direct') AS source FROM users;

email           | source
----------------+---------
a@example.com  | google
b@example.com  | direct
c@example.com  | twitter

SELECT email, IFNULL(referrer, 'direct') AS source FROM users;

SELECT total_revenue / NULLIF(order_count, 0) AS avg_order_value FROM stats;

CREATE TABLE sessions (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT,           -- nullable: anonymous sessions
    token      VARCHAR(64) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL,
    INDEX idx_user (user_id)  -- indexes NULL values too
);

-- This query can use idx_user
SELECT * FROM sessions WHERE user_id IS NULL;

-- If any value in category_ids is NULL, this returns no rows
SELECT * FROM products WHERE category_id NOT IN (SELECT id FROM categories);

SELECT * FROM products p
WHERE NOT EXISTS (SELECT 1 FROM categories c WHERE c.id = p.category_id);

-- NULLs last in ascending order
SELECT * FROM users ORDER BY referrer IS NULL, referrer ASC;

-- NULLs first in ascending order (default behavior, but explicit)
SELECT * FROM users ORDER BY referrer IS NULL DESC, referrer ASC;