You are here
Home > Sql Server > BETWEEN Operator in sql

BETWEEN Operator in sql

BETWEEN Operator

  • BETWEEN operator is used to select data that is within a range of values. It allows the selection of rows that contain values within a specified lower and upper limit.
  • It can be used in where clause with a SELECT, DELETE, INSERT, or UPDATE statement.
  • Syntax:
  • SELECT

    column1, column2,…., columnN

    FROM

    tablename

    WHERE

    columnname BETWEEN value1 AND value2;

  • Here, value1 is lower value and value2 is upper value.
  • We can combine BETWEEN with NOT operator whose column values are not in the specified range.
  • Syntax:
  • SELECT

    column1, column2,….,columnN

    FROM

    tablename

    WHERE

    columnname NOT BETWEEN value1 AND value2;

  • Here, value1 is lower value and value2 is upper value.
  • Examples: 
  • Create DEMO and EMP tables.
  • CREATE TABLE DEMO(
  •             ID int NOT NULL,

    NAME varchar(50) NOT NULL,

    CITY varchar(50) NOT NULL,

    SALARY int NOT NULL)

     

  • CREATE TABLE EMP(
  •             ID int NOT NULL,

    NAME varchar(50) NOT NULL,

    CITY varchar(50) NOT NULL,

    SALARY int NOT NULL

    )

  • Table: EMP
  • ID

    NAME CITY SALARY

    1

    KRISHNA

    ANAND 2000

    2

    KISHAN V.V.NAGAR 4000

    3

    GOVIND ANAND

    4000

    4

    MADHAV AHMEDABAD

    8000

    5 SHIV SURAT

    6000

    6 OM SURAT

    8000

     

     

1) Retrieve EMP table records where name between GOVIND and OM.

SELECT * FROM EMP WHERE NAME BETWEEN ‘GOVIND’ AND ‘OM’;

  • Output:
  1. ID

    NAME CITY SALARY

    1

    KRISHNA ANAND 2000

    2

    KISHAN V.V.NAGAR 4000

    3

    GOVIND ANAND 4000

    4

    MADHAV

    AHMEDABAD

    8000

    6

    OM SURAT

    8000

     

 

2) Retrieve EMP table records where ID between 1 and 4.

SELECT * FROM EMP WHERE ID BETWEEN 1 AND 4; 

  • Output:
  1. ID

    NAME CITY

    SALARY

    1

    KRISHNA ANAND 2000

    2

    KISHAN V.V.NAGAR 4000

    3

    GOVIND

    ANAND 4000

    4

    MADHAV AHMEDABAD

    8000

 

 

3) Retrieve EMP table records where salary between 2000 and 6000.

SELECT * FROM EMP WHERE SALARY BETWEEN 2000 AND 6000; 

  • Output:
  • ID

    NAME CITY SALARY

    1

    KRISHNA ANAND 2000

    2

    KISHAN V.V.NAGAR 4000

    3

    GOVIND ANAND 4000

    5

    SHIV

    SURAT

    6000

 

4) Retrieve EMP table records where city is not between anand and surat.

SELECT * FROM EMP WHERE CITY NOT BETWEEN ‘ANAND’ AND ‘SURAT’;

  • Output:
  • ID

    NAME CITY SALARY

    2

    KISHAN V.V.NAGAR 4000

    4

    MADHAV AHMEDABAD

    8000

 

5) Retrieve EMP table records where salary is not between 2000 and 6000.

SELECT * FROM EMP WHERE SALARY NOT BETWEEN 2000 AND 6000;

  • Output:
  • ID

    NAME CITY SALARY

    4

    MADHAV AHMEDABAD 8000

    6

    OM

    SURAT 8000

        

 

6) Delete EMP table records where ID between 1 and 4.

DELETE FROM EMP WHERE ID BETWEEN 1 AND 4;

  • Output: 4 rows affected.
  • Select data from EMP table.

SELECT * FROM EMP;

  • Output:
  • ID

    NAME CITY SALARY

    5

    SHIV SURAT

    6000

    6

    OM SURAT

    8000

 

 

7) Insert EMP table records in to DEMO table where ID between 5 and 6;

      INSERT INTO DEMO SELECT * FROM EMP WHERE ID BETWEEN 5 AND 6;

  • Two rows will be inserted. Select data from DEMO table.

SELECT * FROM DEMO;

  • Output:
  • ID

    NAME CITY SALARY

    5

    SHIV SURAT 6000
    6 OM SURAT

    8000

 

 

8) Update name from DEMO table where ID between 5 and 6.

UPDATE DEMO SET NAME=’GANESH’ WHERE ID BETWEEN 5 AND 6;

  • Two rows will be updated. Select data from DEMO table.

SELECT * FROM DEMO;

  • Output:
  • ID

    NAME CITY SALARY

    5

    GANESH SURAT 6000

    6

    GANESH SURAT

    8000

 

Keep visiting my site ask2tech.com for upcoming top technical article

Learn more about DBA at TechNet Consultancy

Leave a Reply

Top