SQL IN Operator
The IN operator allows you to test whether a value falls within a set of values. A set can be a list of literal values, or the result of a subquery.
The IN operator must be followed by a comma-delimited list of valid values enclosed within parentheses.
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);
To help you better understand the examples, and enable you to follow along with the tutorial, we are going to use the following sample table.
This table is part of an ‘Employee Management System’ that contains basic information about employees.
The IN Operator
To use the IN operator specify a comma-delimited list of valid values in parentheses. For example, to select all employees who are either 28, 23 or 26 years old, you can issue:
SELECT * FROM Employees WHERE Age IN (28, 23, 26);
You can use the IN operator with data types other than numbers, such as text. For example, the following query will return a list of all employees who are either ‘Managers’, ‘Developers’ or ‘Supervisors’
SELECT * FROM Employees WHERE Job IN ('Manager', 'Developer', 'Supervisor');
As you can see there is no employee with a job title supervisor, so no record associated with it has been selected.
IN operator Vs OR operator
If you are thinking that the IN operator behaves as if many conditions were joined together with OR, then you are correct. The IN operator is a shorthand for multiple OR conditions.
The following SQL statement works exactly like the example above; it selects all employees who are either 28, 23 or 26 years old.
SELECT * FROM Employees WHERE Age = 28 OR Age = 23 OR Age = 26;
Why use the IN operator if you can get the same result by OR’ing multiple conditions together? This is because:
- The IN operator always executes more quickly than multiple OR operators (when the list is large).
- When the list of valid values is long, the IN operator syntax is very clean and easy to read.
- When IN is used in combination with other AND and OR operators, the order of evaluation is easier to maintain.
- The biggest advantage of IN is that it can contain a subquery, enabling you to generate a set for you on the fly.
NOT IN Operator
By combining the IN operator with the NOT operator, you can select values that are not in the specified set. For example, if you wanted to select all employees who are neither 28, 23 nor 26 years old (the opposite of the previous example), you could run this query:
SELECT * FROM Employees WHERE Age NOT IN (28, 23, 26);
Retrieving Values from One Table That Exist in Another
The biggest advantage of IN is that the IN operator can contain a subquery, enabling you to generate a set for you on the fly. You can use the following syntax:
SELECT column_name(s) FROM table_name WHERE column_name IN (subquery);
Suppose the ‘Client’ table looks like this:
And you wanted to select all employees who are from the same city as the clients, then you could write a query like:
SELECT * FROM Employees WHERE City IN (SELECT City FROM Client);
The subquery returns a set of three values. The IN operator tests the cities from the ‘Employees’ table to see if they are in the list of clients’ cities returned by the subquery. If there is a match that row will be included in the result-set.
Python Example for Beginners
Two Machine Learning Fields
There are two sides to machine learning:
- Practical Machine Learning:This is about querying databases, cleaning data, writing scripts to transform data and gluing algorithm and libraries together and writing custom code to squeeze reliable answers from data to satisfy difficult and ill defined questions. It’s the mess of reality.
- Theoretical Machine Learning: This is about math and abstraction and idealized scenarios and limits and beauty and informing what is possible. It is a whole lot neater and cleaner and removed from the mess of reality.
Data Science Resources: Data Science Recipes and Applied Machine Learning Recipes
Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!
Latest end-to-end Learn by Coding Recipes in Project-Based Learning:
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.