SQL IN operator

  • If you have a list of values to match in order to fetch rows from a database table, this is where the SQL IN 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

Following are a few examples of the IN operator with our created database and table. We will use the same database, test_db, and tbl_employee table to show a few examples of the SQL IN operator.

 

Example of IN operator with numbers

The example shows matching the emp_id values within the IN operator. In the query, we used numbers in the IN operator:

The SQL query:

See Demo Table with ‘IN’ online

Example of IN with the text

The following example shows using the SQL IN operator with text values. The example shows searching emp_name column values by using the IN operator.

The SQL query:

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 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 select – IN query:

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 the IN operator.

See Also: SQL Where Clause