BETWEEN AND IN Operator in SQL

Last Updated: 29th October, 2023

The BETWEEN and IN operators in SQL are used to compare values within a given range or a set of values. The BETWEEN operator is utilized to compare two values inside a range, whereas the IN operator is utilized to compare a value with a set of values. Both are commonly utilized when querying a database to choose information from a range or set of values.

BETWEEN Operator in SQL

Sara and Jack work together in a fabricating plant. Every day they need to check the stock and ensure they have sufficient parts to keep the generation line running. To do this, they utilize the "between" and "in" operators when running stock reports. The between operator in SQL permits Sara and Jack to choose a range of parts to search for, such as all parts between A1 and A10. The "in" operator permits Sara and Jack to search for parts such as parts A1, A2, and A3. Sara and Jack can quickly and accurately check the inventory and keep the production line running smoothly using both operators. So let's help them with the operators.

BETWEEN Operator in SQL is a comparison operator that checks if a value is within a specified range.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

The BETWEEN keyword in this SQL syntax filters the results only to include rows where the values of the specified column are within a range. It is used to specify a range to test. In this example, the range is between two values, value1, and value2, for the specified column_name.

Example:

Table Name: orders

SELECT * FROM orders
WHERE order_date BETWEEN '2020-01-01' AND '2020-12-31';

In this example, we select all orders from the orders table, the order date between January 1, 2020, and December 31, 2020.

Using BETWEEN with Numeric Values:

Syntax

SELECT * 
FROM Employees 
WHERE Years_Employed BETWEEN <lower_number> AND <higher_number>;

This syntax retrieves all records of employees from the Employees table who have been employed for a duration of between two specified numbers. The <lower_number> and <higher_number> represent the two numbers that will be used to define the range of values to search for.

Example:

Employees

SELECT *
FROM Employees
WHERE Years_Employed BETWEEN 5 AND 10;

This example retrieves all records of employees from the Employees table who have been employed for a duration of between 5 and 10 years.

Using BETWEEN with Date Values:

Syntax

SELECT *
FROM Orders
WHERE Order_Date BETWEEN <start_date> AND <end_date>;

This syntax retrieves all records of orders from the Orders table that were placed within a specified date range. The <start_date> and <end_date> represent the two dates that will be used to define the range of values to search for.

Example: Orders

SELECT *
FROM Orders
WHERE Order_Date BETWEEN '2020-01-01' AND '2020-12-31';

This example retrieves all records of orders from the Orders table that were placed between the dates of January 1, 2020, and December 31, 2020.

IN Operator in SQL

The IN operator in SQL is utilised to check whether a value is present in a list of values or not. It returns TRUE if the value is present in the list and FALSE if not.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (list_of_values);

This statement will select all the column names listed from the specified table where the specified column name is equal to any of the values in the list of values.

Example:

SELECT * FROM Customers
WHERE CustomerName IN ('IBM', 'Microsoft', 'Apple');

This example will select all records from the Customers table where the CustomerName field equals ‘IBM’, ‘Microsoft’, or ‘Apple’.

Conclusion

Sara and Jack successfully managed the inventory in their manufacturing plant by utilizing the between and in operator in SQL when running inventory reports. This allowed them to quickly and accurately select a range of parts or a specific set of parts, ensuring that the production line was running smoothly.

Key takeaways

1. The IN operator in SQL is used to specify multiple values in a WHERE clause. It is used to match one or more values specified in a list.
2. The BETWEEN operator is utilized to retrieve values within a range.cIt is utilized to match values that are inside a certain range indicated by the client.
3. The IN operator is speedier than the BETWEEN operator since it only has to evaluate the list of values once. The BETWEEN operator should evaluate the range of values twice.
4. The IN operator is valuable for checking for the presence of particular values in a list or table.
5. The BETWEEN operator is valuable for comparing values inside a range. It can also be utilized in conjunction with other operators, such as LIKE or NOT LIKE, to refine the look further.

Quiz

1. Which of the following operators can be used to compare a range of values in SQL? 
   1. Between 
   2. In 
   3. Both A and B  
   4. None of the Above

Answer: C. Both A and B

2. What type of data can be used in the IN operator in SQL?  
   1. Integer 
   2. String 
   3. Boolean 
   4. All of the Above

Answer: D. All of the Above

3. Which operator would you use if you wanted to select all rows from a table where the column value is between two values? 
   1. Between 
   2. In 
   3. Both A and B  
   4. None of the Above

Answer: A. Between

4. In the SQL statement "SELECT * FROM customers WHERE customer_id IN (1,2,3)", what is the purpose of the IN operator?
   1. To compare two values 
   2. To check if a value is equal to any value in a list  
   3. To check if a value is not equal to any value in a list  
   4. To compare a range of values

Answer: B. To check if a value is equal to any value in a list