In the last tutorial, we learned how to use logical operators AND, OR and NOT in SQL where clause. In this guide, we will learn how to combine these operators together in SQL where clause.
SQL AND & OR Operators together
Table: EMPLOYEE
+----+--------------+-----+-----------+----------+ | ID | EMP_NAME | AGE | LOCATION | SALARY | +----+--------------+-----+-----------+----------+ |123 | Daryl | 41 | Chennai | 120000 | |124 | Jon | 40 | Delhi | 80000 | |125 | Saru | 43 | Agra | 110000 | |126 | Hemant | 39 | Shimla | 110000 | |127 | Devesh | 42 | Goa | 90000 | +----+--------------+-----+-----------+----------+
The following SQL statement will fetch the details of those employees, who are more than 40 years old and their location is either “Chennai” or “Agra”.
SELECT * FROM EMPLOYEE WHERE AGE>40 AND (LOCATION='Chennai' OR LOCATION='Agra');
Result:
+----+--------------+-----+-----------+----------+ | ID | EMP_NAME | AGE | LOCATION | SALARY | +----+--------------+-----+-----------+----------+ |123 | Daryl | 41 | Chennai | 120000 | |125 | Saru | 43 | Agra | 110000 | +----+--------------+-----+-----------+----------+
SQL AND, OR and NOT Operators together
Lets see an example where we are using all three AND, OR and NOT operators in a single SQL statement.
Table: EMPLOYEE
+----+--------------+-----+-----------+----------+ | ID | EMP_NAME | AGE | LOCATION | SALARY | +----+--------------+-----+-----------+----------+ |123 | Daryl | 41 | Chennai | 120000 | |124 | Jon | 40 | Delhi | 80000 | |125 | Saru | 43 | Agra | 110000 | |126 | Hemant | 39 | Shimla | 110000 | |127 | Devesh | 42 | Goa | 90000 | +----+--------------+-----+-----------+----------+
The following SQL statement will fetch the details of those employees, who are more than 40 years old and their location is either “Chennai” or NOT “Agra”.
SELECT * FROM EMPLOYEE WHERE AGE>40 AND (LOCATION='Chennai' OR NOT LOCATION='Agra');
Result:
+----+--------------+-----+-----------+----------+ | ID | EMP_NAME | AGE | LOCATION | SALARY | +----+--------------+-----+-----------+----------+ |123 | Daryl | 41 | Chennai | 120000 | |127 | Devesh | 42 | Goa | 90000 | +----+--------------+-----+-----------+----------+