Quick Reach
The IN operator
- If you have a list of values to match in order to fetch rows from a database table, this is where the IN SQL operator works.
- The IN is a logical operator that takes a list of values to be matched to return records.
- The IN operator is used in the Where clause of SQL.
- You can provide a list of numbers, strings or dates in the IN operator.
See an example of IN operator
To make it clearer, suppose you have five numbers to match in a column of a table.
The numbers are: 1,2,3,4,5
The query with OR operator:
Select * from table_name where col_val=1 or col_val =2 or col_val =3 or col_val =4 or col_val =5
This is how using the IN operator will make it simple:
Select * from table_name where col_val IN (1,2,3,4,5)
The IN operator example
Let us show you examples of the IN operator with our created database and table. We will use same database, test_db and tbl_employee table to show a few examples of the SQL IN operator.
Just to remind this is how our example table is created in the test_db:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
Create table tbl_employee
(emp_id int,
emp_name varchar(100),
emp_age int,
emp_salary decimal(8,2),
joining_date datetime,
PRIMARY KEY (emp_id)
);
|
Example of IN operator with numbers
The example shows, matching the emp_id values within the IN operator.
The SQL query:
|
1
2
3
|
select * from tbl_employee
Where emp_id IN (1,4,7,8)
|
See Demo Table with ‘IN’ online
Example of IN with the text
The following example shows using the IN operator with text values. The example shows searching emp_name column values in the IN operator.
The SQL query:
|
1
2
3
|
select * from tbl_employee
Where emp_name IN (‘Mike’,‘Jimmy’,‘Ben’)
|
Demo Table with ‘IN’ online
SQL select – IN with values from another table example
The IN operator is useful if you have to match multiple values rather one or two. The IN SQL operator becomes even more useful when you need to match multiple values (total values are not known) from another table.
Consider a scenario of two tables where the first table tbl_employee is containing the employee’s information. The other table is tbl_customers_orders that contain order information of customers along with the emp_id. The emp_id tells that which employee entertained a specific customer in a departmental store.
Suppose, we want to return only those employees from the tbl_emplyee table who have information in the tbl_customers_orders i.e. who dealt with the customer and entered a customer order.
The SQL select – IN query will be:
|
1
2
3
|
select * from tbl_employee
Where emp_id IN (select emp_id from tbl_customers_orders)
|
Demo Table with Select and ‘IN’
As you can see, only employees records whom records/emp_id exist in the tbl_customers_orders table is fetched. The graphic shows both tables before and then the result after SQL IN statement.
See Also SQL Where Clause
Leave A Comment?