Let’s get a handle on one of its powerful operators, the SQL IN operator. I’m going to keep this as simple as possible, while still covering everything you need to know.

What is the SQL “IN” Operator?

In SQL, the IN operator allows you to specify multiple values in a WHERE clause. Essentially, it’s a shorthand for multiple OR conditions. This might not seem like a big deal, but when you’re dealing with complex databases with many records, this small efficiency can have a huge impact.

Basic Syntax of SQL IN

The basic syntax of SQL IN is pretty straightforward

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

In this syntax, you can see that the IN operator is used in the WHERE clause to filter the records. The values in the parentheses (value1, value2, …) represent the values you’re interested in.

A Practical Example of SQL “IN”

Let’s illustrate the SQL IN operator with an example. Suppose you’re working with a table called Employees

Employee_ID | First_Name | Last_Name | Department
----------------------------------------------
1           | John       | Doe       | IT        
2           | Jane       | Smith     | Marketing 
3           | Sara       | Jones     | HR        
4           | James      | Davis     | IT        
5           | Linda      | Taylor    | Sales

Imagine that you want to find the details of employees who work in either the ‘IT’ or ‘Sales’ department. You could certainly use the OR operator, like so

SELECT *
FROM Employees
WHERE Department = 'IT' OR Department = 'Sales';

However, using the IN operator simplifies this query

SELECT *
FROM Employees
WHERE Department IN ('IT', 'Sales');

Both of these queries return the same results

Result:

Employee_ID | First_Name | Last_Name | Department
----------------------------------------------
1           | John       | Doe       | IT        
4           | James      | Davis     | IT        
5           | Linda      | Taylor    | Sales

As you can see, the IN operator is not only easier to write but also makes your SQL statement cleaner and easier to read.

SQL IN with Subquery

Another way you can use the IN operator is with a subquery. A subquery is a SQL query nested inside a larger query. Let’s say you have another table called Departments

Employees Table:

Employee_ID | First_Name | Last_Name | Department
----------------------------------------------
1           | John       | Doe       | IT        
2           | Jane       | Smith     | Marketing 
3           | Sara       | Jones     | HR        
4           | James      | Davis     | IT        
5           | Linda      | Taylor    | Sales

Departments Table:

Department_ID | Department | Manager
-------------------------------------
1             | IT         | John Doe
2             | Marketing  | Jane Smith
3             | HR         | Sara Jones
4             | Sales      | Linda Taylor

And you want to find all employees who are managers. You could do this using the IN operator with a subquery

SELECT *
FROM Employees
WHERE First_Name + ' ' + Last_Name IN (SELECT Manager FROM Departments);

Result:

Employee_ID | First_Name | Last_Name | Department
----------------------------------------------
1           | John       | Doe       | IT        
2           | Jane       | Smith     | Marketing 
3           | Sara       | Jones     | HR        
5           | Linda      | Taylor    | Sales

This query first retrieves the list of managers from the Departments table, then uses the IN operator to filter the Employees table based on that list.

Conclusion

That’s the basic idea behind the SQL IN operator. It provides an efficient way to check if a certain column’s value is in a list of specified values. Its biggest strengths are its readability and its compatibility with subqueries, which are essential when working with large, complex databases.

Related Topics

• Machine Learning Plus SQL Like