What is the difference between BETWEEN and IN operator in SQL?

Last updated 1 year, 9 months ago | 758 views 75     5

Tags:- SQL MySQL

SQL | difference between BETWEEN and IN operator

The BETWEEN operator selects all records between the given range whereas IN operator selects all matching records given with the WHERE clause.

 

BETWEEN operator

The Between operator in SQL is used to fetch records based on a range. This operator selects all records between a given range.

Syntex

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

Let's see an example,

SELECT id, name, roll_no FROM students 
WHERE roll_no BETWEEN 20 AND 30;

The above query will select all rows from the table students where the value of the field roll_no lies between 20 and 30.


IN operator

The IN operator allows you to specify multiple values in a WHERE clause.

Syntex

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

For example,

SELECT * FROM students 
WHERE roll_no IN (20,21,23);

This query will select all rows from the table students where the value of the field roll_no is either 20 or 21 or 23.