Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Again, writing shorter queries can help with readability and tracking down mistakes in our SQL code. In this video we'll go over the syntax of handling ranges of values in SQL.
SQL Used
SELECT <columns> FROM <table> WHERE <column> BETWEEN <lesser value> AND <greater value>;
Examples:
SELECT * FROM movies WHERE release_year BETWEEN 2000 AND 2010;
SELECT name, description FROM products WHERE price BETWEEN 9.99 AND 19.99;
SELECT name, appointment_date FROM appointments WHERE appointment_date BETWEEN "2015-01-01" AND "2015-01-07";
See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.
We saw in the previous video how we can
use a new keyword to reduce the complexity
0:00
of some of our queries.
0:05
We reduced multiple OR
conditions to a single in condition.
0:07
Here's another example.
0:11
Let's say we wanted to find all books
in our library from the 19th century.
0:12
Let's remind ourselves of
the book tables schema.
0:16
We could do something like,
0:21
SELECT title,
0:26
author FROM books WHERE
0:29
first_published >= 1800 AND
0:34
first_published <= 1899.
0:41
What we've built it on
is known as a range.
0:49
There's a minimum value and
a maximum value.
0:51
If the value fits in this range,
the condition is met.
0:54
There's a way to express a range
in clearer terms by using
0:58
the BETWEEN keyword.
1:03
Here's a syntax of using
the BETWEEN keyword.
1:05
In the WHERE clause,
1:10
you first state the column you want
to test in a particular range.
1:12
Then you type the keyword BETWEEN,
followed by the first value.
1:16
Then the AND keyword, and
then the second value.
1:21
In our case we want our first
published BETWEEN 1800 AND 1899.
1:26
And it returns the same results.
1:32
The lower value has to be first.
1:35
Swapping these like this.
1:37
Will yield no results.
1:46
BETWEEN can be used for
more than numeric values.
1:48
It's often used for date ranges too.
1:51
For example, in our loans table we can
set for all loans in a particular week.
1:54
Let's see all loans from a week in
December 2015, starting Sunday the 13th.
2:00
Let's remind ourself of the loan schema.
2:06
We can write, SELECT * FROM
2:10
loans WHERE loaned_on BETWEEN
2:15
"2015-12-13 AND
2:20
"2015-12-19".
2:25
And those are the two loans that
needed to be returned that week.
2:39
As you can see,
2:43
the BETWEEN keyword simplifies queries
where you're dealing with ranges.
2:43
You don't have to think
about operators at all.
2:49
You can just say BETWEEN and the minimum
and maximum values of your range.
2:52
You need to sign up for Treehouse in order to download course files.
Sign up