SQL In

The SQL IN operator assists you in providing multiple values in your WHERE clause. This can provide very 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.

SQL Syntax

SELECT * FROM table_name
WHERE column_name IN (value1,value2,...)
 

Example

SQL statement

SELECT * FROM Individual
WHERE LastName IN ('Simpson','Ozzbourne','Flinstone')
 

Source Table

Id FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike

Result

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
4 Ozzy Ozzbourne sabbath

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.

Source Tables

Individual Table

IndividualId FirstName LastName UserName
1 Fred Flinstone freddo
2 Homer Simpson homey
3 Homer Brown notsofamous
4 Ozzy Ozzbourne sabbath
5 Homer Gain noplacelike

Publisher Table

IndividualId AccessLevel
1 Administrator
2 Contributor
3 Contributor
4 Contributor

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 IN operator!

SQL statement

SELECT UserName FROM Individual
WHERE IndividualId IN
(SELECT IndividualId FROM Publisher WHERE AccessLevel = 'Contributor')
 

Result

UserName
homey
notsofamous
sabbath
%d bloggers like this: