MySQL is Clause


MySQL is clause is used in conjuction with Where clause to check the NULL values in any column. The general syntax of MySQL is clause is as follows
Syntax

SELECT column1, column2, .......
FROM tableName
WHERE column1 is NULL

sample Database to show the working of IS clause

+-------+---------+--------+-------+-------+---------+--------+
| admno | name    | stream | marks | grade | fees    | gender |
+-------+---------+--------+-------+-------+---------+--------+
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |
| 112   | vipul   | comm   |  NULL | A     | 1650.75 | M      |
+-------+---------+--------+-------+-------+---------+--------+

NULL values -means the absence of a value, can’t be checked using the normal comparison operator. The example demonstrates the same

mysql> select * from student
-> where marks = NULL;
Empty set (0.00 sec)

Now display all the records where marks of the student are not present ie NULL

mysql> select * from student
    -> where marks IS NULL;
+-------+-------+--------+-------+-------+---------+--------+
| admno | name  | stream | marks | grade | fees    | gender |
+-------+-------+--------+-------+-------+---------+--------+
| 112   | vipul | comm   |  NULL | A     | 1650.75 | M      |
+-------+-------+--------+-------+-------+---------+--------+

mysql> select * from student
    -> where marks IS NOT NULL;
+-------+---------+--------+-------+-------+---------+--------+
| admno | name    | stream | marks | grade | fees    | gender |
+-------+---------+--------+-------+-------+---------+--------+
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |
+-------+---------+--------+-------+-------+---------+--------+
11 rows in set (0.00 sec)

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.