Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - IN - Testing Values Returned by a Subquery

By: FYIcenter.com

(Continued from previous topic...)

How To Test Values Returned by a Subquery with the IN Operator?

Normally, the comparison operator IN is used against a list of specifie values as in the format of: "test_value IN (value_1, value_2, ..., value_n)". But you can also replace the list of values by a subquery as the following formats:

test_value IN (SELECT column FROM ...) 
-- Returns TRUE if the test_value equals to one of 
   the values returned from the subquery

test_value NOT IN (SELECT column FROM ...) 
-- Returns TRUE if the test_value does not equal to any of 
   the values returned from the subquery

The following tutorial exercise shows you two examples of IN operators. The sample database AdventureWorksLT provided by Microsoft is used.

USE adventureWorksLT
GO

SELECT COUNT(*) FROM SalesLT.Customer c
WHERE c.CustomerID IN (
   SELECT s.CustomerID FROM SalesLT.SalesOrderHeader s
   )
GO
32

SELECT COUNT(*) FROM SalesLT.Customer c
WHERE c.CustomerID NOT IN (
   SELECT s.CustomerID FROM SalesLT.SalesOrderHeader s
   )
GO
408

(Continued on next topic...)

1. What Is a Boolean Value?
2. What Are Conditional Expressions?
3. What Are Comparison Operations?
4. How To Perform Comparison on Exact Numbers?
5. How To Perform Comparison on Floating Point Numbers?
6. How To Perform Comparison on Date and Time Values?
7. How To Perform Comparison on Character Strings?
8. What To Test Value Ranges with the BETWEEN Operator?
9. What To Test Value Lists with the IN Operator?
10. What To Perform Pattern Match with the LIKE Operator?
11. How To Use Wildcard Characters in LIKE Operations?
12. How To Test Subquery Results with the EXISTS Operator?
13. How To Test Values Returned by a Subquery with the IN Operator?
14. What Are Logical/Boolean Operations?