For NULL, there are two properties in MySQL −
- IS NULL
- IS NOT NULL.
To understand the above concept, let us create a table. The query to create a table is as follows −
mysql> create table NULL_Demo -> ( -> UserId int, -> UserName varchar(20), -> UserAddress varchar(20) -> ); Query OK, 0 rows affected (0.67 sec)Example
Insert some records in the table using insert command. The query is as follows −
mysql> insert into NULL_Demo values(12345,'John',NULL); Query OK, 1 row affected (0.16 sec) mysql> insert into NULL_Demo values(2345,'Carol','UK'); Query OK, 1 row affected (0.35 sec) mysql> insert into NULL_Demo values(233444,NULL,NULL); Query OK, 1 row affected (0.60 sec) mysql> insert into NULL_Demo values(NULL,NULL,NULL); Query OK, 1 row affected (0.27 sec)Display all records from the table using select statement. The query is as follows −
mysql> select *from NULL_Demo;Output
+--------+----------+-------------+ | UserId | UserName | UserAddress | +--------+----------+-------------+ | 12345 | John | NULL | | 2345 | Carol | UK | | 233444 | NULL | NULL | | NULL | NULL | NULL | +--------+----------+-------------+ 4 rows in set (0.00 sec)Here are the queries that works for IS NULL and IS NOT NULL property.
Case 1 − IS NOT NULL
The query is as follows −
mysql> select *from NULL_Demo where UserId = 2345 AND UserName = 'Carol' AND UserAddress IS NOT NULL;The following is the output displaying the NOT NULL record according to the condition set inthe above query −
Case 2 − NOT NULL
The query is as follows −
mysql> select *from NULL_Demo where UserName = 'John' AND UserAddress IS NULL;The following is the output displaying the NULL record according to the condition set in the above query −
+--------+----------+-------------+ | UserId | UserName | UserAddress | +--------+----------+-------------+ | 12345 | John | NULL | +--------+----------+-------------+ 1 row in set (0.00 sec)
Updated on 26-Jun-2020 10:14:39
- Related Questions & Answers
- Difference Between MySql <> NULL and IS NOT NULL?
- Display only NOT NULL values from a column with NULL and NOT NULL records in MySQL
- In MySQL what is the difference between != NULL and IS NOT NULL?
- Display the result with not null value first and then with null value in MySQL
- Insert default into not null column if value is null in MySQL?
- What is the benefit of MySQL ‘IS NULL’ and ‘IS NOT NULL’?
- Conditional NOT NULL case MySQL?
- Enum with NOT NULL in a MySQL field?
- Display NULL and NOT NULL records except a single specific value in MySQL
- What is MySQL NOT NULL constraint and how can we declare a field NOT NULL while creating a table?
- Check for NULL or NOT NULL values in a column in MySQL
- Sum if all rows are not null else return null in MySQL?
- CASE WHEN column1 IS NULL THEN NULL ELSE column2 END with MySQL
- MySQL syntax not evaluating with not equal operator in presence of null?
- Return null for date_format when input is null in MySQL?
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.
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 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 −
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.