Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Use of IN operator and BETWEEN operator in MySQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 2
    • 0
    • 279
    Comment on it

    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(,).

    For ex:-
    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

    For ex:
    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);

     

    BETWEEN Operator
    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;

    For ex:-
    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 <=
    For Ex:-

    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#;

     

    Use of IN operator and BETWEEN operator in MySQL

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: