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
András Novoszáth
1,283 PointsDifference between IN and Between?
Update: I rewrote this question so it includes the whole quiz.
"Which keyword could you use to rewrite this query in a shorter form?"
SELECT <columns> FROM <table> WHERE <column 1> = <value 1> OR <column 1> = <value 2> OR <column 1> = <value 3>;
Possible answers:
- BETWEEN
- ALL
- IN
It accepts "IN" as the correct answer. My question is whether it should not be the opposite?
As I understand BETWEEN 1 AND 3 would define a range of values while IN (column_name_1, column_name_2) is for selecting from among multiple columns.
Jason Anello
Courses Plus Student 94,610 PointsSorry, András.
I didn't realize you had updated this. We don't receive notifications when there's an edit.
I would recommend that you leave a quick comment to your question mentioning that you've updated your question so that everyone who is subscribed to it will be notified.
2 Answers
Jason Anello
Courses Plus Student 94,610 PointsYour understanding of BETWEEN is correct but for IN you're not putting multiple columns in the parentheses. You're specifying values in the parentheses.
The difference between IN and BETWEEN is that BETWEEN is checking for a range of continuous values and IN is checking a set of values. It's basically continuous values vs. discrete values.
The example you posted is checking if the value in column 1 is any of those 3 discrete values. IN can be used here because you're checking against a set of 3 values.
BETWEEN can't be used here because you'll have to specify a min and max and it will check for the continuous range of values between min and max instead of checking for only those 3 specific values like in the example.
If it helps, we can work with actual values.
Let's suppose that column 1 is storing numbers and the 3 values in the example are 1, 5, 10
The where clause looks like
WHERE <column 1> = 1 OR <column 1> = 5 OR <column 1> = 10;
We want all results where the column 1 value is either 1 or 5 or 10. That's a set of 3 discrete values.
Changing it to use IN():
WHERE <column 1> IN(1, 5, 10);
This is going to check if the column 1 value is any of those 3 values. It's doing the same thing as the OR conditions.
On the other hand if we try it with between:
WHERE <column 1> BETWEEN 1 AND 10;
This is going to return results where the value of column 1 is anything in the continuous range of values from 1 to 10.
This means that if there is a row where column 1 has a value of 7, it will be returned.
But in the original example, we definitely don't want the result if column 1 is 7.
András Novoszáth
1,283 PointsWow! This is great!
Yes, this definitely cleared it up for me.
Many thanks!
Derek Gannon
Courses Plus Student 1,140 PointsI guess the technical answer is "it depends." I prefer the IN keyword as I can query multiple columns at once with values selected within parentheses. The IN keyword, at least for me, truncates the query to a smaller, more efficient search. Which in turn can speed up the query times within the database. Especially if the DB has alot of users. In the end it boils down to personal preference of the end user or DB admin.
András Novoszáth
1,283 PointsSorry, I still don't get it:
In the above example, the original query sets the simultaneous condition of three different values, but it does this in reference of the same column.
So, based on my understanding of the working of "IN", it cannot produce the right result.
Or, maybe I miss something, but then the question is what is it? :)
Jason Anello
Courses Plus Student 94,610 PointsJason Anello
Courses Plus Student 94,610 PointsHi András,
Which question are you referring to?