What is NULL in MySQL?
MySQL IS NULL & IS NOT NULL: In MySQL, NULL is a special marker that represents missing or unknown data. It is neither zero nor an empty string—it simply means the absence of a value.
Key Facts About NULL in MySQL
- NULL is not a data type—it’s a placeholder for missing values.
- Any arithmetic operation with NULL returns NULL (e.g., 5 + NULL = NULL).
- Aggregate functions (COUNT, SUM, AVG, etc.) ignore NULL values unless specified otherwise.
Using IS NULL & IS NOT NULL in MySQL
MySQL IS NULL & IS NOT NULL: Since NULL is not a standard value, you cannot compare it using =, !=, or other operators. Instead, MySQL provides IS NULL and IS NOT NULL to check for NULL values.
1. Checking for NULL Values: IS NULL
Use IS NULL to filter records where a column contains NULL.
Example: Find Members Without an Email Address
sql
SELECT * FROM members WHERE email IS NULL;
🔹 Result: Returns all members who have not provided an email address.
2. Filtering Non-NULL Values: IS NOT NULL
MySQL IS NULL & IS NOT NULL: Use IS NOT NULL to retrieve records where a column has a value (not NULL).
Example: Find Members Who Provided a Contact Number
sql
SELECT * FROM members WHERE contact_number IS NOT NULL;
🔹 Result: Returns all members with a registered phone number.
Creating NOT NULL Constraints
MySQL IS NULL & IS NOT NULL: To prevent NULL values in certain columns, you can define them as NOT NULL during table creation.
Example: Creating a Table with NOT NULL Constraints
sql
CREATE TABLE employees (
employee_number INT NOT NULL,
full_names VARCHAR(255),
gender VARCHAR(6)
);
🔹 Rule: The employee_number column must always have a value when inserting a new record.
Trying to Insert a NULL Value in a NOT NULL Column
sql
INSERT INTO employees (full_names, gender) VALUES (‘Steve Jobs’, ‘Male’);
🔹 Error: MySQL will throw an error because employee_number is NOT NULL.
NULL in Boolean Comparisons
MySQL IS NULL & IS NOT NULL: Since NULL represents an unknown value, logical comparisons with NULL behave differently.
| Condition | Result |
| 5 = 5 | TRUE (1) |
| NULL = NULL | NULL (Unknown) |
| 5 > NULL | NULL (Unknown) |
| NULL IS NULL | TRUE (1) |
| NULL IS NOT NULL | FALSE (0) |
Examples of NULL Behavior in MySQL Queries
1. Comparing NULL Values
sql
SELECT NULL = NULL;
🔹 Result: NULL (because two unknown values cannot be compared).
2. Checking if NULL Exists in a Column
sql
SELECT 5 IS NULL;
🔹 Result: 0 (FALSE, because 5 is not NULL).
sql
SELECT NULL IS NULL;
🔹 Result: 1 (TRUE, because NULL is indeed NULL).
Summary: Key Takeaways
✅MySQL IS NULL & IS NOT NULL: NULL represents missing or unknown data in MySQL.
✅ You must use IS NULL or IS NOT NULL for NULL value comparisons.
✅ Arithmetic operations involving NULL return NULL.
✅ NOT NULL constraints prevent inserting NULL values into a column.
✅ Boolean comparisons involving NULL can return TRUE, FALSE, or NULL.
Understanding how NULL works in MySQL will help you write more accurate queries and prevent unexpected errors!
Click To Open
👉Tutorial-2: MYSQL AUTO INCREMENT
👉Tutorial-3: MYSQL ALTER
👉Tutorial-4: MYSQL LIMIT
