Text copied!
IN operator
In SQL, Operators are special character, symbol or a keyword that is used to perform some specific operations. For example : comparing data
SQL operators are commonly used with SQL statements to retrieve, filter, and manipulate data.
IN operator :
IN operator is used to examine if a specific column matches any of the values you specify in 'IN' operator. It is similar to 'equals to' ( = ) operator in SQL.
But, IN operator enables you to specify a collection or list of values all at once to check if a specific column matches any of the these list of values.
The syntax of the IN operator generally looks like this :
SELECT column_name(s)
FROM table_name
WHERE column IN (value1, value2, value3, value4, ...);
• Specify the column(s) name after the 'SELECT' keyword.
• Specify Asterisk (*) symbol to selects all columns from the table after the 'SELECT' keyword.
• Specify the table name after the 'FROM' keyword.
• Specify the conditions after the 'WHERE' keyword.
• Specify the list of values after the 'IN' keyword.
Here's an example of how you might use the IN operator :
Let's suppose we have two tables :
[a] Employees Table :
→ It contains columns [Employee_Id], [Employee_Name], [Gender] and [Salary].
[b] Employees_2 Table :
→ It contains all columns same as '[Employees] table' with same data types and order. Except '[Age]' column.
Case - 1 :
Let's assume you want to retrieve employee data from [Employees] table where Employee_Id contains value 1, 4, 5.
It can be achieved by combining 'equals to' operator with 'OR' operator which is a very bad idea. Instead that you can use 'IN' operator to keep code short, clean and efficient.
Run below SQL statement :
SELECT *
FROM [Employees]
WHERE Employee_Id IN (1,4,5);
Above statement will retrieve employee data where Employee_Id contains value 1, 4, 5.
Case - 2 :
• You can also use 'SELECT' statement in 'IN' operator to retrieve list of values from some other table.
Let's assume you want to retrieve employee data from [Employees] table where Employee_Id should match the values found in the Employee_Id column of the [Employees_2] table.
Run below SQL statement :
SELECT *
FROM [Employees]
WHERE Employee_Id IN ( SELECT Employee_Id FROM [Employees_2] );
Only value '1' matches in the Employee_Id column in both the employee's table. Hence only 1 row is returned.