Apa itu null di mysql?

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 −

+--------+----------+-------------+
| UserId | UserName | UserAddress |
+--------+----------+-------------+
|   2345 |    Carol |          UK |
+--------+----------+-------------+
1 row in set (0.00 sec)

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 −

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.

Apa itu NULL di sql?

Apa yang dimaksud dengan nilai NULL?

Kenapa data bisa NULL?