|
Sometimes we need to check if a value or expression is between two other values. For example, checking if a number is between two other numbers, or if a date is between two other dates.
One way to do this is to write two separate WHERE clauses:
SELECT *
FROM order
WHERE order_date <= TO_DATE('2018-12-31', 'YYYY-MM-DD')
AND order_date >= TO_DATE('2018-01-01', 'YYYY-MM-DD');
This will find orders that have an order date between 1st Jan and 31st Dec, 2018.
There is an easier way to do this though: using the BETWEEN keyword.
The BETWEEN keyword allows you easily filter data that is in a range between two values.
It looks like this:
WHERE expression BETWEEN value1 AND value2
This will check if your expression (e.g. a column) is between the two values mentioned. These values are inclusive: if the expression is equal to the value, it is included.
So, to rewrite our earlier query using BETWEEN, it would look like this:
SELECT *
FROM order
WHERE order_date BETWEEN TO_DATE('2018-01-01', 'YYYY-MM-DD')
AND TO_DATE('2018-12-31', 'YYYY-MM-DD');
I think it’s easier to read, and it’s clearer that you’re checking if a value is between two dates, which can often be overlooked if separate WHERE clauses are used.
It can also be used with numbers.
SELECT *
FROM order
WHERE order_amount BETWEEN 100 AND 500;
This will show all orders where the order_amount is greater than or equal to 100, and less than or equal to 500.
More Information
Copyright de Ben Brumm - DatabaseStar
|
