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
