SQL IN Logical Operator

The following post provides a few simple examples on how to utilize the SQL IN Logical Operator when executing T-SQL statements.  There are several techniques that make this a very useful statement in the WHERE clause.

From the Microsoft Documentation site:  Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE, FALSE, or UNKNOWN.  Specifically, the “IN” Logical operator returns TRUE if the operand is equal to one of a list of expressions.

As a note of caution, if you include too many values in your IN statement, this can cause poor performance and in some cases produce errors.  If you need to run your criteria against a large number of values, it is suggested that you put them in a separate table and use a SELECT subquery within an IN clause.

SQL IN Logical Operator – Example 1 – Simple IN Statement

The first example demonstrates a simple condition, where I’d like to see all football players that play the positions of Tight End (TE) or Quarterback (QB)

After running the SQL code above, you will first see the results of all records in the #Player table:

SQL IN Logical Operator

Followed by the results of the SELECT statement with the SQL IN Logical Operator:  WHERE Position IN (‘TE’, ‘QB’)

SQL IN Logical Operator

SQL IN Logical Operator – Example 2 – SQL IN Statement using subquery

The second example demonstrates using a subquery as the SQL IN Logical Operator statement, to SELECT the list of Players that play for a specific NFLTeam

After running the SQL code above, you will first see the results of all records in the #Player table:

SQL IN Logical Operator

Next, you will first see the results of all records in the #Team table:

SQL IN Logical Operator

Then the final result set, based on the SELECT statement with a subquery as the SQL IN Logical Operator:  WHERE NFLTeam IN (SELECT NFLTeam FROM #Team)

SQL IN Logical Operator

SQL IN Logical Operator – Example 3 – Using the SQL “NOT IN” Logical Operator

The last example demonstrates the inverse of the SQL IN, which is the SQL NOT IN Logical Operator.  In this example, I’d like to see all football players that play the positions other than Tight End (TE) or Quarterback (QB).  This would be the inverse of Example 1 above.

After running the SQL code above, you will first see the results of all records in the #Player table:

SQL IN Logical Operator

Followed by the results of the SELECT statement with the SQL NOT IN Logical Operator:  WHERE Position NOT IN (‘TE’,’QB’)

SQL IN Logical Operator

 

I hope you find the example above informative.  Please use and modify to suit your needs.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Leave a Comment