Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
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