NULL Handling

IFNULL() and NULLIF()

IFNULL(expr1, expr2)

Returns expr1 if it is not NULL, otherwise expr2.

NULLIF(expr1, expr2)

Returns NULL if expr1 = expr2, otherwise returns expr1.

SELECT IFNULL(5,'abc'), IFNULL(NULL, 'abc');

5, 'abc'

SELECT NULLIF(10,10), NULLIF(9,10);

NULL, 9

SELECT IFNULL(5,'abc'), IFNULL(NULL, 'abc');

SELECT NULLIF(10,10), NULLIF(9,10);

SELECT IFNULL(5,'abc'), IFNULL(NULL, 'abc');

SELECT NULLIF(10,10), NULLIF(9,10);

SELECT IFNULL(5,'abc'), IFNULL(NULL, 'abc'):

IFNULL(5,'abc')	IFNULL(NULL, 'abc')
5	abc

SELECT NULLIF(10,10), NULLIF(9,10):

NULLIF(10,10)	NULLIF(9,10)
NULL	9

ISNULL(expr) returns 1 if expr is NULL, 0 otherwise — a shorthand alternative to expr IS NULL. See also COALESCE(), which generalizes IFNULL() to more than two arguments.

NULLs from an Outer Join

NULL values commonly need special handling after a LEFT or RIGHT JOIN, where unmatched rows on the outer side of the join are padded with NULLs.

CREATE TABLE Purchases (
  product VARCHAR(64), price FLOAT, country VARCHAR(64)
);
INSERT INTO Purchases VALUES
  ('Vacuum Cleaner', 120, 'UK'),
  ('Intel Processor Chip', 500, 'USA'),
  ('Intelligent Rice Cooker', 200, 'China');

CREATE TABLE Shipping (
  country VARCHAR(64), fee FLOAT
);
INSERT INTO Shipping VALUES
  ('China', 20), ('USA', 5), ('Brazil', 10);

SELECT *, ISNULL(Shipping.fee) AS is_local_purchase,
Purchases.price + IFNULL(Shipping.fee,1) AS total_cost
FROM Purchases LEFT OUTER JOIN Shipping USING (country);

UK has no matching row in Shipping, so its fee comes back NULL; ISNULL(fee) flags that row as 1 (a "local" purchase with no shipping fee on record), and IFNULL(fee,1) substitutes a fallback of 1 so the total_cost arithmetic doesn't itself become NULL.

CREATE TABLE Purchases (
    product VARCHAR(64),
    price FLOAT,
    country VARCHAR(64)
);
INSERT INTO Purchases VALUES
    ('Vacuum Cleaner', 120, 'UK'),
    ('Intel Processor Chip', 500, 'USA'),
    ('Intelligent Rice Cooker', 200, 'China');

CREATE TABLE Shipping (
    country VARCHAR(64),
    fee FLOAT
);
INSERT INTO Shipping VALUES
    ('China', 20), ('USA', 5), ('Brazil', 10);

SELECT *, ISNULL(Shipping.fee) AS is_local_purchase,
       Purchases.price + IFNULL(Shipping.fee, 1) AS total_cost
    FROM Purchases LEFT OUTER JOIN Shipping USING (country);

CREATE TABLE Purchases (
    product VARCHAR(64),
    price FLOAT,
    country VARCHAR(64)
);
INSERT INTO Purchases VALUES
    ('Vacuum Cleaner', 120, 'UK'),
    ('Intel Processor Chip', 500, 'USA'),
    ('Intelligent Rice Cooker', 200, 'China');

CREATE TABLE Shipping (
    country VARCHAR(64),
    fee FLOAT
);
INSERT INTO Shipping VALUES
    ('China', 20), ('USA', 5), ('Brazil', 10);

SELECT *, ISNULL(Shipping.fee) AS is_local_purchase,
       Purchases.price + IFNULL(Shipping.fee, 1) AS total_cost
    FROM Purchases LEFT OUTER JOIN Shipping USING (country);

country	product	price	fee	is_local_purchase	total_cost
UK	Vacuum Cleaner	120	NULL	1	121
USA	Intel Processor Chip	500	5	0	505
China	Intelligent Rice Cooker	200	20	0	220

Comparison	<<<	Data Manipulation Language	>>>	Numeric
last update: 17 Aug 2026

MENU

NULL Handling

IFNULL() and NULLIF()

NULLs from an Outer Join