YogeshChauhan . com

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.

amazon

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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

How to enable GD library support for PHP on windows server?PHPHow to create a new HTML element programmatically using JavaScript?HTMLHow to draw an SVG path on scroll using JavaScript?JavaScriptHow to add Local State to a Class in React?ReactHow to hide a DIV on clicks outside of it using jQuery?jQueryHow to use @if and @else in SCSS?SCSS