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.

empno name age role location
001 Andrew 30 Manager India
002 Beslin 28 Business Analyst India
003 Joanna 23 Senior Developer USA

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

empno name age role location
001 Andrew 30 Manager India

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

empno name age role location
001 Andrew 30 Manager India
002 Beslin 28 Business Analyst India
003 Joanna 23 Senior Developer USA

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

empno name age role location
003 Joanna 23 Senior Developer USA

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

empno name age role location
003 Joanna 23 Senior Developer USA

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

empno name age role location
003 Joanna 23 Senior Developer USA

Most Read