In & Between Operator In MySQL

In MySQL, The IN and BETWEEN operators are used in the WHERE clause to filter results based on specified conditions.

IN and BETWEEN provide a more readable way to specify multiple conditions in "SELECT", "UPDATE", and "DELETE" statements.

Use Cases of IN and BETWEEN in MySQL:

Filtering Rows with Multiple Values: Use the `IN` Operator to specify a list of discrete values for filtering.
Range Filtering: Use BETWEEN Operator to filter rows within a specific range, particularly useful for dates, or sequential values.
Improving Query Readability: Both operators make complex queries easier to read and understand.

Here's how we can use IN and BETWEEN Operators In MySQL:

IN Operator:

The IN operator is used to filter results based on a list of values. It allows us to specify multiple values for a column in the condition.

Example:

SELECT *
FROM products
WHERE category IN ('GAMES', 'HEALTH_CARE', 'CLOTHING', 'UTENSILS');

The above query retrieves all columns from the `products` table where the `category` is either 'GAMES', 'HEALTH_CARE', 'CLOTHING' or 'UTENSILS'.

IN Operator using Update Statement:

The below query will update the `product_quantity` to `1000` for those `product_code` inside IN (A111, A222, A333 and A444) Operator.

Example:

UPDATE products
SET product_quantity = 1000
WHERE product_code IN (A111, A222, A333, A444);

BETWEEN Operator:

The BETWEEN operator is used to filter results based on a range of values. It allows us to specify a range with a lower and upper limit.

Example:

SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2024-01-31';

The above query retrieves all columns from the orders table where the "order_date" falls within the range of January 1, 2023, to January 31, 2024.

BETWEEN Operator using Update Statement:

This query retrieves all columns from the products table where the "product_arrival_date" falls within the range of January 1, 2022, to December 31, 2022 and it will update `product_quantity` to `1000`.

Example:

UPDATE products
SET product_quantity = 1000
WHERE product_arrival_date BETWEEN '2022-01-01' AND '2022-12-31';

Combining IN and BETWEEN Operator in SQL:

We can also use both operators in combination to create more complex conditions.

Example:

SELECT *
FROM products
WHERE category IN ('GAMES', 'HEALTH_CARE')
  AND price BETWEEN 2000 AND 4000;

The above query retrieves all columns from the products table where the category is either 'GAMES' or 'HEALTH_CARE', and the price is between 2000 and 4000.

<-- Union

Null Values -->