
        
        
            
                
                
                    SELECT value IS NOT NULL;
SELECT column IS NOT NULL
FROM table_name;

Examples

Consider the following table about students' extracurricular activities:

student_id	club	date_entered
1	Football	2016-02-13
2	Boxing	2016-05-25
3	Apple	2018-08-17
4	Fishing	2017-01-01
5	NULL	NULL

The above sample table can be created using the code here.

Basic usage

To check whether club IS NOT NULL for students:


        
        
            
                
                
                    SELECT student_id, club IS NOT NULL 
FROM extracurricular;
                
            
            +------------+------------------+
| student_id | club IS NOT NULL |
+------------+------------------+
|          1 |                1 |
|          2 |                1 |
|          3 |                1 |
|          4 |                1 |
|          5 |                0 |
+------------+------------------+

0 is returned for student_id=5 as club is NULL.

Use with WHERE clause

The IS NOT NULL operator can be used in conjunction with WHERE to filter out missing values:


        
        
            
                
                
                    SELECT * 
FROM extracurricular 
WHERE club IS NOT NULL;
                
            
            +------------+----------+--------------+
| student_id | club     | date_entered |
+------------+----------+--------------+
|          1 | Football | 2016-02-13   |
|          2 | Boxing   | 2016-05-25   |
|          3 | Chess    | 2018-08-17   |
|          4 | Fishing  | 2017-01-01   |
+------------+----------+--------------+

We can see the record for student_id=5 has been filtered out.

Published by Arthur Yanagisawa

Edited by 0 others

Did you find this page useful?

thumb_up

thumb_down

Comment

Citation

Ask a question or leave a feedback...

Official MySQL Documentation

https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_is-not-null

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!