Friday, 20 January 2012

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
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:
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.

The IN operator
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:
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.

Other considerations
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':
WHERE name LIKE 'J%' OR name LIKE 'S%'
On the other hand, the following query won't work the same way:
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:

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

0 thoughts:

Post a Comment

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.