Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

SQL Basics

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Searching Within a Range of Values

2:57 with Andrew Chalkley

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.

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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, author FROM books WHERE 0:23
    first_published >= 1800 AND 0:28
    first_published <= 1899. 0:39
    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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up