{"id":20702,"date":"2021-01-11T04:43:53","date_gmt":"2021-01-11T12:43:53","guid":{"rendered":"https:\/\/careerkarma.com\/blog\/?p=20702"},"modified":"2023-12-01T04:07:57","modified_gmt":"2023-12-01T12:07:57","slug":"sql-in","status":"publish","type":"post","link":"https:\/\/careerkarma.com\/blog\/sql-in\/","title":{"rendered":"SQL IN: A Beginner\u2019s Guide"},"content":{"rendered":"\n

The SQL IN keyword lets you select values that appear in a range of values. You can specify a query inside an IN statement to search for records whose column has a value in the result of a query. The IN keyword appears after a WHERE statement.<\/em><\/p>\n\n\n\n

You can retrieve records whose values are equal to one of multiple possible values from a database using OR conditions. If you\u2019re looking to compare a column to a long list of values, specifying multiple OR conditions can make your query messy.<\/p>\n\n\n\n

There\u2019s another option: the SQL<\/a> IN operator. In this guide, we\u2019re going to talk about what the SQL IN operator is and how it works. We\u2019ll walk through a few examples of this operator in action so that you can learn how to use it.<\/p>\n\n\n\n

What is the SQL IN Operator?<\/h2>\n\n\n\n

The SQL IN operator checks whether a value exists in a set of values. The set of values can either be comma-separated or a query. If you specify a query, that query will execute. Its results will be compared with the column you have stated in the WHERE statement.<\/p>\n\n\n\n

The syntax for the SQL IN operator is:<\/p>\n\n\n\n

SELECT column1, column2 FROM table\nWHERE column1 IN (value1, value2);\n<\/pre><\/div>\n\n\n\n
The IN operator accepts a list of values to which you want to compare to the value in a column separated by commas. You must surround the values in curly brackets. This applies even if you are using a SELECT statement in the IN clause.<\/p>\n\n\n\n
You can specify as many values in an IN statement as you want. This makes them more convenient than using SQL OR statements<\/a> if you want to compare a value to multiple values.<\/p>\n\n\n\n
How to Use the SQL IN Operator<\/h2>\n\n\n\n
We\u2019ve got a database called \u201cemployees\u201d. This database stores information on all the people who work for a pharmaceutical sales business. Our database looks like this:\n<\/p>\n\n\n\n
id<\/strong><\/td>name<\/strong><\/td>title<\/strong><\/td>branch<\/strong><\/td>salary<\/strong><\/td><\/tr>
1<\/td>Thomas Carlton<\/td>Sales Representative<\/td>Cambridge<\/td>29400<\/td><\/tr>
2<\/td>Lisa Nelson<\/td>Senior Sales Representative<\/td>San Francisco<\/td>32000<\/td><\/tr>
3<\/td>Victoria Carlisle<\/td>Sales Director<\/td>Cambridge<\/td>37800<\/td><\/tr>
4<\/td>Julie Forsythe<\/td>Senior Sales Representative<\/td>San Francisco<\/td>32000<\/td><\/tr>
5<\/td>Hallie Peters<\/td>Sales Representative<\/td>Los Angeles<\/td>29400<\/td><\/tr>
6<\/td>Rachel Parsons<\/td>Sales Representative<\/td>San Francisco<\/td>29400<\/td><\/tr><\/tbody><\/table>\n\n\n\n
Let\u2019s retrieve a list of all the employees who work in either Los Angeles or San Francisco. We are going to select the names of each employee, their titles, and the name of the branch at which they are employed.\n\n<\/p>\n\n\n\n
The following SQL SELECT statement<\/a> will retrieve these values from our database:<\/p>\n\n\n\n
SELECT name, title, branch FROM employees\nWHERE branch IN ('Los Angeles', 'San Francisco');<\/pre><\/div>\n\n\n\n
Our statement returns:<\/p>\n\n\n\n
name<\/strong><\/td>title<\/strong><\/td>branch<\/strong><\/td><\/tr>
Lisa Nelson<\/td>Senior Sales Representative<\/td>San Francisco<\/td><\/tr>
Julie Forsythe<\/td>Senior Sales Representative<\/td>San Francisco<\/td><\/tr>
Hallie Peters<\/td>Sales Representative<\/td>Los Angeles<\/td><\/tr>
Rachel Parsons<\/td>Sales Representative<\/td>San Francisco<\/td><\/tr><\/tbody><\/table>\n\n\n\n
Only employees working in the San Francisco or Los Angeles branches are returned.<\/p>\n\n\n\n
A Look at Multiple SQL OR Statements<\/h3>\n\n\n\n
Our above query that retrieves all employees at the San Francisco or Los Angeles branches could have been written using an OR operator:<\/p>\n\n\n\n
SELECT name, title, branch FROM employees\nWHERE branch = 'Los Angeles' OR branch = 'San Francisco';<\/pre><\/div>\n\n\n\n
This statement is syntactically correct. If you\u2019re looking to compare a value in a database to a long list of values, OR statements can become cluttered. Imagine searching through four values. You would need to specify three OR statements in your query.<\/p>\n\n\n\n
For instance, suppose we wanted to retrieve a list of all employees who work at the Cambridge, San Francisco, Los Angeles, and Denver branches. Our query would look like this:<\/p>\n\n\n\n
SELECT name, title, branch FROM employees\nWHERE branch = 'Los Angeles' OR branch = 'San Francisco' OR branch ='Denver' OR branch = 'Cambridge';\n<\/pre><\/div>\n\n\n\n
Using an IN statement would make this query easier to read.<\/p>\n\n\n\n
SQL IN Using SELECT<\/h2>\n\n\n\n
The IN operator accepts the result of an SQL SELECT query.\n\n<\/p>\n\n\n\n
This means that you can compare values in a database to the result of another query. To demonstrate this, we\u2019re going to create a new table: probation.\n\n<\/p>\n\n\n\n
employee<\/strong><\/td>employed_on<\/strong><\/td><\/tr>
1<\/td>2019-07-19<\/td><\/tr>
3<\/td>2020-01-03<\/td><\/tr>
6<\/td>2019-11-23<\/td><\/tr><\/tbody><\/table>\n\n\n\n
Let\u2019s retrieve a list of employees who are currently on probation. Consider the following query:<\/p>\n\n\n\n
SELECT name FROM employees\nWHERE id IN (SELECT employee FROM probation);<\/pre><\/div>\n\n\n\n
This query will select all employees whose employee ID appears in the \u201cprobation\u201d table.\n\n<\/p>\n\n\n\n
Our query returns:<\/p>\n\n\n\n
name<\/strong><\/td><\/tr>
Thomas Carlton<\/td><\/tr>
Victoria Carlisle<\/td><\/tr>
Rachel Parsons<\/td><\/tr><\/tbody><\/table>\n\n\n\n
The query \u201cSELECT employee FROM probation\u201d selects all the IDs of employees who are on probation. The result of this query (1, 3, 6), is then used with the IN operator.\n\n<\/p>\n\n\n\n
Our main query returns a list of all employees whose value is equal to 1, 3, or 6.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
The SQL IN operator verifies if a column is equal to one of many values. You can specify the values to which the column should compare using a comma-separated list. Or, you can specify a query.<\/p>\n\n\n\n
As a challenge, write a query that would retrieve the names of all employees whose title is either:<\/p>\n\n\n\n