MySQL

295 guides

keyboard_arrow_down

Other math topics

Dagster

Pandas

NumPy

Matplotlib

PySpark

MySQL

chevron_leftOperators

AND BETWEEN BINARY CASE DIV IN IS IS NOT IS NOT NULL IS NULL LIKE NOT NOT BETWEEN NOT IN NOT LIKE NOT REGEXP OR REGEX RLIKE SOUNDS LIKE XOR

check_circle

Mark as learned

thumb_up

thumb_down

chat_bubble_outline

Comment

auto_stories Bi-column layout

settings

MySQL | NOT

schedule Aug 11, 2023

Last updated

local_offer

MySQL

Syntax


        
        
            
                
                
                    SELECT column_name(s)
FROM table_name
WHERE NOT condition;

Examples

Consider the following table about some students:

student_id	fname	lname	day_enrolled	age	username
1	Sky	Towner	2015-12-03	17	stowner1
2	Ben	Davis	2016-04-20	19	bdavis2
3	Travis	Apple	2018-08-14	18	tapple3
4	Arthur	David	2016-04-01	16	adavid4
5	Benjamin	Town	2014-01-01	17	btown5

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

Basic usage

To return students who are not 17 years old:


        
        
            
                
                
                    SELECT * 
FROM studentsWHERE NOT age = 17;
                
            
            +------------+--------+-------+--------------+------+----------+
| student_id | fname  | lname | day_enrolled | age  | username |
+------------+--------+-------+--------------+------+----------+
|          2 | Ben    | Davis | 2016-04-20   |   19 | bdavis2  |
|          3 | Travis | Apple | 2018-08-14   |   18 | tapple3  |
|          4 | Arthur | David | 2016-04-01   |   16 | adavid4  |
+------------+--------+-------+--------------+------+----------+

NOT can be combined with other operators such as AND:


        
        
            
                
                
                    SELECT * 
FROM students
WHERE NOT (age = 17 AND fname = 'Sky');
                
            
            +------------+----------+-------+--------------+------+----------+
| student_id | fname    | lname | day_enrolled | age  | username |
+------------+----------+-------+--------------+------+----------+
|          2 | Ben      | Davis | 2016-04-20   |   19 | bdavis2  |
|          3 | Travis   | Apple | 2018-08-14   |   18 | tapple3  |
|          4 | Arthur   | David | 2016-04-01   |   16 | adavid4  |
|          5 | Benjamin | Town  | 2014-01-01   |   17 | btown5   |
+------------+----------+-------+--------------+------+----------+

We return all students except those that have age=17 and have fname='Sky'.

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/logical-operators.html#operator_not

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!