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.
What are Logical operators in SQL?
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.
Uses of Logical Operators
- Logical operators are used for filtering the data based on conditions.
- Logical operators returns TRUE or FALSE values.
- It is also used for comparing the values at a time.
The AND, OR and NOT logical operators
| 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. |
SQL Logical AND Operator
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.
- CITY must be Delhi
- and the COUNTRY should be India
Output
|
1 2 3 4 |
FIRSTNAME LASTNAME CITY COUNTRY -------------- -------------- ------ ---------- Rahul Singh Delhi India Karan Singh Delhi India |
SQL Logical OR Operator
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.
- either country is ‘USA’
- or country is ‘Canada’
Output
|
1 2 3 4 5 |
FIRSTNAME LASTNAME CITY COUNTRY -------------- -------------- ------ ---------- Max Well New York USA John Williams Toronto Canada John Smith Houston USA |
SQL Logical NOT Operator
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.
- country NOT a UK
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 |
Share this:
