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 check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
What happens when we add Numbers and Strings in JavaScript?JavaScriptWhat’s the difference between visibility: hidden and display: none?CSSWordPress: How to find all posts with a specific custom field value?WordPressHow to create a Random Hex Color generator using JavaScript?JavaScriptA short basic guide on states in ReactReactDebugging in WordPress Part 1: WP_DEBUGWordPress