Does BETWEEN Include Endpoints
I was setting the condition (salary >= 1000 AND salary <= 2000) using the greater-than-or-equal-to (>=), less-than-or-equal-to(<=), and AND operators to filter the data in a certain range as inclusive in the SQL table. Then I realized that we can easily use the BETWEEN operator to filter the data within a certain range as inclusive. But in BETWEEN operator we cannot set the range exclusively, which means the
endpoint we give in the range will not be included, So If you want to calculate a range value, you can use the BETWEEN operator. In this article, we will see how does BETWEEN operator include endpoints in SQL.
What is BETWEEN?
BETWEEN is the comparison operator, so we can filter the data based on a certain range. It includes both the starting and ending points of the range in the query results, which means the resulting values include the starting point and end point of the specified range. This operator is always used in conjunction with a WHERE clause to define a filter condition. We have to give two values to define a range(start points and end points), the AND keyword is used to join these two values. So, you can use BETWEEN operator if you want to check start point and end point values together.
SELECT column1, column2, ... FROM table_name WHERE column_name BETWEEN starpoint AND endpoint;
column_name is the column you want to filter, and
endpoint represent the range boundaries. The BETWEEN operator selects rows where the value of
column_name is greater than or equal to
starpoint and less than or equal to
endpoint . The WHERE clause is crucial in this context, as it specifies the condition for filtering the rows based on the specified range.
If you attempt to use the BETWEEN operator without the WHERE clause, it will result in a syntax error, as SQL expects a condition to be specified in the WHERE clause to determine which rows to retrieve from the table.
How does BETWEEN operator include start points?
In the example below the BETWEEN operator has a
1000is appearing in the Salary column, so it includes and displays
Example table: employee
--Filtering Data SELECT * FROM employee WHERE salary BETWEEN 1000 AND 2000;
In the above SQL command, we use the WHERE clause to filter the data by setting the condition in the BETWEEN operator. We have used the SELECT command to display data. If there is any data in the two values that we have given in range i.e.
endpoint then it will also be displayed. Look at the below output, the ‘
1000‘ We have given in
starpoint is also displayed as it is in the salary column.
How does BETWEEN operator include endpoints?
If your endpoint value appears in the column you are checking, it will be added by the BETWEEN operator. For example:
In the example below, the BETWEEN operator has
32000 in the
salary column, so it also includes and displays.
--Filtering Data SELECT * FROM employee WHERE salary BETWEEN 500 AND 2000;
What we can do using BETWEEN operator can also be done using the greater-than-or-equal-to (>=) symbol and less-than-or-equal-to(<=) symbol operators, like:
SELECT * FROM employee WHERE salary >= 1000 AND salary <= 2000;