CodeCapsule

SQL NULL values

by

✅ What NULL Means

NULL = unknown / missing / not applicable value

Important:

  • NULL is not zero.

  • NULL is not an empty string.

  • NULL cannot be compared with = or !=.

✅ How to Check for NULL

Correct:

SELECT * FROM users
WHERE email IS NULL;

Not allowed (ALWAYS false):

WHERE email = NULL; -- ❌ wrong
WHERE email != NULL; -- ❌ wrong

✅ Handling NULL in Expressions

COALESCE (ANSI SQL)

Returns the first non-NULL value.

SELECT COALESCE(nickname, 'No Nickname') AS display_name
FROM users;

IFNULL (MySQL)

SELECT IFNULL(score, 0) FROM players;

NVL (Oracle)

SELECT NVL(age, 0) FROM users;

✅ NULL in Aggregations

Aggregates skip NULLs:

SELECT AVG(price) FROM products; -- ignores NULL prices

✅ NULL and JOINs

If a foreign key column contains NULL, the row will not match in an INNER JOIN.

SELECT *
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id;

Rows with o.customer_id IS NULL will appear with customer fields also NULL.

✅ NULL in Constraints

Allow NULL (default)

age INT NULL

Disallow NULL

age INT NOT NULL

🔥 Common Best Practices

✔ Use NULL to represent missing/unknown data
✔ Use NOT NULL for required columns
✔ Index columns with many NULLs carefully (engine-specific)
✔ Use COALESCE to substitute defaults safely

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *