In this quick tutorial, we’ll take a look at the MySQL NOT operator. Suppose you are a teacher and you want a list of students who were not present for all 100 days in a term. In other words, the condition of students present for 100 days should NOT be satisfied, and the resulting output should be of all entries not satisfying the condition.
MySQL provides you with a logical operator called the NOT operator to deal with such situations. The NOT operator should always be before a condition like a
WHERE clause or other condition-based clauses. It returns all the records which do not satisfy the given condition.
Syntax of the MySQL NOT Operator
NOT Boolean_expressionCode language: SQL (Structured Query Language) (sql)
Examples of the MySQL OR Operator
Let us take a look at a few examples to demonstrate the use of the
NOT Operator. Consider the following Students table.
1. Finding Students NOT Present for 100 Days
Let us try to find all the students who were not present for all 100 days of the term. First, let us write a query which shows the students present for all 100 days.
SELECT * FROM Students WHERE DaysPresent=100;Code language: SQL (Structured Query Language) (sql)
And it will return the records of students who were present for all the 100 days of the term.
Now, finding students not present for 100 days is easy. You just need to write
NOT before the condition as follows:
SELECT * FROM Students WHERE NOT DaysPresent=100;Code language: SQL (Structured Query Language) (sql)
And you will get the following output:
As you can see, none of the above students has 100 as a value in the DaysPresent column.
2. Finding Students Not From a Specific City
Let us take a look at a complex example. How about finding students who are not from Pune and are present for more than 85 days.
Oh wait, looks like you need two logical operators here. One is
NOT and the other is the
AND operator as both conditions need to get satisfied. The query for the above task is as follows,
SELECT * FROM Students WHERE DaysPresent>85 AND NOT City=’Pune’;Code language: SQL (Structured Query Language) (sql)
We get the following output,
NOT Operator can be used within the
WHERE clause or with other clauses like IN, where we use it as the NOT IN operator.
NOT can be very useful, and so I would encourage you to check out the references for further information.
- JournalDev article on Logical Operators.
- MySQL official documentation on Logical Operators.