Syntax error How to check if data is NULL in MySQL?

How to check if data is NULL in MySQL?

You can use IF() to check if data is NULL.  Let us first create a table −

mysql> create table DemoTable
(
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name varchar(200),
   Age int
);
Query OK, 0 rows affected (0.44 sec)

Insert records in the table using insert command −

mysql> insert into DemoTable(Name,Age) values('John',23);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(Name,Age) values('Sam',null);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable(Name,Age) values('Mike',23);
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(Name,Age) values('David',21);
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable(Name,Age) values('Carol',null);
Query OK, 1 row affected (0.13 sec)

Display records from the table using select command −

mysql> select *from DemoTable;

This will produce the following output −

+----+-------+------+
| Id | Name  | Age  |
+----+-------+------+
| 1  | John  | 23   |
| 2  | Sam   | NULL |
| 3  | Mike  | 23   |
| 4  | David | 21   |
| 5  | Carol | NULL |
+----+-------+------+
5 rows in set (0.00 sec)

Here is the query to check if data is NULL or not. This adds a message wherever NULL in the record is visible −

mysql> select if(Age IS NULL,'Age is missing',Age) from DemoTable;

This will produce the following output −

+--------------------------------------+
| if(Age IS NULL,'Age is missing',Age) |
+--------------------------------------+
| 23                                   |
| Age is missing                       |
| 23                                   |
| 21                                   |
| Age is missing                       |
+--------------------------------------+
5 rows in set (0.00 sec)
Updated on: 2019-07-30T22:30:25+05:30

429 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements