MySQL AND OR NOT Operator


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’.

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.