👉Tutorial-2 : MySQL AUTO_INCREMENT
👉Tutorial-3: MYSQL ALTER
👉Tutorial-4: MYSQL Index Tutorial
What is NULL in MySQL?
MySQL IS NULL & IS NOT NULL: In SQL, NULL represents missing or undefined data. It acts as a placeholder for values that do not exist in a table.
🔹 Key Facts About NULL:
✔ NULL is not a data type (e.g., INT, VARCHAR).
✔ Arithmetic operations with NULL return NULL. (Example: 10 + NULL = NULL)
✔ Aggregate functions ignore NULL values unless specified otherwise.
Checking for NULL Values in MySQL
MySQL IS NULL & IS NOT NULL: You cannot use standard comparison operators (=, !=, <, >) to check for NULL. Instead, use:
✅ IS NULL – Checks if a column has NULL values.
✅ IS NOT NULL – Checks if a column does not have NULL values.
Example 1: Find Members Without Contact Numbers
sql
SELECT * FROM members WHERE contact_number IS NULL;
Example 2: Find Members Who Have Provided Contact Numbers
sql
SELECT * FROM members WHERE contact_number IS NOT NULL;
Why NOT NULL is Important?
MySQL IS NULL & IS NOT NULL: Sometimes, you need to ensure that a column always has a value. You can define a column as NOT NULL when creating a table.
Example: Creating a Table with NOT NULL Constraints
sql
CREATE TABLE employees (
employee_id INT NOT NULL,
full_names VARCHAR(255),
gender VARCHAR(10)
);
🔹 If you try inserting a record without an employee_id, MySQL throws an error.
NULL in Aggregate Functions
MySQL IS NULL & IS NOT NULL: By default, MySQL ignores NULL values in aggregate functions like COUNT, SUM, AVG, MIN, and MAX.
Example: Count Members with a Contact Number
sql
SELECT COUNT(contact_number) FROM members;
✅ This counts only the rows where contact_number is not NULL.
NULL in Boolean Operations
NULL creates a three-valued logic:
| Condition | Result |
| NULL = NULL | NULL |
| NULL != NULL | NULL |
| NULL > NULL | NULL |
| NULL IS NULL | TRUE |
| NULL IS NOT NULL | FALSE |
Example: Checking for NULL in Boolean Comparisons
sql
SELECT NULL = NULL; — Returns NULL
SELECT 5 IS NULL; — Returns FALSE (0)
SELECT NULL IS NULL; — Returns TRUE (1)
Summary
✔ NULL represents missing or unknown data in MySQL.
✔ Use IS NULL and IS NOT NULL to filter records.
✔ Use NOT NULL to enforce mandatory values in a column.
✔ Aggregate functions ignore NULL by default.
✔ Boolean operations with NULL return TRUE, FALSE, or NULL.
Next Steps: Practice writing MySQL queries using IS NULL and IS NOT NULL in your database!
