In the last tutorial, we learned how to use where clause in an SQL statement. In this guide, we will learn about logical operators AND and OR which we can use in where clause to join multiple conditions and NOT operator which is used to negate a condition.
SQL AND Operator
When multiple conditions are joined using AND operator, only those rows will be fetched from the database which meets all the conditions.
AND Operator Syntax
SELECT column_name1, column_name2, ... FROM table_name WHERE condition_1 AND condition_2 ...;
SQL AND Example
Table: STUDENT
+----+--------------+-----+-----------+----------+ | ID | STU_NAME | AGE | ADDRESS | BRANCH | +----+--------------+-----+-----------+----------+ | 11 | Carl | 22 | Agra | ECE | | 12 | Rick | 23 | Delhi | CSE | | 13 | Maggie | 23 | Gurgaon | CL | | 14 | Carol | 24 | Noida | ME | | 15 | Negan | 26 | Delhi | EE | +----+--------------+-----+-----------+----------+
Lets write an SQL statements to fetch the name of the students where student age is “greater than 23” and address is “Delhi”
Query:
SELECT STU_NAME FROM STUDENT WHERE AGE > 23 AND ADDRESS = 'Delhi';
Result:
+----------+ | STU_NAME | +----------+ | Negan | +----------+
SQL OR Operator
When multiple conditions are joined using OR operator, all those rows will be fetched from the database which meet any of the given conditions.
OR Operator Syntax
SELECT column_name1, column_name2, ... FROM table_name WHERE condition_1 OR condition_2 ...;
SQL OR Example
Table: EMPLOYEE
+----+--------------+-----+-----------+----------+ | ID | EMP_NAME | AGE | ADDRESS | SALARY | +----+--------------+-----+-----------+----------+ | 90 | David | 30 | Agra | 10000 | | 91 | Steve | 31 | Delhi | 9000 | | 92 | Ajeet | 29 | Gurgaon | 11000 | | 93 | Rahul | 33 | Noida | 19000 | | 94 | Pappu | 35 | Agra | 9900 | +----+--------------+-----+-----------+----------+
Lets write an SQL statements to fetch the details of the employees, where either employee age is “greater than 30” or address is “Agra”.
Query:
SELECT * FROM EMPLOYEE WHERE AGE > 30 OR ADDRESS = 'Agra';
Result:
+----+--------------+-----+-----------+----------+ | ID | EMP_NAME | AGE | ADDRESS | SALARY | +----+--------------+-----+-----------+----------+ | 90 | David | 30 | Agra | 10000 | | 91 | Steve | 31 | Delhi | 9000 | | 93 | Rahul | 33 | Noida | 19000 | | 94 | Pappu | 35 | Delhi | 9900 | +----+--------------+-----+-----------+----------+
Explanation:
Row number 1 is in the output because it meets the second condition (ADDRESS = ‘Agra’).
Row number 2nd and 4th are in the output because they meet the first condition (AGE > 30).
Row number 5th is in the output because it meets both the conditions.
SQL NOT Operator
When a NOT operator is used with a condition, only those rows will fetched from database which do not meet the given condition.
NOT Operator Syntax
SELECT column_name1, column_name2, ... FROM table_name WHERE NOT condition;
SQL NOT Example
Table: ORDER
+----+--------------+-----+-----------+----------+ | ID | CUSTOMER_NAME| AGE | ADDRESS | AMOUNT | +----+--------------+-----+-----------+----------+ | 70 | Ram | 30 | Sector121 | 11000 | | 71 | Tim | 31 | Sector122 | 900 | | 72 | Tom | 29 | Sector62 | 1000 | | 73 | Lisa | 33 | Sector61 | 1000 | | 74 | Dexter | 35 | Sector121 | 500 | +----+--------------+-----+-----------+----------+
The following SQL statement selects all the order id and customer name where address is NOT Sector121.
Query:
SELECT ID, CUSTOMER_NAME FROM ORDER WHERE NOT ADDRESS = 'Sector121';
Result:
+----+--------------+ | ID | CUSTOMER_NAME| +----+--------------+ | 71 | Tim | | 72 | Tom | | 73 | Lisa | +----+--------------+
SQL AND, OR and NOT Operators together
We can use AND, OR and NOT logical operators together in where clause. Refer this tutorial: SQL AND, OR and NOT operators together.