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)