A Comprehensive Look at the SQL IN Statement

Demystifying IN

The SQL IN operator is sometimes referred to as a SQL IN function, most probably because its syntax looks like a function. The SQL IN keyword as used in SQL statements is actually from a group of operators in SQL classified as comparison operators.

Basic Syntax

The basic syntax of the SQL IN operator in use in a typical SQL statement is as follows:

SELECT column(s) FROM table WHERE column IN (value1, value2, … valuen);

So What Exactly Does the SQL Statement Above Do?

It allows you to extract the information from desired columns mentioned from a particular table where the contents in column that has been specified after the WHERE clause is equal to any of the values listed within the brackets.

The use of IN in SQL statements within the context mentioned above is very important in that it makes your SQL statements more compact, improves readability, makes it easier to maintain and improves performance.

The context in which these four attributes suggest that there is an alternative to using the SQL IN operator. Rightly so. There is an alternative via the SQL OR operator. An example on how to use the SQL OR operator from the same statement above is as follows:

SELECT column(s) FROM table WHERE column = value1 OR column = value2 … OR column = valuen;

You can see from this example of the simplest form of the SQL IN operator use it takes more effort and space to write it out. Furthermore on larger tables, using the SQL IN operator is clearly more efficient and therefore runs much faster than if you use the SQL OR operator.

Negating the IN Operator

The functionality of the SQL IN operator does not just stop with what we have learned so far. You can actually force this operator to produce the opposite results by combining it with the SQL NOT operator. Going by the same statement used above, if you wanted to pull information off the tables that did where not equal to the values in the list, this is what you would do.

SELECT column(s) FROM table WHERE column NOT IN (value1, value2, … valuen);

This combination of operators comes in handy when you are looking for a very wide array of values which could be the majority of the data in the table. In this case you do not need to mention all the values. You can simply list the fewer values and get the results your expect.

Using SQL Sub-queries

You may also use SQL subqueries to build the list of values you are looking for if they are contained in the same or another table in your database. Suppose you have a table with a unique list of items indexed in some form. This could be something like books of the Bible. These books could link to Bible’s scriptural content lying in another table. Maybe you want to select the verses from a range of books you can simply use SQL IN with SQL subqueries to populate the list for you. Take a look at this SQL query:

SELECT column(s) FROM bible_content WHERE book IN (SELECT book FROM books WHERE id > 5 AND id < 20);

This query will extract the columns specified from the bible_content table. The SQL IN operator will then rely on the result of the sub-query for the list of books to compare with. The list of Bible books is retrieved from the column “book” in the books table where the ids of the columns have been explicitly set. In this example we use all the bible books between id 5 and 20.

You can have a look at the images to have an idea of what the two Bible tables might look like inclusive of the data.

This way you are able to retrieve complex results from unknowns with the power of SQL IN and subqueries.

Additional Tips

When you construct a SQL IN statement, you are not restricted to using just the SELECT statements. You can also use SQL IN to execute CREATE statements especially when you want to create temporary tables or Views. In most cases you will be creating these from tables that exist in you database.

The SQL IN operator can also be used when you want to update your data as it can operate as a powerful feature. DELETE statements are also simplified when using this operator. For the UPDATE and the DELETE statements, it is highly advised that you use simple queries that are easy to read and understand such as IN statement. This is because once data has been modified or deleted you cannot reverse the process unless the operation was done as part of a transaction..

So make it a habit of using SQL IN statements wherever possible in your SQL statements.

References

Source: author’s own experience.

MySQL reference manual, www.mysql.com/documentation/

Images provided by writer.