Blogging Tips

SQL: the IN operator

The IN operator can be very useful when we need to perform SQL queries where we need to filter the results with more than one possible value.
Just to understand the power of the IN operator,  we need to go back a few steps and see how we usually filter data from a table with the OR operator.

The OR operator is what we normally use to filter data using different values. For example, we can have a table with a list of users. One of the table fields is the first name ("name"). If we want to filter data, and get all the users that have 'John' and 'James' as name, we will create a query like:

SELECT * FROM users
WHERE name = 'John' OR name = 'James'

The above query is just an example, but I believe it is the usual way we are used to filter data with multiple values. But it's not the only way of doing it.

Using the IN operator is much easier and, let me say it, more elegant (if that matters!). Let's see how we could write the above query, using it:

SELECT * FROM users
WHERE name IN ('John', 'James')

As you can see the query is cleaner. It is important to note that the list of possible matching values is just a comma separated list. I believe you understand the strenght of such a solution. For example, if we have a multiselect listbox on our page, we can join the selected options and pass the values to our query to obtain the results.

There's something more I would like you to consider. When we filter results, we sometimes use the LIKE operator, especially when we need to use only part of a string as filtering value. For example in the first above query, we could filter data by names starting with 'J' or 'S':

SELECT * FROM users
WHERE name LIKE 'J%' OR name LIKE 'S%'

On the other hand, the following query won't work the same way:

SELECT * FROM users
WHERE name IN ('J%', 'S%')

So what can we do? We can use SOUNDEX. In fact, a correct way for the query using the IN operator will be:

SELECT * FROM users
WHERE SOUNDEX(name) IN (SOUNDEX('J'), SOUNDEX('S'))

And that's it. I hope you found the article useful. If you like, please leave a comment in the section below.