The SQL IN Operator
Several values can be specified for a column in a WHERE clause using the SQL IN operator. You can use it to filter data according to whether a certain column’s value corresponds to any values in a given list or subquery.
Let’s have a look with some examples
Examples of SQL IN operator
Example 1: Using IN with a List of Values
Suppose you have a table called Products with a column named Category, and you want to retrieve all products that belong to either the ‘Electronics’ or ‘Clothing’ categories.
SQL> SELECT * FROM Products
WHERE Category IN (‘Electronics’, ‘Clothing’);
Category IN (‘Electronics’, ‘Clothing’) checks whether the Category column value matches any of the values in the specified list.
Example 2: Using IN with a Subquery
SQL> SELECT * FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE OrderDate >= ‘2023-08-01’);
Example 3: Using IN with Numeric Values
SQL> SELECT * FROM Employees
WHERE DepartmentID IN (101, 102);
Example 4: Using NOT IN
SQL> SELECT * FROM Products
WHERE Category NOT IN (‘Books’);
Category NOT IN (‘Books’) retrieves rows where the Category column value does not match ‘Books’.
The SQL IN operator is an important tool for data filtering and retrieval based on a set of values or the output of a subquery. It enables you to efficiently execute conditional searches with several potential values.