Explanation of the NULL value in MySQL and the MySQL NULL Value

Explanation of the NULL value in MySQL and the MySQL NULL Value

We can see that the SQL SELECT command of the WHERE clause is used to obtain data from the MySQL table. However, when the condition we try to compare the value of a field or column is NULL, it cannot work normally.

To handle this situation, MySQL provides three operators

1. Is null: This operator returns true when the column value is null.
2. Is not null: returns true if the column value is not null.
3. <=> The operator comparison value (different from the = Operator) is true, even if two NULL values

NULL conditions are special. Cannot Use = NULL or! = NULL: the column that finds the NULL value. This comparison always tells them whether it is a real failure because it is impossible. Even if it is NULL = NULL, failure.

If you want to find a column that IS or is not null, use is null or is not null.
Use the NULL value at the command prompt:

Assume that a table named tcount_tbl contains two columns: stutorial_author and tutorial_count. The value indicated by NULL is unknown.
Example:

Try the following example:

root@host# mysql -u root -p password;Enter password:*******mysql> use TUTORIALS;Database changedmysql> 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>

We can see the = and! = Do not use NULL values, as shown below:

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 record where the tutorial_count column is or is not NULL, the query should be written as follows:

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)

PHP scripts process NULL values:

You can use the IF... ELSE condition to perform the NULL value query.
Example:

The following example tutorial_count is external and can be compared with values in the table.

<?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){ die('Could not connect: ' . mysql_error());}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 $tutorial_count';}mysql_select_db('TUTORIALS');$retval = mysql_query( $sql, $conn );if(! $retval ){ die('Could not get data: ' . mysql_error());}while($row = mysql_fetch_array($retval, MYSQL_ASSOC)){  echo "Author:{$row['tutorial_author']} <br> ".     "Count: {$row['tutorial_count']} <br> ".     "--------------------------------<br>";} echo "Fetched data successfully\n";mysql_close($conn);?>