What is a NULL Value?
A field with a NULL value is a field with no value. If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value. In MySQL, a NULL value means unknown. A NULL value is different from zero ( 0 ) or an empty string ” . A NULL value is not equal to anything, even itself. If you compare a NULL value with another NULL value or any other value, the result is NULL because the value of each NULL value is unknown.
Note: A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL
and IS NOT NULL
operators instead.
IS NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | WA1 1DP | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | S-958 22 | Sweden |
The IS NULL Operator
The IS NULL
operator is used to test for empty values (NULL values). The following SQL lists all customers with a NULL value in the “Address” field:
Example
SELECT;CustomerName, ContactName, Address<br>FROM;Customers<br>WHERE ;Address;IS ;NULL;<a target="_blank" href="https://www.futurefundamentals.com/mySQl/trymysql.asp?filename=trysql_is_null" rel="noreferrer noopener">»</a>
The IS NOT NULL Operator
The IS NOT NULL
operator is used to test for non-empty values (NOT NULL values). The following SQL lists all customers with a value in the “Address” field:
Example
SELECT ;CustomerName, ContactName, Address<br>FROM ;Customers<br>WHERE ;Address ;IS ;NOT ;NULL;<a href="https://www.futurefundamentals.com/mySQl/trymysql.asp?filename=trysql_is_not_null" target="_blank" rel="noreferrer noopener"»</a>
Using NULL values at the Command Prompt
Assume that there is a table called tcount_tbl in the TUTORIALS database and it contains two columns namely tutorial_author and tutorial_count, where a NULL tutorial_count indicates that the value is unknown.
Example
Try the following examples −
root@host# mysql -u root -p password; Enter password:******* mysql> use TUTORIALS; Database changed mysql> create table tcount_tbl -> ( -> tutorial_author varchar(40) NOT NULL, -> tutorial_count INT -> ); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO tcount_tbl -> (tutorial_author, tutorial_count) values ('mahran', 20); mysql> INSERT INTO tcount_tbl -> (tutorial_author, tutorial_count) values ('mahnaz', NULL); mysql> INSERT INTO tcount_tbl -> (tutorial_author, tutorial_count) values ('Jen', NULL); mysql> INSERT INTO tcount_tbl -> (tutorial_author, tutorial_count) values ('Gill', 20); mysql> SELECT * from tcount_tbl; +-----------------+----------------+ | tutorial_author | tutorial_count | +-----------------+----------------+ | mahran | 20 | | mahnaz | NULL | | Jen | NULL | | Gill | 20 | +-----------------+----------------+ 4 rows in set (0.00 sec) mysql>
You can see that = and != do not work with NULL values as follows −
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;
Empty set (0.01 sec)
To find the records where the tutorial_count column is or is not NULL, the queries should be written as shown in the following program.
mysql> SELECT * FROM tcount_tbl
-> WHERE tutorial_count IS NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahnaz | NULL |
| Jen | NULL |
+-----------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * from tcount_tbl
-> WHERE tutorial_count IS NOT NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
| mahran | 20 |
| Gill | 20 |
+-----------------+----------------+
2 rows 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. The following example takes the tutorial_count from outside and then compares it with the value available in the table.
Example
Copy and paste the following example as mysql_example.php −
<html> <head> <title>Handling NULL</title> </head> <body> <?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'root@123'; $dbname = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); $tutorial_count = null; if($mysqli->connect_errno ) { printf("Connect failed: %s<br />", $mysqli->connect_error); exit(); } printf('Connected successfully.<br />'); if( isset($tutorial_count )) { $sql = 'SELECT tutorial_author, tutorial_count FROM tcount_tbl WHERE tutorial_count = ' + $tutorial_count; } else { $sql = 'SELECT tutorial_author, tutorial_count FROM tcount_tbl WHERE tutorial_count IS NULL'; } $result = $mysqli->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { printf("Author: %s, Count: %d <br />", $row["tutorial_author"], $row["tutorial_count"]); } } else { printf('No record found.<br />'); } $mysqli->close(); ?> </body> </html>
Output
Access the mysql_example.php deployed on apache web server and verify the output.
Connected successfully.
No record found.