How to use NULL in MySQL SELECT statement?

In MySQL, the length of NULL is 0. Here, we will see how NULL can be used with SELECT statement. Let us create a table with the help of CREATE command −

Creating a table −

mysql> CREATE table NullWIthSelect
-> (
-> Name varchar(100)
-> );
Query OK, 0 rows affected (0.62 sec)

Above, I have created a table successfully. Now I will insert some records with the help of INSERT command −

Inserting records −

mysql> INSERT into NullWIthSelect values('John');
Query OK, 1 row affected (0.16 sec)

mysql> INSERT into NullWIthSelect values('Bob');
Query OK, 1 row affected (0.12 sec)

mysql> INSERT into NullWIthSelect values();
Query OK, 1 row affected (0.18 sec)

mysql> INSERT into NullWIthSelect values('Carol');
Query OK, 1 row affected (0.15 sec)

mysql> INSERT into NullWIthSelect values('');
Query OK, 1 row affected (0.16 sec)

mysql> INSERT into NullWIthSelect values('David');
Query OK, 1 row affected (0.19 sec)

mysql> INSERT into NullWIthSelect values();
Query OK, 1 row affected (0.08 sec)

Above, I have inserted 7 records in which one record has empty value and two have null values. Rest of them has some values.

To display all the records, we can use the SELECT command −

mysql> SELECT * from NullWIthSelect;

The following is the output

+-------+
| Name  |
+-------+
| John  |
| Bob   |
| NULL  |
| Carol |
|       |
| David |
| NULL  |
+-------+
7 rows in set (0.00 sec)

Now, we can use NULL with SELECT statement as shown below.

Firstly, let us see the syntax −

SELECT * from yourTableNamet where column_name is NULL;

Applying the above query to know which column value is null. The query is as follows −

mysql> SELECT * from NullWIthSelect where Name is NULL;

The following is the output −

+------+
| Name |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.00 sec)

Now, we can get the length of NULL value that is 0. The query is as follows −

mysql> SELECT count(Name) from NullWIthSelect where Name is NULL;

The following is the output −

+-------------+
| count(Name) |
+-------------+
| 0           |
+-------------+
1 row in set (0.04 sec)