Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Searching Within a Set of Values2: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.
SELECT <columns> FROM <table> WHERE <column> IN (<value 1>, <value 2>, ...);
To find all rows that are not in the set of values you can use
SELECT <columns> FROM <table> WHERE <column> NOT IN (<value 1>, <value 2>, ...);
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.
Three people have dropped their library cards.
Their cards only have their ID on them.
The three IDs look like this.
[SOUND] We'll need to use them to help us find the patrons in our database.
Let's remind ourselves of the structure of the patrons table.
We need to select the first name and email address, and
use their library ID and compare the three values.
We need to write the WHERE clause with three conditions separated by ORs.
We found our clumsy patrons.
But, imagine if there were significantly more values that we had to search through.
Typing all of the conditions where you wanted to check the same column
seems a little verbose.
And there's often a possibility of a typo happening.
Luckily, SQL allows us to compare multiple values using the IN keyword.
The syntax looks like this.
After the WHERE keyword,
you include the column you want to compare your values with.
Then the end key word.
The end key word is followed by all the values you want to check against.
You separate the values with a comma.
Finally, you wrap all of your values in a pair of parentheses.
So, our original query can be written as, Select first_name,
email FROM patrons, WHERE library_id, in, and then the values.
And it retrieves the same results.
You may be asking, what's the opposite of this query?
What if we wanted to reward our more careful patrons for
not losing their cards?
We can use the NOT keyword right before the IN keyword.
So this reads, select the first_name and email address
FROM the patron's table where the library_id is NOT IN this set of values.
And our least clumsy patron is returned.
Good on you, Dave.
You need to sign up for Treehouse in order to download course files.Sign up