Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases SQL Basics Finding the Data You Want Searching Within a Set of Values

Posted by A X
A X
12,842 Points

Is IN just a substitute for using OR multiple times?

I'm curious in this video if IN only refers to a substitute for using the OR keyword multiple times...and if I understand correctly, NOT IN is a substitute for using AND multiple times in a query. Or am I mistaken?

3 Answers

Steven Parker
Steven Parker
231,271 Points
Steven Parker
Steven Parker
231,271 Points

:point_right: You're half right. The basic use of IN is equivalent to multiple equality tests against the first value, combined using OR. Essentially both are asking "are ANY of these true?".

On the other hand, NOT IN is different from combining equality tests with AND. In the case of NOT IN, you are asking "are NONE of these true?". But using AND is like asking "are ALL of these true?"

Now if you were to combine inequality tests with AND, then it would be the same as using NOT IN.

Iain Simmons
MOD
Iain Simmons
Treehouse Moderator 32,305 Points
Iain Simmons
Iain Simmons
Treehouse Moderator 32,305 Points

I think the real power comes from having sub-queries in within the parentheses for the IN, where you can have a huge number of possible results to compare against. Here's a totally made up example:

SELECT id, first_name FROM patrons WHERE first_name IN (SELECT first_name FROM staff_members);

The sub-query doesn't even have to have the same column name in the SELECT statement, you just need to be able to return compatible data types and number of columns to get the results you want.

Jason Anello
PLUS
Jason Anello
Courses Plus Student 94,610 Points
Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

Hi abbymann,

Yes, NOT IN can be a substitute for the AND operator.

If you have something like

column_name != value1 AND column_name != value2

then that could be rewritten as 

column_name NOT IN (value1, value2)

Here's some example queries that you could run and experiment with on the patrons table in the sql playground associated with this video.

The first 2 queries will return the rows for Andrew and Dave.

select id, first_name from patrons where first_name IN ("Andrew", "Dave");

select id, first_name from patrons where first_name = "Andrew" OR first_name = "Dave";

These 3 queries will return all the patrons that are not named Andrew or Dave. This happens to be Alena and Michael.

select id, first_name from patrons where first_name NOT IN ("Andrew", "Dave");

select id, first_name from patrons where NOT (first_name IN ("Andrew", "Dave"));

select id, first_name from patrons where first_name != "Andrew" AND first_name != "Dave";

So NOT IN () can be a substitute for the AND operator provided that you're checking if the column is not equal to several different values.