One of the key features of SQL is its ability to use logical operators to filter and sort data. In this tutorial, we will explore the AND, NOT, and OR operators in SQL and how to use them to retrieve the data we need.
AND Operator
The AND operator is used to retrieve data that meets multiple conditions. It requires both conditions to be true for the record to be included in the result set. For example, let’s say we have a table of customer data with columns for first name, last name, and age. We want to retrieve all customers whose first name is “John” and last name is “Doe” and whose age is greater than 25. We can use the AND operator to achieve this:
|
1 2 3 4 |
SELECT * FROM customers WHERE first_name = 'John' AND last_name = 'Doe' AND age > 25; |
This query will return all records in the “customers” table where the first name is “John”, the last name is “Doe”, and the age is greater than 25.
NOT Operator
The NOT operator is used to retrieve data that does not meet a specific condition. It negates the condition that follows it. For example, let’s say we want to retrieve all customers whose age is not greater than 25. We can use the NOT operator to achieve this:
|
1 2 3 |
SELECT * FROM customers WHERE NOT age > 25; |
This query will return all records in the “customers” table where the age is not greater than 25.
OR Operator
The OR operator is used to retrieve data that meets at least one of multiple conditions. It requires only one of the conditions to be true for the record to be included in the result set. For example, let’s say we want to retrieve all customers whose first name is “John” or last name is “Doe”. We can use the OR operator to achieve this:
|
1 2 3 4 |
SELECT * FROM customers WHERE first_name = 'John' OR last_name = 'Doe'; |
This query will return all records in the “customers” table where the first name is “John” or the last name is “Doe”.
SQL WHERE Operators: AND, NOT, OR Summary
Logical operators are an essential part of SQL and can be used to filter and sort data in a variety of ways. The AND, NOT, and OR operators are particularly useful for retrieving data that meets multiple conditions, negating conditions, and retrieving data that meets at least one of multiple conditions. By mastering these operators, you can gain a greater understanding of how to manipulate data in SQL and improve your ability to retrieve the data you need.