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 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

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

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.

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

Output

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

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

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.

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

Output

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

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

SELECT FIRSTNAME, LASTNAME, CITY, COUNTRY FROM USERDETAILS WHERE NOT COUNTRY ='UK';

Display the following columns with below condition.

1. 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