Get Better Database Results with the SQL IN Statement
Databases let us organize our lives like nothing else. Whether for work or play, a properly normalized SQL database makes life easier. We can store just about any information in them, and retrieve it later with gusto. While these databases let us store anything in them, their power comes from the SQL language. You can do anything with the right SQL statement. These statements can be ridiculously simply or unfathomably complex depending on your needs. For instance, if you need to retrieve records containing certain specific pieces of information in the same column, you have two options. You can either include them as separate “OR” command conditions or you can use a single SQL “IN” statement to grab them all in one line.
The SQL IN Statement
The SQL IN statement replaces the need for having multiple OR conditions. For instance, let’s say you have the following table in your SQL database.
If you only need the records for, let’s say, L. A. and San Diego, you could use multiple OR statements to get them. These statements would like something like this:
SELECT * FROM Store_Information WHERE Store_Name = ‘Los Angeles’ OR Store_Name = ‘San Diego’;
There is nothing wrong with the statement. You can use it if you want; especially for small tables like this one. However, using multiple OR statements can get obnoxious for larger tables. For a table with hundreds of entries, your SQL OR chain can get as long as a book if you need more than just a few results. Fortunately, we have the SQL IN statement to streamline our SQL.
The SQL IN statement changes all those OR conditions into a single statement featuring a comma-separated list of values. These values are your search terms enclosed in parentheses. The SQL IN statement syntax looks something like this:
Expression IN (value1, value2, …, value_n)
For a practical example, we can rewrite our previous SQL statement as:
SELECT * FROM Stroe_Information WHERE Store_Name IN (‘Los Angeles’, ‘San Diego’);
You can put anything you want in the values list as long as it all the same data type. Because you are dealing with a single database column, you cannot mix data types such as string and numbers. The value data types must also match the data type of your column. The following is an example of an all number SQL IN statement.
SELECT * FROM orders WHERE order_id IN (10000, 10001, 10003, 10005);
In any of these cases, the SQL IN statement produces the same result as its equivalent OR chain.
The SQL NOT IN Statement
Sometimes, you know what you don’t want more than what you want. For these times, you can add the keyword NOT to your SQL IN statements. NOT IN works is the reverse of the IN statement. It returns all records that do not include any item in your values list. For instance, we could get the same result as before if we used the following NOT IN statement.
SELECT * FROM Stroe_Information WHERE Store_Name NOT IN (‘Boston’, ‘San Francisco’);
The NOT IN statement is equivalent to a chain of AND inequality statements. We could have written the above statements as the following example, but why should we when NOT IN is so simple to use!
SELECT * FROM Store_Information WHERE Store_Name != ‘Boston’ AND Store_Name != ‘San Francisco’;
Using both the IN and NOT IN statements together
That is all you need to know to use the SQL IN statement and its cousin the NOT IN statement in your SQL queries. You just have to remember that these statements work only on one and only one database column. That means your values must be of the same data type, but that is your only restriction.
You can use these statements to retrieve data from one table base on another. The values in the values list can be column names instead of literals as I have done here though that can get quite complicated. Most likely, you will use this scenario when you are combining two or more tables from a normalized database.
For example, if you need only a select few records form a table, but you only know the records of another one, you can use combine the two tables in your SQL statement and use the IN clause to filter the resultant data. The following statement draws all the records from the table orders that have corresponding records in the suppliers table.
SELECT orders.* FROM orders INNER JOIN suppliers ON suppliers.ID = orders.suppierID WHERE suppliers.supplier_name IN ( 'IBM', 'Hewlett Packard', 'Microsoft');
Finally, you can use multiple version of the SQL IN statement to solve even more complicated tasks. This makes working with multiple tables with multiple conditions a snap! You just have to link each SQL IN statement with a logical operator like I did below.
SELECT * FROM orders INNER JOIN suppliers ON suppliers.ID = orders.suppierID WHERE suppliers.supplier_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft') AND order_id IN (10000, 10001, 10003, 10005);
The Bottom Line
While you can store any information you want in a database, they do have their limits. To overcome these limits, the vast SQL language gives us tools to put things back in order with a single statement. When you know you need information based on some criteria, you can place as many conditional statements into the WHERE clause of your SELECT statements. However, when these criteria involve a single category of data, you can use the SQL IN statement to reduce the lengths of your SQL statements.
The SQL IN statement is a simple list of values containing the information you want. You can even add the key word NOT to create a NOT IN statement to discard any records you don’t want. In either case, you will love what the SQL IN statement can do for you.
Database Developer Tools students also learn
Empower your team. Lead the industry.
Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.