SQL AND, OR and NOT Operators with examples

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.