DATA with BARAA

SQL BETWEEN

In this tutorial, you will learn how to check if a value falls within a specific range using the SQL operator BETWEEN within the WHERE clause.

The BETWEEN is one of the logical operators in SQL that allows you to select only rows that are within a specific range.

AND Return True if both conditions are True
OR Return True if one of conditions is True
NOT Reverse the result of any Boolean operator
IN Return True if a value is in a set of values
BETWEEN Return True if a value falls within a specific range
LIKE Return True if a value matches a pattern

The BETWEEN operator returns TRUE if the value is greater than or equal (>=) to the min value and less than or equal (<=) to the max value.

Syntax

The basic syntax of the BETWEEN operator to select values within a given range can be given with:

				
					SELECT column_names
FROM table_name
WHERE column_name BETWEEN min_value AND max_value
				
			
  • The values used in BETWEEN  operator can be numbers, text, or dates.
  • The values used in BETWEEN  operator are included in the condition.

Technically, the BETWEEN is the equivalent to the following Syntax that uses the operators: (>=) and (<=) 

				
					SELECT column_names
FROM table_name
WHERE column_name >= min_value AND column_name <= max_value
				
			
.

BETWEEN Numeric Example

To understand the BETWEEN statement in a better way, let’s look at the following customers table in our tutorial database:

Customers
customer_id first_name last_name country score
1 Maria Cramer Germany 350
2 John Steel USA 900
3 Georg Pipps UK 750
4 Martin MĂĽller Germany 500
5 Peter Franken USA NULL

Now, let’s check out some examples that demonstrate how it actually works.

We have the following task be to solve using SQL statements

Find all customers whose score falls in the range between of 100 and 500

The following SQL statement will returns all customers from customers table and order the result by the score column in ascending order.

				
					SELECT *
FROM customers
WHERE score BETWEEN 100 AND 500
				
			

After executing the above query, you’ll get the result set something like this:

As you can see the output contains everything the whole customers tables including all rows and columns.

				
					SELECT
    *
FROM customers
WHERE score >= 100 AND score <= 500
				
			

Similarly, you can use the DESC option to perform a sorting in descending order. The following statement will orders the result set by the numeric salary column in descending order.

Some people prefer not using e BETWEEN and use the second variant like the query above, because it more explicitly shows what the query is doing (it’s easy to forget whether or not e BETWEEN includes the range bounds)

BETWEEN Date Example

Find all orders sold between 1st January 2021 and 30st April 2021

As you can see the output contains everything the whole customers tables including all rows and columns.

				
					SELECT *
FROM orders
WHERE order_date between '2021-01-01' AND '2021-04-30'
				
			
.
Share it !