In SQL, The "AND" and "OR" operators combine multiple conditions in a WHERE clause to filter rows from a table based on specified criteria.
These operators allow you to create more complex conditions by specifying that multiple conditions must be true (AND) or at least one condition must be true (OR).
Here's how we can use AND & OR Operator in SQL Queries:
The AND operator combines multiple conditions, requiring all the specified conditions found to be true for a row to be included in the result response.
SELECT * FROM products WHERE category = 'GAMES' AND price > 1000;
Note: This query retrieves all columns from the `products` table where the `category` is 'GAMES' and the `price` is greater than `1000`.
The OR operator combines multiple conditions, requiring at least one of the specified conditions found to be true for a row to be included in the result response.
SELECT * FROM products WHERE category = 'GAMES' OR category = 'HEALTH_CARE';
Note: This query retrieves all columns from the `products` table where the `category` is either 'GAMES' or 'HEALTH_CARE'.
We can also use parentheses to control the order of evaluation when combining AND and OR operators.
SELECT * FROM products WHERE (category = 'GAMES' AND price > 5000) OR (category = 'HEALTH_CARE' AND price > 10000);
Note: The above query retrieves all columns from the `products` table where either: The category is 'GAMES' and the price is greater than 5000 , OR The category is 'HEALTH_CARE' and the price is greater than 10000 .
We can also use parentheses to control the order of evaluation when combining AND and OR operators.
SELECT * FROM products WHERE (NOT category = 'CLOTHING' AND product_price < 500) OR category = 'ELECTRONICS';
The above query returns all records from the `products` table where either.
The `category` is not 'CLOTHING' and the `product_price` is less than 500 or `category` is 'ELECTRONICS'.
The NOT operator negates the condition following it.