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
Writing multiple conditions could could lead to typos and possible errors in our reporting. In this video we'll take a look at a short hand syntax.
SQL Used
SELECT <columns> FROM <table> WHERE <column> IN (<value 1>, <value 2>, ...);
Examples:
SELECT name FROM islands WHERE id IN (4, 8, 15, 16, 23, 42);
SELECT * FROM products WHERE category IN ("eBooks", "Books", "Comics");
SELECT title FROM courses WHERE topic IN ("JavaScript", "Databases", "CSS");
SELECT * FROM campaigns WHERE medium IN ("email", "blog", "ppc");
To find all rows that are not in the set of values you can use NOT IN.
SELECT <columns> FROM <table> WHERE <column> NOT IN (<value 1>, <value 2>, ...);
Examples:
SELECT answer FROM answers WHERE id IN (7, 42);
SELECT * FROM products WHERE category NOT IN ("Electronics");
SELECT title FROM courses WHERE topic NOT IN ("SQL", "NoSQL");
See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.
Our patrons are being
particularly clumsy today.
0:00
Three people have dropped
their library cards.
0:02
Their cards only have their ID on them.
0:05
The three IDs look like this.
0:07
[SOUND] We'll need to use them to help
us find the patrons in our database.
0:09
Let's remind ourselves of
the structure of the patrons table.
0:16
We need to select the first name and
email address, and
0:20
use their library ID and
compare the three values.
0:26
We need to write the WHERE clause with
three conditions separated by ORs.
0:31
Great!
0:58
We found our clumsy patrons.
0:59
But, imagine if there were significantly
more values that we had to search through.
1:01
Typing all of the conditions where
you wanted to check the same column
1:05
seems a little verbose.
1:09
And there's often a possibility
of a typo happening.
1:11
Luckily, SQL allows us to compare
multiple values using the IN keyword.
1:14
The syntax looks like this.
1:19
After the WHERE keyword,
1:21
you include the column you want
to compare your values with.
1:23
Then the end key word.
1:27
The end key word is followed by all
the values you want to check against.
1:29
You separate the values with a comma.
1:34
Finally, you wrap all of your
values in a pair of parentheses.
1:37
So, our original query can be written as,
Select first_name,
1:41
email FROM patrons, WHERE library_id,
in, and then the values.
1:47
And it retrieves the same results.
2:06
You may be asking,
what's the opposite of this query?
2:08
What if we wanted to reward
our more careful patrons for
2:12
not losing their cards?
2:15
We can use the NOT keyword
right before the IN keyword.
2:17
So this reads,
select the first_name and email address
2:21
FROM the patron's table where the
library_id is NOT IN this set of values.
2:25
And our least clumsy patron is returned.
2:32
Good on you, Dave.
2:34
You need to sign up for Treehouse in order to download course files.
Sign up