sql between operator

SQL Advance

homeBack

Next

SQL BETWEEN Operator

  • The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

  • SQL BETWEEN Operator Syntax

    SELECT column_name(s)
    FROM table_name
    WHERE column_name
    BETWEEN value1 AND value2

  • SQL BETWEEN Operator EXAMPLE(1) :

    Table Name ” emp” 

    S.no Name1 Name2 City
    2458 Peter Jhon Hyderabad
    4857 Faran Khan Hyderabad
    5874 Raj Kiran London
    8578 Sameer Ali Mumbai

    we want to select the persons with a Name1 alphabetically between “Faran” and “Sameer” from the table above. 
    We use the following SELECT statement: 

     SELECT * FROM emp
    WHERE name1
    BETWEEN ‘Faran’ AND ‘Sameer’ 

    Result: 

    S.no Name1 Name2 City
    4857 Faran Khan Hyderabad

  • SQL BETWEEN Operator EXAMPLE(2) :

    Table Name ” emp” 

    S.no Name1 Name2 City
    2458 Peter Jhon Hyderabad
    4857 Faran Khan Hyderabad
    5874 Raj Kiran London
    8578 Sameer Ali Mumbai

    To display the persons outside the range in the previous example, use NOT BETWEEN: 

     SELECT * FROM emp
    WHERE name1
    NOT BETWEEN ‘Faran’ AND ‘Sameer’ 

    Result: 

    S.no Name1 Name2 City
    2458 Peter Jhon Hyderabad
    5874 Raj Kiran London
    8578 Sameer Ali Mumbai

  • NOTE :

    The BETWEEN operator is treated differently in different databases! 
    In some databases, persons with the LastName of “Hansen” or “Pettersen” will not be listed, 
    because the BETWEEN operator only selects fields that are between and excluding the test values. 

    In other databases, persons with the LastName of “Hansen” or “Pettersen” will be listed
    because the BETWEEN operator selects fields that are between and including the test values. 

    And in other databases, persons with the LastName of “Hansen” will be listed, 
    but “Pettersen” will not be listed (like the example above), because the BETWEEN operator selects fields between the test values, 
    including the first test value and excluding the last test value. 

    Therefore: Check how your database treats the BETWEEN operator.

  • Advertisements

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s