There three type of logical operators – AND OR NOT
| AND | Check two operands are equal or not |
| OR | Check at least one operand true |
| NOT | Is seems true->false and false->true |
Let’s consider a table for operators operations of AND operator.
AND operator
The AND operator is used to combine two or more conditions and return true if all the conditions are true.
Syntax –
Syntax –
| SELECT column1, column2,… FROM table WHERE condition1 AND condition2; |
For example, the following query would return all records where both the gender is ‘M’ AND their age must be greater than 55.
For example, the following query would return all records where both the gender is ‘F’ AND their age must be greater than 55.
Note: you can use multiple AND operators also. How!!! Let’s see below:
Let’s see some another example:
Let’s take another example:
OR operator
The OR operator is used to combine two or more conditions and return true if any of the conditions are true.
Syntax –
| SELECT column1, column2,… FROM table WHERE condition1 OR condition2; |
Let’s consider a table for operators operations of OR operator.
For example, the following query would return all records where loc either ‘kolkata’ or ‘california’
NOT operator
The NOT operator is used to negate a condition and return true if the condition is not true.
Syntax –
| SELECT column1, column2,… FROM table WHERE NOT condition; |
Consider this table:
For example, the following query would return name,age where age is not greater than 35.
For example, the following query would return name,loc,age where loc is not ‘kolkata’.
Let’s take another example:
Let’s work with IN operator with NOT operator
The IN operator in MySQL is used to specify multiple values in a WHERE clause. It allows you to specify multiple values in a single statement, making it more concise and easier to read than using multiple OR conditions. For example, you can use the IN operator to find all records where a column matches any of a list of specified values. Here’s an example:
| SELECT * FROM my_table WHERE column_name IN (value1, value2, value3); |
This query will return all records where column_name matches value1, value2, or value3.
Note, we can use ‘NOT IN’ operator in sub-queries. So the syntax:
| SELECT * FROM my_table WHERE column_name NOT IN (value1, value2, value3); |
Let’s consider a student’s table
Let’s implement IN operator into this table:
Let’s implement ‘NOT IN’ operator into this table:
Let’s understand by another example: show all students who not belongs from ‘bakura’
