When you use the IN subquery, you are going to generate results that begin in a list of zero or more values. These results are returned by the subquery and used by the outer query.

IN is also used to check whether a specific value matches any other value within a list.
If the IN subquery is used and a list contains NULL, the result of IN and NOT IN will return as UNKNOWN.

By using IN in SQL Server database, you can save some valuable time and streamline the information within a query within a matter of seconds.

Cities table:

States table:

IN Example:

Find the cities that have the state in the states table.
SELECT * FROM cities c
WHERE c.state IN (SELECT s.name FROM states s );

Result:

NOT IN Example:

Find the cities that NOT have the state in the states table.
SELECT * FROM cities c
WHERE c.state NOT IN (SELECT s.name FROM states s );

Result: