The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.
• The NOT operator displays a record if the condition(s) is NOT TRUE.

The general syntax of AND Operator is as follows

AND operator Syntax

SELECT columnName1, columnName2,............
FROM tableName
WHERE condition1 AND condition2 AND condition3.....; 

OR Operator Syntax

SELECT columnName1, columnName2,............
FROM tableName
WHERE condition1 OR condition2 OR condition3.....; 

NOT operator Syntax

SELECT columnName1, columnName2, ...
FROM table_name
WHERE NOT condition;

Sample DataBase for AND, OR, NOT Operator

mysql> select * from student;
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 101   | rakesh  | comm   | 89.00 | A     | 9871812332  | M      |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
+-------+---------+--------+-------+-------+-------------+--------+

Use of AND Operator with where Clause

mysql> select * from student
    -> where stream='comm' and gender='F';
+-------+--------+--------+-------+-------+-------------+--------+
| admno | name   | stream | marks | grade | phone       | gender |
+-------+--------+--------+-------+-------+-------------+--------+
| 105   | unnati | comm   | 77.50 | B     | 96887766551 | F      |
+-------+--------+--------+-------+-------+-------------+--------+

Since only the above row is able to satisfy both the condition, this is only the result of the above SQL statement.

Use of OR operator with Where clause

mysql> select * from student
    -> where stream='comm' OR gender='F';
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 101   | rakesh  | comm   | 89.00 | A     | 9871812332  | M      |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
+-------+---------+--------+-------+-------+-------------+--------+
6 rows in set (0.00 sec)

Now all these 6 rows either have stream =’comm’ or their gender is ‘F’

Use of NOT operator inWhere Clause

mysql> select * from student
    -> where  NOT stream='comm';
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
+-------+---------+--------+-------+-------+-------------+--------+
5 rows in set (0.00 sec)

The above command is going to fetch all the rows where stream is not ‘comm’.