MySQl IN operator facilitates us to match a column's value against multiple values in one go.
As most of us are aware that we use WHERE clause for giving any condition in SELECT, INSERT, UPDATE, DELETE statements, so whenever we have to match a value against multiple values in WHERE clause we give those multiple values in IN operator and those multiple values are separated by comma(,).
To select those users whose id's are 10 or 20 or 22 or 25 or 28, we use IN operator as
SELECT * FROM Users WHERE Id IN (10, 20, 22, 25, 28);
While executing the above query, the IN operator will return 1 if a value of Id lies in the comma separated values passed to IN operator otherwise it returns 0.
To give a simple example, I am selecting those customers who belong to either Argentina or Belgium or Poland.
SELECT * FROM Customers where Country IN ('Argentina', 'Belgium', 'Poland');
Sample result from this query is :
IN operator can be combined with NOT operator to fetch those values which are excluded from a particular list of elements, i.e
To select those users whose id's are not 10 or 20 or 22 or 25 or 28, we will write
SELECT * FROM Users WHERE Id NOT IN (10, 20, 22, 25, 28);
As the name suggests it is used to fetch values lying in a specified range. i.e It fetches values in a given range.
It's Syntax is :
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
If I want to select users whose Id lies between 10 and 50, I would write
SELECT * FROM Users WHERE Id BETWEEN 10 AND 50;
(Inclusion & Exclusion of Lower limit & Upper Limit depends on the database you are using).
Below is the sample query and its output:- (I am selecting those customers whose id lies between 10 and 15)
SELECT * FROM Customers where CustomerID BETWEEN 10 AND 15;
BETWEEN operator is equivalent of using >= AND <=
The following query will give the same result as given by above query written using BETWEEN operator
SELECT * FROM Customers where CustomerID >=10 AND CustomerID <=15 ;
We can also use the BETWEEN operator with Date values and String values.
ex:- (To fetch those students whose date of birth lies between 1/1/90 and 5/2/2015 the query would be)
SELECT * FROM Students WHERE DOB BETWEEN #01/01/1990# AND #05/02/2015#;