IN Operator in SQL
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.