Day 04 – logical operators – AND, OR, and NOT
SQL provides three logical operators – AND, OR, and NOT – that can be used in conjunction with the WHERE clause to filter data based on multiple conditions. These operators are used to combine one or more conditions to create more complex conditions for filtering data. Here’s a brief overview of each operator:
- AND – The AND operator is used to retrieve rows that satisfy multiple conditions. If you specify multiple conditions separated by the AND operator, all the conditions must be true for the row to be retrieved.
Example:
SELECT * FROM orders
WHERE customer_id = 1234 AND order_date >= '2022-01-01';
This SQL statement retrieves all orders where the customer ID is 1234 and the order date is on or after January 1, 2022.
- OR – The OR operator is used to retrieve rows that satisfy at least one of the specified conditions. If you specify multiple conditions separated by the OR operator, the row will be retrieved if any one of the conditions is true.
Example:
SELECT * FROM orders
WHERE customer_id = 1234 OR order_date >= '2022-01-01';
This SQL statement retrieves all orders where the customer ID is 1234 OR the order date is on or after January 1, 2022.
- NOT – The NOT operator is used to retrieve rows that do not satisfy a specified condition. If you specify a condition after the NOT operator, the row will be retrieved only if the condition is false.
Example:
SELECT * FROM customers
WHERE NOT country = 'USA';
This SQL statement retrieves all customers where the country is not ‘USA’.
By using the AND, OR, and NOT operators in combination with the WHERE clause, you can create more complex conditions to filter and retrieve data from a table.