SQL AND, OR and NOT

In this tutorial, we will learn how to use AND, OR and NOT Operator with WHERE clause to select record based on one or more conditions.

SQL AND, OR and NOT Operators

AND, OR and NOT are the boolean operators which are used to define the multiple conditions in the WHERE clause.

AND and OR Operators are used to filtering the records based on one or more conditions.

AND Operator:

It returns a record if all the condition which is separated by AND operator is satisfied or true.

SELECT column1,column2.....columnN
FROM tableName
WHERE [CONDITION1] AND [CONDITION2] AND...[CONDITION-N]

OR Operator:

It returns a record if atleast one of the condition which is separated by OR operator is satisfied or true.

SELECT column1,column2.....columnN
FROM tableName
WHERE [CONDITION1] OR [CONDITION2] OR...[CONDITION-N]

NOT Operator:

It returns a record if the condition is false or not true.

SELECT column1,column2.....columnN
FROM tableName
WHERE NOT [CONDITION]

Example:

Let us consider this table "Employee" for records.

Table Name: Employee

Example of AND Operator

For finding an employee whose City is "Delhi" and Country is "India", a query will be:-

SELECT * FROM Employee
WHERE City='Delhi' AND Country='India';

Example of OR Operator

For finding an employee whose City is "Delhi" OR Country is "India", a query will be:-

SELECT * FROM Employee
WHERE City='Delhi' OR Country='India'

Example of NOT Operator

For finding employee whose City is NOT "Delhi", query will be:-

SELECT * FROM Employee
WHERE NOT City='Delhi'

Nested Condition AND, OR and NOT Operator

For finding employee whose Salary greater then 15000 and Salary less than 28000 or Gender not equals to "female", query will be:-

SELECT * FROM Employee
WHERE (Salary>15000 AND Salary<28000)
OR NOT Gender='female'