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:
- AND
- OR
- 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 ID | Name | City | Country |
---|---|---|---|
1 | James | Paris | Germany |
2 | William | Lyon | Italy |
3 | Daniel | Berlin | France |
4 | Oliver | Marseille | Germany |
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 ID | Name | City | Country |
---|---|---|---|
1 | James | Paris | Germany |
3 | Daniel | Berlin | France |
4 | Oliver | Marseille | Germany |
- 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.