We have seen the SQL SELECT command along with the WHERE clause to fetch data from a MySQL table, but when we try to give a condition, which compares the field or the column value to NULL, it does not work properly.
To handle such a situation, MySQL provides three operators −

• IS NULL − This operator returns true, if the column value is NULL.
• IS NOT NULL − This operator returns true, if the column value is not NULL.
• − This operator compares values, which (unlike the = operator) is true even for two NULL values.
  IS NULL − This operator returns true, if the column value is NULL.
  IS NOT NULL − This operator returns true, if the column value is not NULL.
  − This operator compares values, which (unlike the = operator) is true even for two NULL values.
  The conditions involving NULL are special. You cannot use = NULL or != NULL to look for NULL values in columns. Such comparisons always fail because it is impossible to tell whether they are true or not. Sometimes, even NULL = NULL fails.
  To look for columns that are or are not NULL, use IS NULL or IS NOT NULL.

  Using NULL values at the Command Prompt

  Assume that there is a table called employees_sallary in the mydb database and it contains two columns namely employee_name and employee_sallary, where a NULL employee_count indicates that the value is unknown.

  Example

  Try the following examples −

  root@host# mysql -u root -p password;
  Enter password:
  mysql> use mydb;
  Database changed
  mysql> insert into employees_sallary(id,employee_name,employee_sallary) values(1,'Nancy',20000);
  Query OK, 1 row affected (0.03 sec)
  MariaDB [mydb]> insert into employees_sallary(id,employee_name,employee_sallary) values(2,'Cencini',NULL);
  Query OK, 1 row affected (0.05 sec)
  MariaDB [mydb]> insert into employees_sallary(id,employee_name,employee_sallary) values(3,'Mariya',NULL);
  Query OK, 1 row affected (0.03 sec)
  mysql> SELECT * from employees_sallary;
  +----+---------------+------------------+
  | id | employee_name | employee_sallary |
  +----+---------------+------------------+
  |  1 | Nancy         |            20000 |
  |  2 | Cencini       |             NULL |
  |  3 | Mariya        |             NULL |
  +----+---------------+------------------+
  3 rows in set (0.00 sec)
  mysql>

  You can see that = and != do not work with NULL values as follows −

  mysql> SELECT * from employees_sallary WHERE employee_sallary = NULL;
  Empty set (0.00 sec)
  mysql> SELECT * from employees_sallary WHERE employee_sallary != NULL;
  Empty set (0.00 sec)

  To find the records where the employee_sallary column is or is not NULL, the queries should be written as shown in the following program.

  mysql> SELECT * from employees_sallary WHERE employee_sallary IS NULL;
  +----+---------------+------------------+
  | id | employee_name | employee_sallary |
  +----+---------------+------------------+
  |  2 | Cencini       |             NULL |
  |  3 | Mariya        |             NULL |
  +----+---------------+------------------+
  2 rows in set (0.00 sec)
  mysql> SELECT * from employees_sallary WHERE employee_sallary IS NOT NULL;
  +----+---------------+------------------+
  | id | employee_name | employee_sallary |
  +----+---------------+------------------+
  |  1 | Nancy         |            20000 |
  +----+---------------+------------------+
  1 row in set (0.00 sec)

  Handling NULL Values in a PHP Script

  You can use the if...else condition to prepare a query based on the NULL value.

  Example

  <title>NULL and NOT NULL in mysql </title>
  <?php
  $servername = "localhost";
  $username = "root";
  $password = "";
  $dbname = "mydb";
  // Create connection
  $conn = mysqli_connect($servername, $username, $password, $dbname);
  // Check connection
  if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
  }
  $sql = "SELECT * from employees_sallary WHERE employee_sallary IS NULL ";
  $result = mysqli_query($conn, $sql);
  echo "SELECT * from employees_sallary WHERE employee_sallary IS NULL   <br> <hr>";
  if (mysqli_num_rows($result) > 0) {
  while($row = mysqli_fetch_array($result)) {
  echo
  "Employee Id : {$row['id']} <br> ".
  "Employee Name : {$row['employee_name']} <br> ".
  "Employee Sallary: {$row['employee_sallary']} <br> ".
  "--------------------------------<br>";
  } }
  else {
  echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  }
  echo "Fetched data successfully";
  ?>