MySQL IS NULL with EXAMPLES

MySQL where is null query; Through this tutorial, you will learn how to use MySQL WHERE IS NULL. In this tutorial, we will also learn about MySQL WHERE “IS NULL” syntax and examples. We will explain more things about “IS NULL” & also how you can use this with a different MySQL clauses & logical operators.

Whenever we obtain data from a table of MYSQL database. To fetch data from the database table, we require the conditions in our MYSQL query in order of our requirement. As we have done in the query of MYSQL, AND, OR, NOT, etc. We can do this with IS NULL to fetch data from the MySQL database table.

In our previous tutorial – you have learned about logical operators like AND, OR, NOT and MySQL IS NOT NULL with syntax and examples. IF you want to know more about MySQL logical operators and IS NOT NUL click here.

MySQL IS NULL with EXAMPLES

• Syntax
• Params(Parameters)
• Ex:-Using SELECT Clause With MySQL IS NULL
• Ex:-Using “INSERT INTO” Clause With MySQL IS NULL
• Ex:-UPDATE Clause With MySQL IS NULL
• Ex:-DELETE Clause With MySQL IS NULL
• Ex:-Using IS NULL With AND Logical Operator
• Ex:-Using IS NULL With OR Logical Operator

MySQL WHERE IS NULL

“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. We can use “IS NULL” with MySQL clauses with examples. MySQL clauses like INSERT INTO, SELECT, UPDATE, DELETE etc. And Also we can use this with logical operator like (AND, OR) etc.

Syntax

MySQL syntax is “IS NULL” given below : –

table_column_name IS NULL

OR

exp IS NULL

Params

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.

Using SELECT Clause With MySQL IS NULL

Now, We will describe how to use the MySQL SELECT Clause with IS NULL.

 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.

Using “INSERT INTO” Clause MySQL “IS NULL”

Next, Example of “INSERT INTO” with “IS NOT NULL”, we will show you how to use the MySQL “INSERT INTO” Clause with IS NULL.

 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.

UPDATE Clause With MySQL “IS NULL”

Next example of “UPDATE” with “IS NULL”, we will describe how to use the MySQL “UPDATE” Clause with IS NULL.

 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.

DELETE Clause With MySQL “IS NULL”

In the below example of “DELETE” with “IS NULL”, we will describe how to use the MySQL “DELETE” Clause with IS NULL.

 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.

Using IS NULL On Join Conditions

In below example, We will describe how to use the MySQL “LEFT JOIN” Clause with IS NULL.

 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.

Using IS NULL With AND Logical Operator

Example of “AND” logical operator with “IS NULL”, we will describe how to use the MySQL “AND” operator with IS NULL.

 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.

Using IS NOT NULL With OR Logical Operator

Example of “OR” logical operator with “IS NULL”, we will describe how to use the MySQL “OR” operator with IS NULL.

 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.

Recommended MySQL Tutorials