IN operator assists you in providing multiple values in your
IN operator can be useful when you need to compare your value to a list of values. Often this list could be the result of a query from another table.
SELECT * FROM table_name WHERE column_name IN (value1,value2,...);
SELECT * FROM Individual WHERE LastName IN ('Simpson','Ozzbourne','Flinstone');
You might have noticed that this returns the same result as the following SQL statement:
SELECT * FROM Individual WHERE LastName = 'Simpson' OR LastName = 'Ozzbourne' OR LastName = 'Flinstone';
Yes, we could just have easily used that statement but the SQL
IN statement is more concise.
SQL IN and Subqueries
Now, where the SQL
IN operator becomes really useful is when you need to compare a value against the result of another query.
For example, lets say we have another table called Publisher. This table contains users who are allowed to contribute to the website via an administration console. All users in the Publisher table are also in the Individual table, but not all users in the Individual table are in the Publisher table.
Our task is to return a list of usernames from all publishers who have an access level of Contributor.
You may notice that the usernames are in the Individual table but the access level is stored in the Publisher table. Also, there could potentially be many contributors. This is a good candidate for the SQL
SELECT UserName FROM Individual WHERE IndividualId IN (SELECT IndividualId FROM Publisher WHERE AccessLevel = 'Contributor');
In this example there aren't many records in the Publisher table, but imagine if there were thousands — the
IN statement is great for this sort of thing.