MySQL AND, OR and NOT Operators

The where clause is used to filter the table content based on certain conditions.
We can use AND, OR and NOT operators in where clause to perform multiple condition checks based on our requirement.

AND operator - Returns the records that matches all the conditions seperated by AND operator.
OR operator - Returns all the records that matches atleast one condition seperated by OR operator.
NOT operator - Returns all the records that does not match the condition.

For this tutorial we will use the following employee table.

AND operator

AND operator will returns the records that matches all the conditions seperated by AND operator.
Syntax for AND operator

select column_1, column_2, ... from table_name where condition_1 and condition_2;

Example for AND operator

select * from employee where role='Manager' and location='India';

The above query will return the records with role of Manager and location as India.

Output

OR Operator

OR operator returns all the records that matches atleast one condition seperated by OR operator.

Syntax for OR operator

select column_1, column_2, ... from table_name where condition_1 or condition_2;

Example for OR operator

select * from employee where location='India' or location='USA';

The above query will return the records whose location is India or USA.

Output

NOT operator

NOT operator returns all the records that does not match the condition.
Syntax for NOT operator

select column_1, column_2, ... from table_name where NOT condition_1;

Example for NOT operator

select * from employee where NOT location='India';

The above query will return the records whose location is not India.

Output

It is also possible to combine AND, OR and NOT operators in a single query.

Example 1

select * from employee where role='Senior Developer' AND (location ='India' OR location='USA')

The above query will retrieve the records of employee's whose role is Senior Developer and their location is in India or USA.

Output

Example 2

select * from employee where role='Senior Developer' AND NOT  location='India';

The above query will find the records of the employee's whose role is Senior Developer and their location is not India.

Output