MySQL NOT Operator

By /
NOT Operator

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.

Students Table
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.

Not Operator Example 1 2

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:

Not Operator Example 1 1

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 Example 2

Conclusion

The MySQL 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.

References