AND, OR & NOT operator

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

ü  The AND operator displays a record if all the conditions separated by AND is TRUE.

ü  The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

NOT Syntax

SELECT column1, column2, ...
FROM table_name

WHERE condition1 AND condition2 AND condition3 ...; 

Example of AND,OR & NOT operator

 Consider the following Student table.

s_id	s_Name	age	salary
101	Dashzin	15	5000
102	Ravi	17	4000
103	Abhinav	17	7000
104	Ankit	22	4000

Example of AND operator
AND operator is used to set multiple conditions with Where clause.

SELECT * from student WHERE salary < 5000 AND age >17;

s_id	s_Name	age	salary
104	Ankit	22	4000

Example of OR operator
AND operator is used to set multiple conditions with Where clause.

SELECT * from student WHERE salary < 5000 OR age >17;

s_id	s_Name	age	salary
102	Ravi	18	4000
104	Ankit	22	4000

Example of NOT operator
AND operator is used to set multiple conditions with Where clause.

SELECT * from student WHERE salary NOT 5000;

s_id	s_Name	age	salary
102	Ravi	17	4000
103	Abhinav	17	7000
104	Ankit	22	4000