SQL - Between

BETWEEN is a conditional statement found in the WHERE clause. It is used to query for table rows that meet a condition falling between a specified range of numeric values. It would be used to answer questions like, "How many orders did we receive BETWEEN July 20^th and August 5^th?"

SQL Select Between:

USE mydatabase;

SELECT *
FROM orders
WHERE day_of_order BETWEEN '7/20/08' AND '8/05/08';

SQL Results:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	4
2	Tia	2008-08-01 00:00:00.000	Stapler	1
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	3
6	Tia	2008-07-25 00:00:00.000	HP Printer	2

BETWEEN essentially combines two conditional statements into one and simplifies the querying process for you. To understand exactly what we mean, we could create another query without using the BETWEEN condition and still come up with the same results, (using AND instead).

SQL Select Between:

USE mydatabase;

SELECT *
FROM orders
WHERE day_of_order >= '7/20/08'
AND day_of_order <= '8/05/08';

SQL Results:

id	customer	day_of_order	product	quantity
1	Tia	2008-08-01 00:00:00.000	Pen	4
2	Tia	2008-08-01 00:00:00.000	Stapler	1
5	Tia	2008-07-25 00:00:00.000	19" LCD Screen	3
6	Tia	2008-07-25 00:00:00.000	HP Printer	2

As you can see from comparing the results of these two queries, we are able to retrieve the same data, but you may find BETWEEN easier to use and less cumbersome than writing two different conditional statements. In the end, the preference is really up to the individual writing the SQL Code.

Posted in: SQL