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