A. Comparing OR and IN
The following example selects a list of the names of employees who are design engineers, tool designers, or marketing assistants.
USE AdventureWorks;
GO
SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.Title = 'Design Engineer'
OR e.Title = 'Tool Designer'
OR e.Title = 'Marketing Assistant';
GO
However, you retrieve the same results by using IN.
USE AdventureWorks;
GO
SELECT FirstName, LastName, e.Title
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.Title IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');
GO
Here is the result set from either query.
FirstName LastName Title
--------- --------- ---------------------
Sharon Salavaria Design Engineer
Gail Erickson Design Engineer
Jossef Goldberg Design Engineer
Janice Galvin Tool Designer
Thierry D'Hers Tool Designer
Wanida Benshoof Marketing Assistant
Kevin Brown Marketing Assistant
Mary Dempsey Marketing Assistant
(8 row(s) affected)
B. Using IN with a subquery
The following example finds all IDs for the salespeople in the SalesPerson table for employees who have a sales quota greater than $250,000 for the year, and then selects from the Employee table the names of all employees where EmployeeID that match the results from the SELECT subquery.
USE AdventureWorks;
GO
SELECT FirstName, LastName
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE EmployeeID IN
(SELECT SalesPersonID
FROM Sales.SalesPerson
WHERE SalesQuota > 250000);
GO
Here is the result set.
FirstName LastName
--------- --------
Tsvi Reiter
Michael Blythe
Tete Mensa-Annan
(3 row(s) affected)
C. Using NOT IN with a subquery
The following example finds the salespersons who do not have a quota greater than $250,000. NOT IN finds the salespersons who do not match the items in the values list.
USE AdventureWorks
GO
SELECT FirstName, LastName
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE EmployeeID NOT IN
(SELECT SalesPersonID
FROM Sales.SalesPerson
WHERE SalesQuota > 250000)
GO