In & Between Operators In SQL

In SQL, 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 SQL:

Filtering Rows with Multiple Values: Use `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 these operators:

IN Operator in SQL:

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');

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

IN Operator in Update Statement:

The below query will update the `product_quantity` to `1000` for products with `product_code` are A111, A222, A333 and A444.

Example:

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

BETWEEN Operator in SQL:

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';

This 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 in 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;

This 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.

<-- Like

Delete -->

In & Between Operators In SQL

In SQL, 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 SQL:

Filtering Rows with Multiple Values: Use `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 these operators:

IN Operator in SQL:

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');

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

IN Operator in Update Statement:

The below query will update the `product_quantity` to `1000` for products with `product_code` are A111, A222, A333 and A444.

Example:

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

BETWEEN Operator in SQL:

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';

This 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 in Update Statement:

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;

This 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.

<-- Like

Delete -->