Searching Within a Set of Values

2:35 with Andrew Chalkley

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.

Teacher's Notes
Video Transcript
Downloads

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.

0:00
Our patrons are being particularly clumsy today.
0:02
Three people have dropped their library cards.
0:05
Their cards only have their ID on them.
0:07
The three IDs look like this.
0:09
[SOUND] We'll need to use them to help us find the patrons in our database.
0:16
Let's remind ourselves of the structure of the patrons table.
0:20
We need to select the first name and email address, and
0:26
use their library ID and compare the three values.
0:31
We need to write the WHERE clause with three conditions separated by ORs.
0:58
Great!
0:59
We found our clumsy patrons.
1:01
But, imagine if there were significantly more values that we had to search through.
1:05
Typing all of the conditions where you wanted to check the same column
1:09
seems a little verbose.
1:11
And there's often a possibility of a typo happening.
1:14
Luckily, SQL allows us to compare multiple values using the IN keyword.
1:19
The syntax looks like this.
1:21
After the WHERE keyword,
1:23
you include the column you want to compare your values with.
1:27
Then the end key word.
1:29
The end key word is followed by all the values you want to check against.
1:34
You separate the values with a comma.
1:37
Finally, you wrap all of your values in a pair of parentheses.
1:41
So, our original query can be written as, Select first_name,
1:47
email FROM patrons, WHERE library_id, in, and then the values.
2:06
And it retrieves the same results.
2:08
You may be asking, what's the opposite of this query?
2:12
What if we wanted to reward our more careful patrons for
2:15
not losing their cards?
2:17
We can use the NOT keyword right before the IN keyword.
2:21
So this reads, select the first_name and email address
2:25
FROM the patron's table where the library_id is NOT IN this set of values.
2:32
And our least clumsy patron is returned.
2:34
Good on you, Dave.

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

SQL Basics

Searching Within a Set of Values

SQL Used

Sign in