The SQL IN operator is a powerful comparison tool used in WHERE clauses to filter results based on a list of values. It enhances query readability and performance when dealing with multiple OR conditions.
Table of Contents
π‘ Introduction to SQL IN Operator
The SQL IN operator allows you to test whether an expression matches any value in a list. It’s most commonly used in SELECT, UPDATE, DELETE, and INSERT queries to filter data efficiently.
π°οΈ A Glimpse into History
The SQL IN operator was introduced in early SQL standards to simplify multiple OR comparisons. It has been widely supported across all major RDBMS platforms, including SQL Server, Oracle, and MySQL.
β Advantages of SQL IN Operator
A few advantages of SQL IN Operator is given below for more clarity:
β Simplifies Multiple Conditions
Instead of writing multiple OR conditions, IN offers a cleaner syntax.
SELECT EmpID, EmpName, EmpDept
FROM Emp
WHERE EmpDept IN ('Admin', 'Sales', 'IT', 'HR', 'Marketing');β Improves Readability
Queries with IN are easier to write and understand, especially when filtering with many values.
β Can Be Optimized with Indexes
If the target column is indexed, IN can leverage that index for faster lookups.
β Supports Both Literals and Subqueries
IN can evaluate against a hardcoded list or the result set of a subquery, making it flexible.
SELECT OrderID, ItemID, ItemName, ItemQnty, ItemRate, ItemPrice, ItemValue
FROM Orders
WHERE ClientID IN (SELECT ClientID FROM Clients WHERE IsActive=1);β It Reduces Code Duplication
Reduces repetitive OR logic and makes WHERE clauses more concise and reusable.
-- Beginers:
WHERE OrderStatus = 'P' OR Status = 'C' OR Status = 'S' OR Status = 'D'
-- Experienced:
WHERE OrderStatus IN ('P', 'C', 'S', 'D');β Easy to use & Safe for Known Lists
When filtering against a known set of fixed values, IN is straightforward and safe to use.
β Easily Maintainable in Reports & Filters
Frequently used in dashboards and reports where filters are driven by user selections (multi-select dropdowns).
β It Avoids Join Overhead in Small Lookups
When compared to a short list of values, IN avoids the overhead of unnecessary JOINs or temp tables.
β Disadvantages of SQL IN Operator
A few disadvantages of SQL IN Operator is given below for more clarity:
β Performance Drops if the list is Long
Very long lists inside IN (e.g., hundreds/thousands of values) may slow down performance.
β It does not handle NULL values
IN excludes NULL unless explicitly checked using IS NULL.
SELECT EmpID, EmpName, EmpDept
FROM Emp
WHERE EmpDept IN ('Admin') OR EmpDept IS NULL;β Less Efficient than EXISTS for Subqueries
For large datasets, using EXISTS or JOIN is often more efficient than IN with subqueries.
SELECT EmpID, EmpName, EmpDept
FROM Emp e
WHERE EXISTS (
SELECT 1 FROM Depts d
WHERE d.DeptID = e.DeptID);β Duplicates in List Cause Redundancy
IN does not ignore duplicate values in the list, which may not affect logic but can affect performance.
SELECT EmpID, EmpName, EmpDept
FROM Emp
WHERE EmpDept IN ('Admin', 'IT', 'HR'); -- Redundantβ Hard to Maintain Long Literal Lists
Using long literal lists in IN clauses becomes messy and error-prone.
-- Difficult to manage:
WHERE ProductID IN (5001, 5002, 5003, 5004, 5005, 5006,......................................................, 5999,, 6000)β Vulnerable to SQL Injection if Not Parameterized
When values in IN come from user input and are directly embedded in dynamic SQL, it opens injection risks.
Bad Practice (Vulnerable):
EXEC('SELECT ClientID, ClientName, JoiningDate FROM Clients WHERE ClientID IN (' + @ClientIDs + ')');
Good Practice (Parameterized):
Use table-valued parameters or stored procedures.β Subquery with NULL Returns No Results
If the subquery returns even a single NULL, the entire IN condition may not match anything.
-- If subquery returns NULLs:
SELECT ItemID, ItemName, ItemPrice, ItemQnty
FROM Items
WHERE ItemCategoryID IN
(SELECT CategoryID FROM Categories WHERE IsActive=1);
-- May fail if CategoryID has NULLsβ It may not be indexed properly
Some query optimizers may not optimize IN (subquery) as well as a JOIN or EXISTS, especially in older versions of SQL Server.
π Required Permissions to Use the SQL IN Operator
To use the SQL IN operator, users must have standard SELECT, UPDATE, and DELETE permissions on the involved tables.
π§ When to Use the SQL IN Operator
A few use cases for SQL IN Operator are given below for better understanding:
β To Replace Multiple OR Conditions
Use IN to simplify queries where a column needs to be compared against multiple values.
SELECT EmpID, EmpName, EmpDept
FROM Emp
WHERE EmpDept IN ('Admin', 'Sales', 'Finance', 'IT');β When Filtering Based on a Predefined List
IN is ideal when the filter values are known ahead of time, such as filtering specific regions or statuses.
SELECT OrderID, ItemName, ItemQnty, ItemPrice, ItemAmount
FROM Sales
WHERE Country IN ('US', 'UK', 'IN', 'DN');β To Match Values from a Subquery
When you want to filter rows based on a dynamic list returned from another query.
SELECT ItemID, ItemName, ItemQnty, ItemPrice
FROM Items
WHERE ItemCategoryID IN (SELECT CategoryID FROM Categories WHERE IsActive=1);β To Filter in Reporting or UI Multi-Select Filters
In reporting systems where users select multiple values, the ‘IN’ operator can efficiently filter records based on the selection.
β To Avoid Joins for Small Lookup Tables
When working with a short list of values, IN may perform better and be easier to write than a JOIN.
SELECT OrderID, ItemName, ItemQnty, ItemPrice, ItemAmount
FROM Sales
WHERE SalesPersonID IN (50601, 48261, 31022, 21005, 23567, 68362);β Use SQL IN Operator for Small to Medium Lists
Limit the number of values in the IN clause to avoid performance issuesβideally, fewer than 100 items.
SELECT EmpID, EmpName, EmpDept, EmpDesig
FROM Emp
WHERE EmpDept IN ('Sales', 'Admin', 'Marketing', 'Finance');β Ensure the Column is Indexed
For better performance, apply the IN condition on indexed columns whenever possible.
β Avoid Duplicates Values in the IN List
Remove redundant values to reduce unnecessary parsing and execution overhead.
β Use Parameterized Queries in Applications
To prevent SQL injection and improve caching, never concatenate user input directly into an IN clause.
β Avoid NULL values in Subqueries Used with IN
If a subquery returns NULLs, the IN operator may fail to return correct results. Use the IS NOT NULL filter.
β Prefer EXISTS or JOIN for Large Datasets
For large subquery result sets, the EXISTS or JOIN operator often provides better performance than the IN operator.
β Use Table-Valued Parameters for Dynamic Lists
When passing a list to a stored procedure, use TVPs instead of dynamic SQL with IN.
β Combine with Other Conditions Efficiently
Ensure IN is part of a well-optimized WHERE clause and not paired with overly broad conditions.
Examples of SQL In Operator
Below are the practical examples of the SQL IN operator:
β Step 1: Create Table Script
CREATE TABLE Employees (
EmpID INT IDENTITY(1,1) PRIMARY KEY,
EmpName VARCHAR(100),
EmpDept VARCHAR(50),
Country VARCHAR(50),
Salary Decimal(15,2),
DOJ DateTime DEFAULT GETDATE(),
IsActive bit DEFAULT 1
);β Step 2: Insert Sample Data
--TRUNCATE TABLE Employees
INSERT INTO Employees (EmpName, EmpDept, Country, Salary) VALUES
('Hannah', 'HR', 'USA', 67000.00),
('Bob', 'Finance', 'UK', 78000.00),
('Charles', 'IT', 'USA', 83000.00),
('David', 'IT', 'India', 78000.00),
('Evan', 'HR', 'Germany', 62000.00),
('Frank', 'Marketing', 'UK', 68000.00),
('Tom', 'IT', 'Germany', 96600.00),
('Ashok', 'Admin', 'India', 69300.00),
('Ivy', 'IT', 'UAE', 71040.00),
('Jackey', 'Marketing', 'USA', 55090.00),
('Jim', 'Marketing', 'USA', 55090.00),
('Kishor', 'Admin', 'India', 69300.00),
('Alex', 'IT', 'USA', 83000.00),
('Tania', 'IT', 'India', 78000.00);πΉ Find employees in specific departments
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpDept IN ('Admin', 'Marketing', 'Finance');
πΉ Find employees from selected countries
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE Country IN ('UK', 'India');
πΉ Find employees with specific EmpIDs
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpID IN (4, 8, 9);
πΉ Find employees with salaries in a specific range
SELECT * FROM Employees
WHERE Salary IN (71040.00, 78000.00, 69300.00);πΉ Find employees not from selected countries using NOT IN
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE Country NOT IN ('USA', 'UK');πΉ Use SQL IN Operator with a subquery: Find employees in the same departments as employees from India
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpDept IN (
SELECT DISTINCT EmpDept
FROM Employees
WHERE Country = 'India');πΉ Find employees whose names match a set of values
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpName IN ('Bob', 'Evan', 'Ivy');πΉ Case-insensitive match using IN with COLLATE
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpName COLLATE SQL_Latin1_General_CP1_CI_AS IN ('alice', 'EVAN', 'FRANK');πΉ Combine IN with AND for multiple conditions
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpDept IN ('IT', 'HR')
AND Country IN ('Germany', 'USA');πΉ Use IN in a DELETE statement
DELETE FROM Employees
WHERE Country IN ('UAE', 'UK');β οΈ Caution: Run this only after backing up or testing on dummy data.
πΉ Use IN with a Correlated Subquery
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees E
WHERE EmpDept IN (
SELECT EmpDept
FROM Employees
GROUP BY EmpDept
HAVING AVG(Salary) > 61000);πΉ Use IN with a Derived Table (Common Table Expression)
;WITH DeptAvg AS (
SELECT EmpDept, AVG(Salary) AS AvgSal
FROM Employees
GROUP BY EmpDept
)
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpDept IN (
SELECT TOP 2 EmpDept
FROM DeptAvg
ORDER BY AvgSal DESC);πΉ Join with IN and Subquery for Filtering
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpDept IN (
SELECT DISTINCT EmpDept
FROM Employees
WHERE Country IN ('Germany', 'India'));πΉ Use IN with a User-Defined Table-Valued Function
-- Sample function (create once)
CREATE FUNCTION dbo.fn_GetPriorityCountries()
RETURNS @Countries TABLE (Country NVARCHAR(50))
AS
BEGIN
INSERT INTO @Countries VALUES ('USA'), ('Germany');
RETURN;
END;
-- Query using the function
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE Country IN (SELECT Country FROM dbo.fn_GetPriorityCountries());πΉ Use SQL IN Operator inside a CASE Expression
SELECT EmpName, Country,
CASE
WHEN Country IN ('USA', 'UK') THEN 'Western'
WHEN Country IN ('India', 'UAE') THEN 'Asia'
ELSE 'Other'
END AS RegionGroup
FROM Employees;
πΉ SQL IN Operator with Window Function and Subquery
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpID IN (
SELECT EmpID
FROM (
SELECT EmpID,
RANK() OVER (PARTITION BY EmpDept ORDER BY Salary DESC) AS RecordRank
FROM Employees
) AS Ranked
WHERE RecordRank = 1);πΉ SQL IN Operator with Dynamic Column Filter via Table Join
-- Supporting table
CREATE TABLE EmpDept (
DeptName NVARCHAR(50)
);
INSERT INTO EmpDept VALUES ('IT'), ('HR');
-- Query
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpDept IN (SELECT DeptName FROM EmpDept);πΉ IN with a Comma-Separated String Split
-- Input string
DECLARE @DeptList NVARCHAR(MAX) = 'IT,Finance,HR';
-- Convert to a table and use IN
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpDept IN (
SELECT LTRIM(value)
FROM STRING_SPLIT(@DeptList, ',')
);πΉ SQL IN Operator with JSON Parsing
-- Declare JSON array
DECLARE @json NVARCHAR(MAX) = '["India", "Germany", "UAE"]';
-- Parse JSON and use IN
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE Country IN (
SELECT value
FROM OPENJSON(@json)
);πΉ SQL IN Operator with EXISTS and Nested SELECTs
SELECT EmpID, EmpName, EmpDept, Country, Salary FROM Employees
WHERE EmpDept IN (
SELECT EmpDept
FROM Employees
WHERE Country = 'USA'
GROUP BY EmpDept
HAVING COUNT(*) > 1
);Conclusion
The operator has an essential component of SQL that filters the logic and increases the code readability. If we use the SQL IN operator properly in our query, it can improve both query efficiency and stability in various applications.
FAQs: Top 50 Interview Questions with Answers
What is SQL IN Operator?
Answer: It is used to filter the results by matching a column against the list of values.
Qns: Can the SQL ‘IN’ clause be used with subqueries?
Ans: Yes, it is commonly used with subqueries for dynamic filtering.
Qns: What is the syntax of SQL IN?
Ans: SELECT * FROM Table WHERE Column IN (value1, value2, β¦);
Qns: How does SQL IN differ from multiple OR conditions?
Ans: IN is a cleaner and more readable replacement for OR.
Qns: Can you use the SQL ‘IN’ operator with strings?
Ans: Yes, IN works with strings, numbers, and dates.
Qns: Is SQL IN case-sensitive?
Ans: It depends on the database collation settings.
Qns: What happens if the list in IN contains NULL?
Ans: NULLs can lead to unexpected results and should be excluded.
Qns: Can the SQL IN operator be used in the WHERE clause only?
Ans: Primarily in WHERE, but also applicable in HAVING.
Qns: How do you avoid SQL injection in IN queries?
Ans: Use parameterized queries or table-valued parameters.
Qns: Which is faster: IN or EXISTS?
Ans: EXISTS is usually faster with large subqueries.
Qns: Can SQL IN compare against a result set?
Ans: Yes, through subqueries like IN (SELECT β¦).
Qns: What is the maximum number of values in an IN list?
Ans: It depends on the DBMS, but generally thousands.
Qns: Can you use the ‘NOT IN’ operator with SQL?
Ans: Yes, NOT IN filters out the listed values.
Qns: What is a common performance issue with IN?
Ans: Long value lists may lead to slower execution.
Qns: What is a workaround for large IN lists?
Ans: Use JOINs or load values into a temporary table.
Qns: Can IN be used with joins?
Ans: Yes, you can combine it with joins.
Qns: Can we use IN with datetime values?
Ans: Yes, but formatting and precision should match.
Qns: Can the SQL IN operator be nested?
Ans: Yes, you can nest subqueries inside an IN clause.
Qns: Does SQL IN use indexes?
Ans: Yes, if the column is indexed.
Qns: Can IN be used in DELETE statements?
Ans: Yes, to delete multiple specific records.
Qns: Does IN work with stored procedures?
Ans: Yes, but dynamic SQL or TVPs are often needed.
Qns: Is IN operator ANSI SQL compliant?
Ans: Yes, it is part of the standard.
Qns: When should you not use IN?
Ans: When working with large result setsβprefer EXISTS.
Qns: What does IN return if the list is empty?
Ans: No rows are returned.
Qns: Can IN be used in CASE statements?
Ans: Yes, inside conditions of CASE WHEN clauses.
Qns: Can you use IN with aliases?
Ans: Yes, such as WHERE t.Col IN (β¦).
Qns: Can IN be used with NULL-safe comparison?
Ans: No, use IS NULL separately.
Qns: What data types does IN support?
Ans: Most data types: INT, VARCHAR, DATE, etc.
Qns: What’s the difference between IN and BETWEEN?
Ans: IN matches specific values; BETWEEN defines a range.
Qns: Is IN a deterministic operator?
Ans: Yes, if values are static and non-null.
Qns: What tool shows IN query performance?
Ans: Execution Plan in SSMS.
Qns: What is the role of collation in IN?
Ans: It affects case sensitivity and comparisons.
Qns: How can IN impact CPU usage?
Ans: Large lists may increase CPU usage due to the increased number of comparisons.
Qns: Can IN be used on multiple columns?
Ans: No, use tuples or joins for that.
Qns: Does IN support wildcards?
Ans: No, use LIKE for pattern matching.
Qns: What is a safer alternative to dynamic IN?
Ans: Table-valued parameters (TVPs).
Qns: Can IN be used with the GROUP BY clause?
Ans: Yes, to filter results before grouping.
Qns: Does SQL Server cache plan for the ‘IN’ operator?
Ans: Yes, but not always if literals are used.
Qns: How to make IN more readable?
Ans: Use named lists, temp tables, or formatting tools.
Qns: Can you use expressions inside IN?
Ans: Yes, e.g., WHERE ID IN (1+1, 2+2).
Qns: What happens if IN has duplicates?
Ans: They are ignored in logic but not in performance.
Qns: Can IN handle sorted lists?
Ans: IN does not require sorted input.
Qns: What’s the impact of datatype mismatch in IN?
Ans: It can lead to implicit conversion and slowdowns.
Qns: Can IN be used with partitioned tables?
Ans: Yes, with proper indexing.
Qns: What is IN vs. JOIN for small datasets?
Ans: IN may be more concise and performant.
Qns: How do you handle long IN lists dynamically?
Ans: Use XML, JSON, or string-split functions.
Qns: Is IN evaluated left to right?
Ans: Logical evaluation order isn’t guaranteed.
Qns: Can IN be used in triggers?
Ans: Yes, just like in regular SELECTs.
Qns: Is IN supported in views?
Ans: Yes, IN can be used in view definitions.
Qns: What are some alternatives to IN?
Ans: EXISTS, JOIN, INTERSECT, temp tables.
Review the articles below, also.
Explore Always Encrypted: Top 5 Usage
Explore SQL Server 2025: 5 Best Usage
Explore Top 10 Features of SSMS 21
PostgreSQL vs MySQL: Top 9 Differences
Explore Sequences and Series: Top 5 Usage
SQL Window Functions: Top 5 Best Usage
Explore SQL Commands: Top 25 Commands
Understand Deadlocks in SQL Server
Unleash Database Insights with Extended Events in SQL Server
Dynamic Data Masking in SQL Server
A Powerful SQL Server Developer Edition
SQL Server Configuration Manager
SQL Managed Instance: Faster & Improved
TSQL Database Backup: Top 7 Usage
Dbcc Freeproccache: A powerful command
Extended Events in SQL Server: A Deep Dive
Understand Deadlocks in SQL Server