SQL AND, OR and NOT Operators
There are three Logical Operators namely AND, OR, and NOT in SQL Operators. The AND and OR operators are used to filter records based on more than one condition, and the NOT operator displays a record if the condition(s) is NOT TRUE
AND Operator Syntax
SELECT column_name1, column_name2,...
FROM table_name
WHERE [condition1] AND [condition2] AND [condition3].....;
OR Operator Syntax
SELECT column_name1, column_name2,...
FROM table_name
WHERE [condition1] OR [condition2] OR [condition3].....;
NOT Operator Syntax
SELECT column_name1, column_name2,...
FROM table_name
WHERE NOT [condition1];
Employee Table
A sample of EMPLOYEE records table & the name of table Employee. The column(s) of the Employee table [ID, EMPLOYEE_NAME, EMPLOYEE_EMAIL, ADDRESS, CITY, and COUNTRY]:
| ID |
EMPLOYEE_NAME |
EMPLOYEE_EMAIL |
ADDRESS |
CITY |
COUNTRY |
| 1 |
Mark J. |
mark@gmail.com |
House 53, Sq. |
London |
U.K. |
| 2 |
Lack Josh |
lackjosh@gmail.com |
2745 road mark |
Delhi |
India |
| 3 |
Kim Juhu |
kimjuhu@gmail.com |
Street 587327, 34 House |
Seoul |
South Korea |
| 4 |
Jack Jenifer |
jenifer@gmail.com |
464673 Mataderos |
Mexico D.F. |
Mexico |
| 5 |
McKinley |
mckinley@gmail.com |
54, Road Washington |
Washington |
U.S. |
| 6 |
Alena |
aln@gmail.com |
House No. 6563 |
Tokyo Japan |
Japan |
| 7 |
Mack Marina |
mmk@gmail.com |
43 Road, NH |
San Francisco |
U.S. |
| 8 |
Dipak Jha |
jha@gmail.com |
Mahanager, 26 Road |
Lucknow |
India |
AND, OR and NOT Operator Example
AND Operator
SELECT * FROM Employee
WHERE Country = "India" AND City = "Delhi";
Result
| ID |
EmployeeName |
EmployeeEmail |
Address |
City |
Country |
| 2 |
Lack Josh |
lackjosh@gmail.com |
2745 road mark |
Delhi |
India |
OR Operator
SELECT * FROM Employee
WHERE Country = "India" OR Country = "U.S.";
Result
| ID |
EmployeeName |
EmployeeEmail |
Address |
City |
Country |
| 2 |
Lack Josh |
lackjosh@gmail.com |
2745 road mark |
Delhi |
India |
| 5 |
McKinley |
mckinley@gmail.com |
54, Road Washington |
Washington |
U.S. |
| 7 |
Mack Marina |
mmk@gmail.com |
43 Road, NH |
San Francisco |
U.S. |
| 8 |
Dipak Jha |
jha@gmail.com |
Mahanager, 26 Road |
Lucknow |
India |
NOT Operator Syntax
SELECT * FROM Employee
WHERE NOT Country = "India";
Result
| ID |
EmployeeName |
EmployeeEmail |
Address |
City |
Country |
| 1 |
Mark J. |
mark@gmail.com |
House 53, Sq. |
London |
U.K. |
| 3 |
Kim Juhu |
kimjuhu@gmail.com |
Street 587327, 34 House |
Seoul |
South Korea |
| 4 |
Jack Jenifer |
jenifer@gmail.com |
464673 Mataderos |
Mexico D.F. |
Mexico |
| 5 |
McKinley |
mckinley@gmail.com |
54, Road Washington |
Washington |
U.S. |
| 6 |
Alena |
aln@gmail.com |
House No. 6563 |
Tokyo Japan |
Japan |
| 7 |
Mack Marina |
mmk@gmail.com |
43 Road, NH |
San Francisco |
U.S. |
Combining AND, OR and NOT
AND & OR Example
SELECT * FROM Employee
WHERE Country = "India" AND (City = "Delhi" OR City = "Lucknow");
AND & NOT Example
SELECT * FROM Employee
WHERE NOT Country = "India" AND NOT Country = "U.S.";