IN Operator in SQL: Syntax , Examples and FAQs
In SQL, IN is an operator. IN operator is used in SQL to match a value against a list or subquery. IN operator allows you to specify multiple values in a WHERE clause to compare a column value. IN operator simplifies querying when you want to check if a value matches any value in a specified list or subquery. Understanding the IN operator enhances the ability to perform complex query, retrieve targeted data, and extract valuable insights from datasets. This article provides a detailed overview of the IN operator with example. After reading this article you can use the IN operator easily and retrieve valuable data from a table.
Syntax
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ........);
Demo Database
CREATE TABLE `Users` (
`Id` int NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Mobile` varchar(100) DEFAULT NULL,
`DOB` date DEFAULT NULL,
`Weight` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`Id`)
);
CREATE TABLE `UserAddress` (
`Id` int NOT NULL AUTO_INCREMENT,
`UserId` int NOT NULL,
`AddressType` enum('Permanent','Temporary') DEFAULT NULL,
`Address` varchar(300) DEFAULT NULL,
`Country` varchar(50) DEFAULT NULL,
`State` varchar(50) DEFAULT NULL,
`City` varchar(50) DEFAULT NULL,
`PostalCode` varchar(50) DEFAULT NULL,
PRIMARY KEY (`Id`)
);INSERT INTO Users (Name, Mobile, DOB, Weight)
VALUES ('Deepak', '9999999991', '1993-01-15', 60),
('Smith', '9999999999', '1994-11-20', 85),
('Naveen', '9999999992', '1997-01-15', 55),
('Deepak Sharma', '9999999993', '1998-08-03', null),
('Jane Smith', '9999999995', '1993-10-05', 73),
('Jone Head', '9999999991', '1983-01-15', 90),
('Deepak Kumar', '9999999999', '1984-11-20', 85),
('Jack', '9999999992', '1987-01-15', 59),
('Ramesh', '9999999993', '1988-08-03', 86),
('David', '9999999995', '1983-10-05', 70);
INSERT INTO UserAddress(UserId, AddressType, Address, Country, State, City, PostalCode)
VALUES
(1, 'Permanent', '23 Oak Street', 'India', 'Maharashtra', 'Mumbai', 400001),
(1, 'Temporary', '456 Oak Ave', 'USA', 'New York', 'New York City', 10001),
(2, 'Permanent', '789 Elm Rd', 'Canada', 'Ontario', 'Toronto', 550002),
(2, 'Temporary', '56 Maple Road', 'India', 'Delhi', 'New Delhi', 110001),
(3, 'Permanent', '23 Oak Street', 'India', 'Maharashtra', 'Mumbai', 400001),
(3, 'Temporary', '456 Oak Ave', 'USA', 'California', 'Los Angeles', 10001),
(4, 'Permanent', '888 Birch Dr', 'USA', 'California', 'LG', 2000),
(4, 'Temporary', '999 Willow Ave', 'India', 'Maharashtra', 'pune', 3000),
(5, 'Permanent', '101 Pineapple Pkwy', 'Germany', 'Bavaria', 'Munich', 80331),
(5, 'Temporary', '202 Mango Ave', 'Germany', 'Berlin', 'Berlin', 10115);IN Operator Example
IN Operator is typically useful when you have a predefined set of values and you want to retrieve data from the database.
SQL query to retrieve permanent users of India, USA and Canada from Users table.
SELECT UserId, AddressType, Country
FROM UserAddress
WHERE AddressType = "Permanent" AND
Country IN ("India","USA","Canada");UserId AddressType Country ------------------------------------- 1 Permanent India 2 Permanent Canada 3 Permanent India 4 Permanent USA
NOT IN Operator
In SQL, NOT is used to negate or reverse the result of a condition. It is commonly used in conjunction with other SQL operators to perform logical negation. NOT IN Operator is used to filter out rows where a column does not match any of the values specified in a given list.
SQL query to retrieve users who are not permanent residents of India, USA, or Canada from the Users table.
SELECT UserId, AddressType, Country
FROM UserAddress
WHERE AddressType = "Permanent" AND
Country NOT IN ("India","USA","Canada");UserId AddressType Country -------------------------------------- 5 Permanent Germany
IN with sub Query
In SQL , you can use IN operator with sub query in WHERE clause as per your business logic.
SQL query to retrieve user name and date of birth of permanent users of India, USA and Canada.
SELECT Name, DOB FROM Users
WHERE Id IN (SELECT UserId
FROM UserAddress
WHERE AddressType = "Permanent" AND
Country IN ("India","USA","Canada")
);Name DOB ------------------ Deepak 1993-01-15 Smith 1994-11-20 Naveen 1997-01-15 Deepak Sharma 1998-08-03
SQL query to retrieve user name and date of birth of permanent users of India.
SELECT Name, DOB FROM Users WHERE Id IN (SELECT UserId FROM UserAddress WHERE AddressType = "Permanent" AND Country ="India");
Name DOB ----------------------- Deepak 1993-01-15 Naveen 1997-01-15
NOT IN With Sub Query
In SQL , you can also use NOT IN operator with sub query in WHERE clause as per your business logic.
SQL query to retrieve user name and date of birth whose addresses are not present in the address table.
SELECT Id, Name, DOB FROM Users WHERE Id NOT IN (SELECT UserId FROM UserAddress);
Id Name DOB ------------------------------- 6 Jone Head 1983-01-15 7 Deepak Kumar 1984-11-20 8 Jack 1987-01-15 9 Ramesh 1988-08-03 10 David 1983-10-05
Key Characteristics of IN Operator
- IN operator is used in SQL to match a value against a list or subquery.
- IN operator is a shorthand for multiple OR conditions.
- IN operator simplifies queries that involve matching against multiple values, avoiding the need for multiple OR conditions.
- IN operator can also be used when the exact number of values is not known in advance (sub query).
- IN operator with indexed columns can significantly improve query performance, especially when the number of values in the IN list is relatively small.
- IN can sometimes offer better performance compared to multiple OR conditions, especially when used with indexed columns.
- IN operator is versatile and efficient for querying data based on specific sets of values, offering flexibility and improved query performance in appropriate use cases.
Frequently Asked Questions (FAQs)
1. What is the IN operator in SQL?
The IN operator in SQL is used to check whether a column value matches any value from a given list or subquery. Instead of writing multiple OR conditions, IN provides a cleaner and more readable syntax. It is commonly used when filtering records against predefined sets such as country lists, status values or IDs returned from another query.
2. How is IN different from using multiple OR conditions?
IN is functionally equivalent to multiple OR conditions, but it is more readable and maintainable. For example, Country IN ('India','USA','Canada') is clearer than chaining multiple OR statements. Databases can also optimize IN internally, making it easier to manage and sometimes more efficient than long OR chains.
3. Can the IN operator be used with subqueries?
Yes. IN works very well with subqueries and is often used to filter records based on results from another table. This is especially useful for parent-child relationships, such as selecting users whose IDs exist in an address or transaction table. The subquery must return a single column.
4. What happens if the IN list contains NULL values?
If NULL appears inside an IN list, comparisons involving NULL return UNKNOWN. This can lead to unexpected filtering behavior. To avoid issues, it is best practice to remove NULLs from IN lists or explicitly handle NULL using IS NULL or COALESCE() when necessary.
5. How does NOT IN work in SQL?
NOT IN is the logical negation of IN. It returns rows where the column value does not match any value in the list or subquery. However, NOT IN behaves differently when NULL values are involved if the subquery returns even one NULL, the entire condition may return no rows unless NULLs are handled properly.
6. Why is NOT IN with subqueries risky?
NOT IN becomes dangerous when the subquery can return NULL values. Because SQL uses three-valued logic, comparisons with NULL result in UNKNOWN, which can exclude all rows unexpectedly. In such cases, NOT EXISTS is generally safer and recommended for correctness.
7. Is IN case-sensitive?
Case sensitivity depends on the database collation and engine. For example, MySQL is typically case-insensitive by default, while PostgreSQL is case-sensitive unless configured otherwise. Always confirm collation settings when using IN with text values to avoid incorrect matches.
8. Can IN be used with numeric columns?
Yes. IN works efficiently with numeric columns and is commonly used with IDs, status codes and enum-like values. When used on indexed numeric columns with small lists, IN performs very well and is often preferred over joins for simple filters.
9. How does IN affect query performance?
Performance depends on the size of the IN list and indexing. Small IN lists on indexed columns perform efficiently. Very large IN lists may degrade performance and should be replaced with joins or temporary tables. Always review execution plans for large datasets.
10. Is IN faster than JOIN?
IN is not always faster than JOIN. For small value sets, IN can be simpler and faster. For large datasets or complex relationships, JOINs are usually more scalable and predictable. Choosing between IN and JOIN depends on data size, indexing and query intent.
11. Can IN be used in UPDATE or DELETE statements?
Yes. IN is commonly used in UPDATE and DELETE queries to target specific rows based on lists or subqueries. However, these operations are destructive, so always verify the subquery results using a SELECT first and ensure backups exist before execution.
12. Is there a limit to how many values IN can contain?
Most databases do not impose a strict limit, but very large IN lists can cause performance issues or query length limitations. In production systems, it is better to store large lists in tables and join them instead of passing thousands of values in an IN clause.
13. When should IN be avoided?
IN should be avoided when the value list is very large, when NULL handling is complex or when correlated conditions are required. In such cases, JOIN or EXISTS often provides better performance and clearer intent.
14. How does IN behave with duplicate values?
Duplicate values inside an IN list do not affect the result. SQL internally treats the list as a set. However, including duplicates reduces readability and should be avoided for clean and professional queries.
15. Is IN commonly asked in SQL interviews?
Yes. IN and NOT IN are frequently tested in interviews, especially with subqueries and NULL behavior. Interviewers often expect candidates to understand when IN is appropriate, its performance implications and why NOT EXISTS is sometimes safer than NOT IN.