SQL Server IN Operator

IN clause in SQL Server is a logical operator to check a list of values, if available in a specific table column. This keyword helps in filtering data from the query results, based on a set of values. Using this IN clause we can specify multiple values in a WHERE clause, making your queries more readable and efficient. The IN clause can be used as a replacement for multiple OR conditions in a query.

In this article, we will understand the IN clause in detail with its syntax, usage, and benefits with examples to illustrate its functionality.

Syntax:

SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

• SELECT: Specifies the column names that we want in our result.
• FROM: Indicates the table from which we are querying the data to retrieve the result set.
• WHERE: Filters the rows based on a specified condition.
• column_name: Specifies the column that we want to filter.
• IN: The keyword used to specify a list of values.

Usage of IN Clause

• The IN clause is commonly used to filter data based on a set of predefined values.
• The IN clause simplifies queries that would otherwise require multiple OR conditions.
• The IN clause can be used with SELECT, UPDATE or DELETE statements.

Advantage of Using IN Clause

1. Readability: The IN clause improves the readability of your SQL queries, especially when filtering on multiple values. It is much clearer than using multiple OR conditions.
2. Simplicity: Queries with IN clause simplifies the complexity and helps to write and maintain the queries in a more easy and simpler way. The IN clause is very helpful when dealing with a long list of data to check with existing data in a column.
3. Performance Optimization: By using the IN clause better performance can be achieved, compared to multiple OR conditions. The database optimizer can often optimize the query execution plan more effectively.
4. Versatile in nature: The IN clause can be used with various data types like strings, numbers, and dates which makes the IN clause applicable to a wide range of use cases.

Student Table

Students Table

Example 1: Retrieving Results from Specific Column Using IN Clause

Suppose you want to retrieve information about students who belong to the grades ‘A’ or ‘B’. The IN clause simplifies this query:

SELECT student_id, student_name, grade
FROM students
WHERE grade IN ('A', 'B');

The result Looks Like:

After Query run Table Looks

Explanation: This query efficiently selects students whose grades match either ‘A’ or ‘B’. The IN clause makes the condition concise and readable.

Example 2: Using Subqueries with IN Clause

Query

SELECT student_id, student_name, grade,grade_level
FROM students
WHERE grade IN (SELECT DISTINCT grade FROM students WHERE grade_level = 'High School');

The result Looks Like:

After Query run Table Looks

Explanation: Here, the subquery retrieves distinct grade values for the ‘High School‘ level, and the main query selects students with those grades.

Example 3: Combining IN Clause with Other Conditions

You can combine the IN clause with other conditions to create more complex queries. For example,

Let’s find students from grades ‘A‘ or ‘B‘ who are enrolled in the ‘Mathematics‘ course:

Query

SELECT student_id, student_name, grade
FROM students
WHERE grade IN ('A', 'B') AND course = 'Mathematics';

The result Looks Like:

After Query Run Table Looks

Explanation: In the above Query, we fetch all students whose grades whether A or B and enrolled in Mathematics courses.

Conclusion

IN clause in SQL Server is a tool through which we can get filter the data based on a predefined set of values. It allows us to efficiently retrieve information based on specific criteria. Whenever we are dealing with a small or large dataset, the IN clause provides a clean and readable way to express conditions in your SQL queries, making it a valuable asset in database querying.