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
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.
What happens when you want to
use more than one condition?
0:00
Depending on what question you want
to ask, you can use either the AND
0:03
keyword or the OR keyword.
0:08
Let's look at a general example
first with one condition.
0:10
To add a second condition you
add the appropriate keyword,
0:14
then the next condition.
0:17
Let's ask a question that
includes two conditions.
0:21
What books in our library were authored
by J.K. Rowling before the year 2000?
0:24
Let's remind ourselves of
the table's structure.
0:30
We want to select the title, and
then we want to compare the author and
0:33
the first_published columns.
0:37
So, SELECT title FROM books
0:40
WHERE author = "J.K.
0:46
Rowling" AND
0:52
first_published < 2000.
0:56
Using the AND keyword means that
the row has to satisfy both conditions.
1:09
If I were to swap it out with an OR,
it will retrieve books by J.K.
1:14
Rowling OR
books first published before 2000.
1:20
As you can see, using the wrong
keyword when combining conditions can
1:24
present very different results.
1:29
Let's ask another question.
1:31
What books do we have in the library
authored by Ernest Klein or Andy Weir?
1:33
Let's select author this time too.
1:39
WHERE author = "Ernest Cline" OR
1:43
author = "Andy Weir".
1:50
We use OR rather than AND because the AND
tests if every condition is satisfied.
2:04
Since the author cannot be both,
Ernest Cline or
2:10
Andy Weir at the same time,
no results will come back.
2:14
If we use OR
we don't mind which condition is true.
2:18
As long as one of the conditions is true,
the row will be returned.
2:21
To recap, the AND and OR keywords can
be used to chain conditions together.
2:26
With the AND
keyword all conditions need to be met.
2:32
With the OR keyword,
only one condition needs to be met.
2:36
Combining the AND and OR keywords with
multiple conditions can get complex.
2:40
I'll include some examples and details in
the teacher's notes for you to refer to.
2:46
You need to sign up for Treehouse in order to download course files.
Sign up