The IN Operator in SQL Server is used to restrict the number of rows (or records) returned by the SELECT Statement. The IN Operator check the given expression or Column name against the Values inside the IN Operator, If there is at least one match then SELECT Statement will return the records
TIP: If you want to check the expression against Multiple values then use IN Operator.
SQL IN Operator Syntax
The basic syntax of the IN operator in SQL Server can be written as:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [Column Names] FROM [Source] WHERE [Column Name] IN (Value1, Value2, ...., ValueN) --We can also use <strong>SQL OR operator</strong> to write the above statement SELECT [Column Names] FROM [Source] WHERE [Column Name] = Value1 OR [Column Name] = Value2 OR .... OR [Column Name] = ValueN |
- Columns: It allows us to choose the number of columns from the tables. It may be One or more.
- Source: One or more tables present in the Database. SQL JOINs are used to join multiple table.
- Values: Here we have to provide the values or expression we want to check against the Column Name. If the column name matches with any Value then only records will be displayed.
In this article we will show you, How to use the IN Operator in SQL Server 2014 with examples. For this, We are going to use the below shown data
SQL IN Operator Example 1
The following query will find all the Customers present in the Customers table whose [Yearly Income] is either 60000 or 70000
T-SQL CODE
|
1 2 3 4 5 6 7 |
SELECT [FirstName] ,[LastName] ,[YearlyIncome] ,[Education] ,[Occupation] FROM [Customer] WHERE [YearlyIncome] IN (60000, 70000) |
OUTPUT
Let us show you one more example for better understanding.
SQL IN Operator Example 2
The following query will find all the Customers present in the Customers table whose Occupation is either Management or Professional or Clerical
T-SQL CODE
|
1 2 3 4 5 6 7 |
SELECT [FirstName] ,[LastName] ,[YearlyIncome] ,[Education] ,[Occupation] FROM [Customer] WHERE [Occupation] IN ('Management', 'Professional', 'Clerical') |
OUTPUT
SQL IN Operator Example 3
SQL Server allows us to use multiple operators in single WHERE Clause. In this example we are going to use IN Operator (Arithmetic Operator) and AND Operator (Logical Operator) in single statement.
The following query will find all the Customers present in the Customers table whose Occupation is either Management or Professional and their [Yearly Income] should be more than 60000
T-SQL CODE
|
1 2 3 4 5 6 7 8 |
SELECT [FirstName] ,[LastName] ,[YearlyIncome] ,[Education] ,[Occupation] FROM [Customer] WHERE [Occupation] IN ('Management', 'Professional') AND [YearlyIncome] > 60000 |
OUTPUT
SQL NOT IN Example
In SQL, We can also use the NOT Keyword along with the IN operator. For example, The following query will find all the Customers present in the Customers table whose Occupation is NOT Management and Professional
T-SQL CODE
|
1 2 3 4 5 6 7 |
SELECT [FirstName] ,[LastName] ,[YearlyIncome] ,[Education] ,[Occupation] FROM [Customer] WHERE [Occupation] NOT IN ('Management', 'Professional') |
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply