MySQL WHERE Clause with 3 SQL Logical operators

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

  1. Logical operators are used for filtering the data based on conditions.
  2. Logical operators returns TRUE or FALSE values.
  3. It is also used for comparing the values at a time.

The AND, OR and NOT logical operators

Logical OperatorsDescription
ANDAND Operator compares between two values at a time. Its returns TRUE if the both values are true otherwise false.
OROR Operators compares between two values. Its returns TRUE if at least one condition true.
NOTNot 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

Example: Fetch all Users details where city Delhi AND country India

Table Name : USERDETAILS

IdFirstNameLastNameCityCountry
1RahulSinghDelhiIndia
2MaxWellNew YorkUSA
3RobinWilliamsLos AngelesUSA
4AryanSinghMumbaiIndia
5KaranSinghDelhiIndia
6JohnSmithHoustonUSA

Select Statement with AND condition

In the above example, we will display the ‘FIRSTNAME’, ‘LASTNAME’, ‘CITY’, ‘COUNTRY’ from USERDETAILS table using following conditions.

  1. CITY must be Delhi
  2. and the COUNTRY should be India

Output

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

Example: Fetch all Users from USA or Canada

Table Name : USERDETAILS

IdFirstNameLastNameCityCountry
1RahulSinghDelhiIndia
2MaxWellNew YorkUSA
3JohnWilliamsTorontoCanada
4ZenMaxMarseilleFrance
5SmithWilliamsDelhiIndia
6JohnSmithHoustonUSA

Select Statement with OR condition

In the following example we are fetching the ‘FIRSTNAME’, ‘LASTNAME’, ‘CITY’, ‘COUNTRY’ from USERDETAILS table with the following conditions.

  1. either  country is ‘USA’
  2. or country is ‘Canada’

Output

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

Example: List all Users that are not from the UK

Table Name : USERDETAILS

IdFirstNameLastNameCityCountry
1Maria AndersBerlinGermany
2HardyThomas LondonUK
3JohnWilliamTorontoCanada
4LaurenceLebihansMarseilleFrance
5Elizabeth LincolnTsawassenCanada
6Aria Diego HoustonBrazil

Select Statement with NOT condition

Display the following columns with below condition.

  1. country NOT a UK

Output

IdFirstNameLastNameCityCountry
1Maria AndersBerlinGermany
3JohnWilliamTorontoCanada
4LaurenceLebihansMarseilleFrance
5Elizabeth LincolnTsawassenCanada
6Aria Diego HoustonBrazil