Yogesh Chauhan's Blog

IN and BETWEEN Operators in SQL

in SQL/MySQL on August 24, 2019

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.


Most Read

#1 How to add Read More Read Less Button using JavaScript? #2 How to check if radio button is checked or not using JavaScript? #3 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
How to add Read More Read Less Button using JavaScript?JavaScriptHow to add new elements with swing animation using JavaScript and CSS?CSSHow to convert a number rounding to a specified number of decimals in JavaScript?JavaScriptHow services and dependency injection work in Angular?AngularLearn to Implement Estimated Reading Time using PHP Part 2: Final Implementation with Source CodePHPStyling Lists with CSSCSS