Using SQL AND, OR, NOT operator with WHERE clause

AND, OR, NOT operators are also can be used with WHERE clause to specify conditions.

+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
+----+--------+---------+------+-----------+

SQL WHERE clause with AND operator

If we want to use more than one condition then we use AND operator with WHERE clause. If all these conditions are true only then we will get the result set if even one of these conditions is false , we will not get the result set.

SQL WHERE clause syntax and example with AND operator-

Suppose we want to customers from the CUSTOMER table who are from INDIA and age is less than 40.\

SELECT * FROM CUSTOMER WHERE COUNTRY = 'INDIA' AND AGE < 40;

+----+--------+-------+------+---------+
| ID | FNAME  | LNAME | AGE  | COUNTRY |
+----+--------+-------+------+---------+
|  1 | AKSHAY | KUMAR |   12 | INDIA   |
+----+--------+-------+------+---------+
1 row in set (0.00 sec)

SQL WHERE clause with OR operator

If we want to use more than one condition and want at least one of these conditions to be true, we should use OR operator. We will get the result if at least one of the given conditions separated by OR is true.

SQL WHERE clause syntax and example with OR operator-

Suppose we want to get CUSTOMERS wither from INDIA or AUSTRALIE, we will use the following syntax-

SELECT * FROM CUSTOMER WHERE COUNTRY = 'INDIA' OR COUNTRY = 'AUSTRALIA';

+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
+----+--------+---------+------+-----------+
3 rows in set (0.00 sec)

We have got all the customers with COUNTRY INDIA and AUSTRALIA.

SQL WHERE clause with NOT operator

NOT operator is used to specify the condition which we want to avoid or if we want results for which some specific condition is not true we should use NOT operator.

SQL WHERE clause syntax and example with NOT operator-

Suppose we want all the customers except the customers where COUNTRY is CHINA, we will specify this condition with NOT operator.

SELECT * FROM CUSTOMER WHERE NOT COUNTRY = 'CHINA';

+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
+----+--------+---------+------+-----------+
4 rows in set (0.00 sec)

Using above query we have got all the rows except the rows with country CHINA.