SQL BETWEEN
Summary: In this tutorial, you will learn how to use SQL BETWEEN operator in WHERE clause to query the records which have values in a range of values.
SQL BETWEEN operator allows you to retrieve records which have values in a range of values.
The syntax of SQL BETWEEN is as follows:
SELECT column_list
FROM table_name
WHERE column BETWEEN lower_value AND upper_value
In the query above, SQL will give you all the records which the column's value in WHERE clause between lower_value and upper_value
For example you want to retrieve products which have unit price from $18 to $19, you can use SQL BETWEEN operator like the following query:
SELECT productName, unitPrice
FROM products
WHERE unitPrice BETWEEN 18 AND 19
You can reconstruct SQL BETWEEN by using less than or equal (<= ) and greater than or equal (>=) operators as follows:
SELECT productName, unitPrice
FROM products
WHERE unitPrice >= 18 AND unitPrice <= 19
Like SQL IN operator, SQL BETWEEN operator can combine with NOT operator to find records that does not have values in a particular range.
For example, to find all products which does not have unit price between $18 and $19, you can combine SQL NOT operator with SQL BETWEEN operator. Here is the query:
SELECT productName, unitPrice
FROM products
WHERE unitPrice NOT BETWEEN 18 AND 19
In this tutorial, you’ve learned how to use SQL BETWEEN operator to find values which are in a range of values. In addition you’ve also learned how to combine SQL NOT operator and SQL BETWEEN operator to find all records which have value is not in a range of values.
Related Tutorials