Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
849 Views
