MySQL where is null condition with query; Through this tutorial, i am going to show you how to use MySQL WHERE IS NULL with the help of examples.
MySQL IS NULL Condition
- Syntax of MySQL IS NULL Condition
- Parameters of MySQL IS NULL Condition
- Example 1 – Using SELECT Clause With MySQL IS NULL
- Example 2 – Using “INSERT INTO” Clause With MySQL IS NULL
- Example 3 – UPDATE Clause With MySQL IS NULL
- Example 4 – DELETE Clause With MySQL IS NULL
- Example 5 – Using IS NULL On Join Conditions
- Example 6 – Using IS NULL With Logical Operator
“IS NULL” is a keyword of MySQL that compares Boolean. It returns true if the given value is NULL and false if the given value is not NULL.
If you want to insert, update, delete or modify your data conditionly in MySQL database table. So, you can use “IS NULL” with MySQL clauses.
Syntax of MySQL IS NULL Condition
MySQL syntax is “IS NULL” given below : –
table_column_name IS NULL OR exp IS NULL
Parameters of MySQL IS NULL Condition
table_column_name(exp) :- It’s the table column (field) name that you check the value if it is NULL.
When we use MySQL “IS NULL” with any table column (field) , then it will be true and false.
- If the given condition is fulfilled, then it will come true.
- If the given condition is not fulfilled, then it will come false.
Example 1 – Using SELECT Clause With MySQL IS NULL
See the following example for how to use the MySQL SELECT Clause with IS NULL; as follows:
SELECT * FROM users WHERE email_address IS NULL;
The above example query will fetch all records from database table users using MySQL IS NULL & SELECT Clause. Where the email_address column (field) contain a null value.
Example 2 – Using “INSERT INTO” Clause MySQL “IS NULL”
See the following example for how to use the MySQL “INSERT INTO” Clause with IS NULL; as follows:
INSERT INTO vendors ( name, phone, addressLine1, addressLine2, city, state, postalCode, country, customer_id ) SELECT name, phone, addressLine1, addressLine2, city, state , postalCode, country, customer_id FROM customers WHERE country IS NULL;
For the above example of MySQL “IS NULL” with “INSERT INTO” Clause will insert all records from database table vendors. Where the country column (field) contain a null value.
Example 3 – UPDATE Clause With MySQL “IS NULL”
See the following example for how to use the MySQL “UPDATE” Clause with IS NULL; as follows:
UPDATE users SET verified = 1 WHERE status IS NULL;
It above example query will update all records from database table users using MySQL IS NULL & UPDATE Clause. Where the “status” column (field) contain a null value.
Example 4 – DELETE Clause With MySQL “IS NULL”
See the following example for how to use the MySQL “DELETE” Clause with IS NULL; as follows:
DELETE FROM users WHERE verified IS NULL;
The above query will delete all records from database table users using MySQL IS NULL & DELETE Clause. Where the “verified” column (field) contain a null value.
Example 5 – Using IS NULL On Join Conditions
See the following example for how to use the MySQL “LEFT JOIN” Clause with IS NULL; as follows:
SELECT * FROM users LEFT JOIN posts ON post.user_id = users.id WHERE user_id IS NULL;
It will fetch records from database table users using MySQL IS NULL & LEFT JOIN Clause. Where the “user_id” column (field) contain a null value.
Example 6 – Using IS NULL With Logical Operator
See the following example for how to use the MySQL “AND” operator with IS NULL; as follows:
SELECT * FROM users WHERE email_address IS NULL AND mobile_number IS NULL;
The above MySQL query get all the records from database table users using MySQL IS NULL & AND logical operator. Where the “email_address” and “mobile_number” column (field) contain a null value.
See the following example for how to use the MySQL “OR” operator with IS NULL; as follows:
SELECT * FROM users WHERE email_address IS NULL OR mobile_number IS NULL;
Where “both email address” and “mobile number” The value of one of the columns(fields) value is also found. than the above query fetch all records from database table users by using IS NULL with OR logical operator.
Conclusion
In this mysql tutorial point – we have learned how to use “IS NULL” with MySQL basic clauses, join clause & logical operators with examples.