AND & OR Operators In MySQL

In MySQL, 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 us to create more complex conditions by specifying that multiple conditions must be met "AND" or at least one condition must be met "OR".

Here's how we can use AND & OR Operator in MySQL:

AND Operator:

The AND operator is used to combine multiple conditions, and it requires that all the specified conditions are true for a row to be included in the result set.

Example:

SELECT *
FROM products
WHERE category = 'GAMES' AND price > 1000;

Note: The above query retrieves all columns from the `products``category` is equal to 'GAMES' and the `price` is greater than `1000`.

OR Operator:

The OR operator is used to combine multiple conditions, and it requires that at least one of the specified conditions is true for a row to be included in the result set.

Example:

SELECT *
FROM products
WHERE category = 'GAMES' OR category = 'HEALTH_CARE';

Note: The above query retrieves all columns from the `products` table where the category is equal either to 'GAMES' or 'HEALTH_CARE'.

Combining AND and OR:

We can also use parentheses to control the order of evaluation when combining "AND" and "OR" operators.

Example:

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 The `category` is equal to 'GAMES' and the `price` is greater than `5000`, OR The `category` is equal to 'HEALTH_CARE' and the price is greater than `10000`.

Combining AND and OR with NOT in SQL:

We can also use parentheses to control the order of evaluation when combining AND and OR operators.

Example:

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 equal to 'ELECTRONICS'.
The `NOT` operator negates the condition following it.

<-- Count, Sum, Avg

NOT -->