MySQL IS NULL Condition

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.

Be First to Comment

Leave a Reply

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