IN and BETWEEN Operators in SQL

The SQL IN Operator

The IN Operator allows users to specify multiple values in a WHERE clause in SQL query.

It’s actually a short version to ask to choose between many OR values. For example, (A,B,C) means either A or B or C.

Syntax:

SELECT column/columns
FROM table
WHERE column IN (value A, value B, ...);

You can use the following syntax as well:

SELECT column/columns
FROM table
WHERE column IN (SELECT Statement);

Examples:

SELECT * FROM country WHERE Name IN ('United States', 'Canada', 'United Kingdom');

You can see the results in DEMO link provided at the end of this article. The results contain only 3 countries. 

You can use the NOT IN in the same way, it will exclude those 3 countries the results. For example,

SELECT * FROM country WHERE Name NOT IN ('United States', 'Canada', 'United Kingdom') LIMIT 10;

LIMIT is used to limit the results as the database contains more than 100 countries. 

The SQL BETWEEN Operator

As the name suggests, BETWEEN operator selects the values within the given range. The range can be any two numbers, two dates or two texts.

REMEMBER:  The BETWEEN operator includes the range values in the results.

Syntax:

SELECT column/columns
FROM table
WHERE column BETWEEN number1 AND number2;

SELECT column/columns
FROM table
WHERE column BETWEEN date1 AND date2;

SELECT column/columns
FROM table
WHERE column BETWEEN text1 AND text2;

Examples:

SELECT * FROM country WHERE LifeExpectancy BETWEEN 40 AND 60 LIMIT 10;

You can see the results in DEMO link provided at the end of this article. The results list of countries with Life Expectancy between 40 and 60 inclusive. 

You can just add NOT and the results will be altered (excluding the range). For example,

SELECT * FROM country WHERE LifeExpectancy NOT BETWEEN 40 AND 60 LIMIT 10;

The query above will show the countries with life expectancy less than 40 and more than 60.

Using Texts

Let’s look at the example in which we give the range of texts.

SELECT * FROM country WHERE Region BETWEEN 'Caribbean' AND 'Middle East' LIMIT 10;

In the example above the query will look for the first Caribbean results and then Middle East texts. Then it will display all the results between those two texts. I have set the limit 10 so it will display maximum of 10 results. 

Now, if there are more than one Caribbean or Middle East, then it will search for the first place for both of those texts.

Share this post