MySQL supports the following logical operations :
- AND(&&) Operator
- OR(||) Operator
- NOT(!) Operator
Lets we see the Logical Operators in MySQL.
MySQL AND(&&) Operator :
The logical AND(&&) operator indicates whether the both operands are true. Lets see a statement using AND operator.
mysql> select studid, name from student where marks > 80
and marks < 100;
(or)
mysql> select studid, name from student where marks > 80
&& marks < 100;
+--------+-------+
| studid | name |
+--------+-------+
| 4 | jack |
| 8 | mille |
+--------+-------+
2 rows in set (0.00 sec)
In the above example it will list the studid and name of the student who have secured more than 80 and less than 100.MySQL OR(||) Operator :
The logical OR(||) operator indicates whether either operand is true. Lets see a statement using OR operator.
mysql> select name, marks, address from student where
name like 'a%' or name like 's%';
(or)
mysql> select name, marks, address from student where
name like 'a%' || name like 's%';
+-------+-------+------------------+
| name | marks | address |
+-------+-------+------------------+
| steve | 100 | 5th cross street |
| anne | 100 | downing street |
| steve | 75 | downing street |
| anne | 80 | edinburgh |
+-------+-------+------------------+
4 rows in set (0.00 sec)
In the above statement it will list the name, marks and address of the student whose name starts with the letter A and S.MySQL NOT(!) Operator :
The logical NOT(!) operator have only one operand and it returns the inverse of the value.
mysql> select * from student where not (studid=1);
(or)
mysql> select * from student where ! (studid=1);
+--------+-------+-------+-----------------+---------+
| studid | name | marks | address | phone |
+--------+-------+-------+-----------------+---------+
| 2 | david | 100 | welling street | 547896 |
| 4 | jack | 82 | welling street | 2436821 |
| 5 | anne | 100 | downing street | 2634821 |
| 6 | steve | 75 | downing street | 2874698 |
| 7 | anne | 80 | edinburgh | 2569843 |
| 8 | mille | 98 | victoria street | 1236547 |
+--------+-------+-------+-----------------+---------+
6 rows in set (0.00 sec)
MYQL Comparison
EQUAL(==)
LESS THAN(<)
LESS THAN OR EQUAL(<=)
GREATER THAN(>)
GREATER THAN OR EQUAL(>=)
NOT EQUAL(<>,!=)
