You are here

BETWEEN Operator in SQL

BETWEEN operator

BETWEEN

In this post we will discuss BETWEEN operator in SQL server. it is used for pattern searching.

  • To select data that is within a range of values, the BETWEEN operator is used. The BETWEEN operator allows the selection of rows that contain values within a specified lower and upper limit.
  • The lower value must be coded first. The two values of range must be linked with the keyword AND.
  • The BETWEEN operator can be used with both character and numeric data types.
  • We can use the BETWEEN operator in where clause with select, update, Delete.
  • We can also rewrite BETWEEN operator using less than and equal (<=) or grater than and equal (>=).
  • If we pass NULL values to the BETWEEN operator it returns NULL value in the result.
  • It’s return type is Boolean.

Syntax:
SELECT
col1, col2
FROM
table
WHERE
column BETWEEN lower_value AND upper_value;

  • We can also combine the BETWEEN operator with the NOT operator whose column values are not in the specified range.

Example:

Table: tblproduct

Pid pname price manufadate
1 Books 100.00 2017-01-12
2 Pens 200.00 2013-01-01
3 Pencils 50.00 2017-09-28
4 Calculators 700.00 2016-04-24
5 Laptop 23000.00 2017-08-14

Retrieve the name of product whose price is between 50 and 200.
select pname from tblproduct where price BETWEEN 50 AND 200;
or we  can also write
select pname from tblproduct where price>=50 AND price<=200;

output:

pname
Books
Pens
Pencils

Retrieve the productname, price, manufacturing date between 1/1/2017 And 31/12/2017.
select pname, price, manufadate from tblproduct where manufadate BETWEEN ‘1/1/2017′ AND ’12/31/2017’;
output:

Pname price manufadate
Books 100.00 2017-01-12
Pencils 50.00 2017-09-28
Laptop 23000.00 2017-08-14

Retrieve the pid, pname where pid between 2 and 4.
select pid, pname from tblproduct where pid BETWEEN 2 AND 4;
output:

pid pname
2 Pens
3 Pencils
4 Calculators

Retrieve the name of product whose price is not between 50 and 200.
select pname, price from tblproduct where price NOT BETWEEN 50 AND 200;
output:

pname price
Calculators 700.00
Laptop 23000.00

Keep Visiting TechwithR.com for more upcoming top technical articles and also see my new blog Mag4info. and for kotlin tutorial also see my blog kotlin category.

Detail course about DBA visit TechNet Consultancy

You can also by SQL 70-761 book write by me from Amazon.

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top