Preview
Video Player
00:00
00:00
Searching Within a Set of Values
2:35Writing multiple conditions could lead to typos and possible errors in our reporting. In this video, we'll take a look at short-hand syntax.
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 IN keyword.
1:27
The IN keyword 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