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

Keyword IN usage

Hi,

I wanna make it clear if the keyword IN (value1, value2, ...) is only used for values related to OR keyword. What if I wanna have muptiple values related to AND keyword? Is there any shorthand syntax that I can use?

And here is one more thing. Does keyword IN subsitute only equality as a operator? Or can this also subsitute other operator for instance like less than, greater than.

2 Answers

Steven Parker
Steven Parker
229,606 Points

The AND and OR operators are logical combiners, they are used with complete comparison expressions or boolean values. On the other hand, IN is a set membership operator, it determines if a single value is part of a set.

Since a single value can only match one item in a set of unique values, an operator that requires it to match all of them would not be useful.

And yes, membership is essentially an equality comparison. If you need to make an inequality comparison with a set, you only need to compare with the smallest value of the set (for less than) or the largest value (for greater than).

if you had a db moods with tables start and finish and you wanted to see all rows where the mood started happy or neutral and ended happy or neutral, you could use this query: SELECT * FROM moods WHERE start IN ("happy", "neutral") AND finish IN ("happy", "neutral")