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

Filtering by Comparing Values

3:58 with Andrew Chalkley

You don't need to just search for equal or unequal values. You can compare them to a value you decide.

Relational Operators

There are several relational operators you can use:

  • < less than
  • <= less than or equal to
  • > greater than
  • >= greater than or equal to

These are primarily used to compare numeric and date/time types.

SQL Used

SELECT <columns> FROM <table> WHERE <column name> < <value>;
SELECT <columns> FROM <table> WHERE <column name> <= <value>;
SELECT <columns> FROM <table> WHERE <column name> > <value>;
SELECT <columns> FROM <table> WHERE <column name> >= <value>;

Examples:

SELECT first_name, last_name FROM users WHERE date_of_birth < '1998-12-01';
SELECT title AS "Book Title", author AS Author FROM books WHERE year_released <= 2015;
SELECT name, description FROM products WHERE price > 9.99;
SELECT title FROM movies WHERE release_year >= 2000;

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

    SQL isn't only used for finding or filtering on exact matches, 0:00
    it can be used to compare values using relational operators. 0:05
    The name of relational operators comes from their 0:09
    usage in comparing the relationship between two values, 0:12
    not because they're used in a relational database. 0:16
    These operators are actually found in many of the programming languages, 0:19
    not just SQL. 0:23
    So let's take a look at the operators we already know. 0:24
    The equality or equal to operator compares the quality of two values. 0:28
    The text of Andrew is the same as the text of Andrew. 0:33
    So this condition is true. 0:37
    The inequality operator, or the not equal to operator, 0:39
    compares the values, and if they don't match, the condition is true. 0:43
    The text of Andrew is not the same as the text of Lauren, so this is true. 0:48
    Now onto the relational operators. 0:54
    You tend to see these operators when comparing numbers and dates. 0:56
    We're going to focus on numbers first. 1:00
    The first one we're going to look at is the less than operator, 1:02
    which is represented by a left angle bracket. 1:06
    All operators read from left to right. 1:09
    If the value on the left-hand side of the operator is smaller than the value on 1:12
    the right side of the operator, the condition will evaluate to true. 1:16
    If the two values are equal to each other, the condition will evaluate to false, 1:21
    because the left value is not smaller than the right value. 1:26
    In this example, all values less than 40, but not including 40, are true. 1:30
    If you wanted to compare values less than or equal to each other, 1:36
    you'd use the less than operator, followed by the equals sign. 1:40
    This is known as the less than or equal to operator. 1:44
    This is just like the less than operator, but 1:48
    it is inclusive of the value on the right hand side. 1:50
    How about creating values greater than a particular value? 1:54
    Well, we use the right angle bracket or the greater than symbol. 1:58
    100 is greater than 40 and 99, but is not greater than 100. 2:02
    However, 100 is greater than or equal to 100. 2:09
    Let's use these new operators to answer some more questions from our database. 2:13
    Let's start with the question what books are in the library 2:18
    that were first published after 2005? 2:22
    Let's start with our general SQL statement. 2:26
    SELECT * FROM books. 2:28
    Then let's add a where clause, 2:34
    first_published is greater than 2005. 2:38
    Notice how Harry Potter and the Half Blood Prince wasn't selected, 2:45
    since it's first published year was in 2005. 2:52
    2005 isn't greater than itself, it is equal to itself. 2:55
    If we wanted to include books from 2005 too, we should use the greater than or 3:01
    equal to operator to get all books from 2005 onwards. 3:06
    Finally, let's find some classics in our library. 3:11
    Let's find all books released before the 20th century. 3:14
    How would you change this query? 3:18
    That's right. 3:21
    We would change the greater than or equal to operator to be the less than 3:23
    operator and the value 1900. 3:29
    Nice. 3:35
    Obviously, 1900 is the beginning of the 20th Century. 3:37
    But if we wanted to include 1900, we'd use the less than or equal to operator. 3:40
    Our results are the same since we have no books in our library from 1900. 3:47
    With these comparison operators under your belt, 3:50
    you can now filter your data in more exciting ways. 3:54

    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