In and Between Operators Cheat Sheet > Querying with SQL

 IN and BETWEEN Operators

Consider the case of a typical e-commerce scenario. Users generally search for the products that belong to a list of brands, or the products that lie within a particular price range.

In such scenarios, we use the IN operator to check if a value is present in the list of values. And, BETWEEN operator is used to check if a particular value exists in the given range.

Let’s learn about these operators in detail using the following database.

Database The database contains a

product

table that stores the data of products like name, category, price, brand and rating. You can check the schema and data of

product

table in the code playground.

IN Operator

Retrieves the corresponding rows from the table if the value of column(c1) is present in the given values(v1,v2,..).

Syntax

1

2

3

4

5

6

SELECT

*

FROM

table_name

WHERE

c1 IN (v1, v2,..);

SQL

Example

Get the details of all the products from

product

table, where the

brand

is either "Puma", "Mufti", "Levi's", "Lee" or "Denim".

1

2

3

4

5

6

SELECT

*

FROM

product

WHERE

brand IN ( "Puma", "Levi's", "Mufti", "Lee", "Denim");

SQL

Output

name	category	price	brand	rating
Blue Shirt	Clothing	750	Denim	3.8
Blue Jeans	Clothing	800	Puma	3.6
Black Jeans	Clothing	750	Denim	4.5
...	...	...	...	...

Try it Yourself!

• Get all the products from
  product
  table, that belong to "Britannia", "Lay's", "Cadbury" brands from the "Food" category.

BETWEEN Operator

Retrieves all the rows from table that have cloumn(c1) value present between the given range(v1 and v2).

Syntax

1

2

3

4

5

6

7

SELECT

*

FROM

table_name

WHERE

c1 BETWEEN v1

AND v2;

SQL

Note

BETWEEN

operator is inclusive, i.e., both the lower and upper limit values of the range are included.

Example

Find the products with

price

ranging from 1000 to 5000.

1

2

3

4

5

6

7

8

9

SELECT

name,

price,

brand

FROM

product

WHERE

price BETWEEN 1000

AND 5000;

SQL

Output

name	price	brand
Blue Shirt	1000	Puma
Smart Cam	2600	Realme
Realme Smart Band	3000	Realme

Possible Mistakes

1. When using the
   BETWEEN
   operator, the first value should be less than second value. If not, we'll get an incorrect result depending on the DBMS.

1

2

3

4

5

6

7

8

9

SELECT

name,

price,

brand

FROM

product

WHERE

price BETWEEN 500

AND 300;

SQL

Output

name	price	brand

2. We have to give both lower limit and upper limit while specifying range.

1

2

3

4

5

6

7

8

9

SELECT

name,

price,

brand

FROM

product

WHERE

price BETWEEN

AND 300;

SQL

1

Error: near "AND": syntax error

SQL

3. The data type of the column for which we're using the
   BETWEEN
   operator must match with the data types of the lower and upper limits.

1

2

3

4

5

6

7

8

9

SELECT

name,

price,

brand

FROM

product

WHERE

name BETWEEN 300

AND 500;

SQL

Output

name	price	brand

Try it Yourself!

• Get all the products from
  product
  table with
  rating
  greater than 4.3 and less than 4.8