Loading

SQL Logical Operators

  • In SQL, logical operators are used to compare conditions in a WHERE clause to filter the rows returned by a query. 
  • The SQL Logical Operators are used within the WHERE clause.

Logical operators present in the SQL are:

  1. AND
  2. OR
  3. NOT

Logical AND:

  • The Logical AND operator is used to compare two operands, it returns TRUE when both the operands follow the logical condition provided in the SQL query.

Example:

Consider a table customer with the column Customer ID, Name, postal code, and country

Customer ID

Name

Postal code

Country

2

Shiva

05021

America

4

Hanuman

05023

Mexico

1

Dhruva

05026

Japan

3

Ganesh

05024

Mexico

If you want to list a country with the name Mexico and Customer ID>2 then it is represented as

SELECT *
FROM Customer
where country=Mexico AND CustomerID > 2;

Output:

Customer ID

Name

Postal Code

Country

4

Hanuman

05023

Mexico

3

Ganesh

05024

Mexico

  • In this example, we fetch the details of the customer whose country is Mexico and whose ID is greater than 2

Logical OR:

  • The Logical OR operator is used to compare two operands, it returns TRUE when any of the two operands follows the logical condition provided in the SQL query.
  • This Operator is used to combine multiple conditions, and at least one of them must evaluate to true for a row to be included in the result set.

Example:

Consider a customer table with the Customer name, ID, City, and Country

Customer IDNameCityCountry
1JamesParisGermany
2WilliamLyonItaly
3DanielBerlinFrance
4OliverMarseilleGermany

If we want to list all records with the city Berlin OR Country Germany then it is represented as

SELECT  *
FROM Customer
WHERE Country='Germany' OR City='Berlin';

Output:

Customer IDNameCityCountry
1JamesParisGermany
3DanielBerlinFrance
4OliverMarseilleGermany
  • In this example, we fetch the details of the customer with a country name Germany or Berlin

Logical NOT:

  • The Logical NOT operator has been used to change the value operand. If the operand's value is True, it changes the value to False. If the value of the operand is false, it changes the value to True.

Example:

Consider a customer table with columns Customer ID, Name, City and Country

ID

Customer Name

City

Country

1

James

Paris

Germany

2

Daniel

Berlin

Italy

3

Oliver

Marseille

Germany

4

Rose

Belgium

France

If you want to display records other than city Germany then we use NOT operator

SELECT *
FROM Customer
Where NOT country='Germany';

Output:

ID

Customer Name

City

Country

2

Daniel

Berlin

Italy

4

Rose

Belgium

France

  • In this example, we fetch the details of a customer who doesn't belong to Germany.

These logical operators are essential for constructing queries that retrieve specific subsets of data from a database based on your desired criteria.