Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

Preview

Start a free Basic trial
to watch this video

Sign up for Treehouse

Filtering on More than One Condition

2:51 with Andrew Chalkley

You're not restricted to just using one condition, you can test rows of information against multiple conditions. You can choose whether you retrieve rows that match both of your conditions or either of them.

SQL Used

You can compare multiple values in a WHERE condition. If you want to test that both conditions are true use the AND keyword, or either conditions are true use the OR keyword.

SELECT <columns> FROM <table> WHERE <condition 1> AND <condition 2> ...;
SELECT <columns> FROM <table> WHERE <condition 1> OR <condition 2> ...;

Examples:

SELECT username FROM users WHERE last_name = "Chalkley" AND first_name = "Andrew";

SELECT * FROM products WHERE category = "Games Consoles" AND price < 400;

SELECT * FROM movies WHERE title = "The Matrix" OR title = "The Matrix Reloaded" OR title = "The Matrix Revolutions";

SELECT country FROM countries WHERE population < 1000000 OR population > 100000000;

See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.

  • 0:00

    What happens when you want to use more than one condition?

  • 0:03

    Depending on what question you want to ask, you can use either the AND

  • 0:08

    keyword or the OR keyword.

  • 0:10

    Let's look at a general example first with one condition.

  • 0:14

    To add a second condition you add the appropriate keyword,

  • 0:17

    then the next condition.

  • 0:21

    Let's ask a question that includes two conditions.

  • 0:24

    What books in our library were authored by J.K. Rowling before the year 2000?

  • 0:30

    Let's remind ourselves of the table's structure.

  • 0:33

    We want to select the title, and then we want to compare the author and

  • 0:37

    the first_published columns.

  • 0:40

    So, SELECT title FROM books

  • 0:46

    WHERE author = "J.K.

  • 0:52

    Rowling" AND

  • 0:56

    first_published < 2000.

  • 1:09

    Using the AND keyword means that the row has to satisfy both conditions.

  • 1:14

    If I were to swap it out with an OR, it will retrieve books by J.K.

  • 1:20

    Rowling OR books first published before 2000.

  • 1:24

    As you can see, using the wrong keyword when combining conditions can

  • 1:29

    present very different results.

  • 1:31

    Let's ask another question.

  • 1:33

    What books do we have in the library authored by Ernest Klein or Andy Weir?

  • 1:39

    Let's select author this time too.

  • 1:43

    WHERE author = "Ernest Cline" OR

  • 1:50

    author = "Andy Weir".

  • 2:04

    We use OR rather than AND because the AND tests if every condition is satisfied.

  • 2:10

    Since the author cannot be both, Ernest Cline or

  • 2:14

    Andy Weir at the same time, no results will come back.

  • 2:18

    If we use OR we don't mind which condition is true.

  • 2:21

    As long as one of the conditions is true, the row will be returned.

  • 2:26

    To recap, the AND and OR keywords can be used to chain conditions together.

  • 2:32

    With the AND keyword all conditions need to be met.

  • 2:36

    With the OR keyword, only one condition needs to be met.

  • 2:40

    Combining the AND and OR keywords with multiple conditions can get complex.

  • 2:46

    I'll include some examples and details in the teacher's notes for you to refer to.

You need to sign up for Treehouse in order to download course files.

Sign up