
This tutorial is the second part of the SQL WHERE Clause in MySQL, Basically, in this tutorial, we are going to explain the SQL Logical operators like AND, OR, NOT in a MySQL query.
MYSQL logical operators were helps in filtering the result set based on multiple conditions in the query. The Logical operators returns TRUE or False values.
The logical operators compare more than two conditions at a time to fetch filter data from the database.
Logical Operators | Description |
AND | AND Operator compares between two values at a time. Its returns TRUE if the both values are true otherwise false. |
OR | OR Operators compares between two values. Its returns TRUE if at least one condition true. |
NOT | Not takes a single condition as an argument and change its value from from true to false or false to true. |
The AND operator compare the two expressions and displays a record if all the conditions separated by AND are TRUE.
AND Operator Syntax
1 2 3 |
SELECT * FROM table_name WHERE condition_1 AND condition_2 AND condition_3 ... condition_3; |
Example: Fetch all Users details where city Delhi AND country India
Table Name : USERDETAILS
Id | FirstName | LastName | City | Country |
---|---|---|---|---|
1 | Rahul | Singh | Delhi | India |
2 | Max | Well | New York | USA |
3 | Robin | Williams | Los Angeles | USA |
4 | Aryan | Singh | Mumbai | India |
5 | Karan | Singh | Delhi | India |
6 | John | Smith | Houston | USA |
Select Statement with AND condition
1 |
SELECT FIRSTNAME, LASTNAME, CITY, COUNTRY FROM USERDETAILS WHERE CITY='Delhi' AND COUNTRY ='India'; |
In the above example, we will display the ‘FIRSTNAME’, ‘LASTNAME’, ‘CITY’, ‘COUNTRY’ from USERDETAILS table using following conditions.
Output
1 2 3 4 |
FIRSTNAME LASTNAME CITY COUNTRY -------------- -------------- ------ ---------- Rahul Singh Delhi India Karan Singh Delhi India |
The SQL Logical OR Operator compare two boolean expression and return TRUE when at least one condition is TRUE and its return FALSE if both conditions is FALSE.
OR Operator Syntax
1 2 3 4 |
SELECT column1, column2, ... columnN FROM table_name WHERE condition_1 OR condition_2 OR condition3 ...conditionN; |
Example: Fetch all Users from USA or Canada
Table Name : USERDETAILS
Id | FirstName | LastName | City | Country |
---|---|---|---|---|
1 | Rahul | Singh | Delhi | India |
2 | Max | Well | New York | USA |
3 | John | Williams | Toronto | Canada |
4 | Zen | Max | Marseille | France |
5 | Smith | Williams | Delhi | India |
6 | John | Smith | Houston | USA |
Select Statement with OR condition
1 |
SELECT FIRSTNAME, LASTNAME, CITY, COUNTRY FROM USERDETAILS WHERE COUNTRY ='USA' OR COUNTRY ='Canada'; |
In the following example we are fetching the ‘FIRSTNAME’, ‘LASTNAME’, ‘CITY’, ‘COUNTRY’ from USERDETAILS table with the following conditions.
Output
1 2 3 4 5 |
FIRSTNAME LASTNAME CITY COUNTRY -------------- -------------- ------ ---------- Max Well New York USA John Williams Toronto Canada John Smith Houston USA |
Not takes a single condition as an argument and change its value from from true to false or false to true.
NOT Operator Syntax
1 2 3 4 |
SELECT column1, column2, ... columnN FROM table_name WHERE NOT condition; |
Example: List all Users that are not from the UK
Table Name : USERDETAILS
Id | FirstName | LastName | City | Country |
---|---|---|---|---|
1 | Maria | Anders | Berlin | Germany |
2 | Hardy | Thomas | London | UK |
3 | John | William | Toronto | Canada |
4 | Laurence | Lebihans | Marseille | France |
5 | Elizabeth | Lincoln | Tsawassen | Canada |
6 | Aria | Diego | Houston | Brazil |
Select Statement with NOT condition
1 |
SELECT FIRSTNAME, LASTNAME, CITY, COUNTRY FROM USERDETAILS WHERE NOT COUNTRY ='UK'; |
Display the following columns with below condition.
Output
Id | FirstName | LastName | City | Country |
---|---|---|---|---|
1 | Maria | Anders | Berlin | Germany |
3 | John | William | Toronto | Canada |
4 | Laurence | Lebihans | Marseille | France |
5 | Elizabeth | Lincoln | Tsawassen | Canada |
6 | Aria | Diego | Houston | Brazil |