Question

what is the range of value in field

Answer 1

maximum value - minimum value

Answer 2

The BETWEEN operator is used in the WHERE clause to select a value within a range of values. We often use the BETWEEN operator in the WHERE clause of the SELECT, UPDATE and DELETE statements.

The following illustrates how to use the BETWEEN operator:

1

2

3

4

5

6

SELECT

    column_1, column_2

FROM

    table

WHERE

    (expr | column) BETWEEN lower_value AND upper_value;

The BETWEEN operator returns TRUE if the result of the expression or value of the column specified in the WHERE clause is less than or equal to lower_value and greater than or equal to upper_value. Otherwise, it returns FALSE. The BETWEEN operator is inclusive.

To specify an exclusive range, you use the less than (<) and greater than (>) operators instead.

If you pass the NULL values to the BETWEEN operator e.g., expr,  lower_valueor upper_value, the BETWEEN operator returns NULL.

SQL BETWEEN operator examples

Let’s take a look at some examples of using the BETWEEN operator.

SQL BETWEEN with number example

The following query selects product whose unit price is from $18 to $19:

1

2

3

4

5

6

SELECT

    productName, unitPrice

FROM

    products

WHERE

    unitPrice BETWEEN 18 AND 19;



You can rewrite the BETWEEN operator using less than or equal ( <= ) and greater than or equal ( >=) operators as follows:

1

2

3

4

5

6

SELECT

    productName, unitPrice

FROM

    products

WHERE

    unitPrice >= 18 AND unitPrice <= 19;

This query produces the same result set, however, the query that uses the BETWEEN operator is much more readable.

The following query uses less than (<) or greater than (>) operators to select data exclusively. In this case, you cannot use the BETWEEN operator.

1

2

3

4

5

6

SELECT

    productName, unitPrice

FROM

    products

WHERE

    unitPrice > 18 and unitPrice < 19;

The query returns 1 row instead 7 rows.



SQL BETWEEN with date examples

You can use the BETWEEN operator to select employees who were born between  01-Jan-1948 and  01-Jan-1960 as follows:

1

2

3

4

5

6

SELECT

    lastname, firstname, birthdate

FROM

    employees

WHERE

    birthdate BETWEEN '1948-01-01' AND '1960-01-01';



In case the column that you want to compare is a DATETIME column, the following expression:

1

dt BEETWEEN '1980-01-01' AND '1980-01-02';

is translated as:

1

dt BEETWEEN '1980-01-01 00:00:00.000000 AND '1980-01-02 00:00:00.000000';

Because the time part is not specified in the date literals, the database engine uses 12:00:00 AM as the default time. It means any row that contains a time part after 12:00 A.M. on 1980-01-01 is not returned because it is outside the range.