SQL IN Operator

Submitted by admin on Wednesday, March 16, 2011 - 11:26.

The SQL IN operator is used with the WHERE clause in the SELECT statement, this can be used to look to the dataset matching the condition and looking in the supplied data, in easier words, the IN operator can make further selections in the selected data.

The SQL IN operator can also be used to write nested SQL queries, the nested query first fetches some data and then the wrapper query select or simply filters more from the selected data.

Now let's see the IN operator in Action.

SQL IN Syntax

SELECT column_name(s) FROM TABLE_NAME WHERE column_name IN "Some Dataset OR combination"

Consider the following two tables for this exercise:

Users

Firstname	Lastname	Salary	DeptNumber
John	Smith	1000	1
Mathew	Simon	3000	2
Bill	Steve	2200	3
Amanda	Rogers	1800	4
Steve	Hills	2800	5
Steve	jobs	2400	6
bill	cosby	700	7

Departments

DeptNo	DepartmentName
1	Employees
2	Management
3	Staff

Example # 1

SELECT Firstname, Lastname, salary FROM users WHERE DeptNumber IN (1,3)

Result of the Query

Firstname	Lastname	Salary
John	Smith	1000
Bill	Steve	2200

The Result of the query includes all the data set whose DeptNumber equals to 1 and 3.

Example # 2

Writing the same query as above but in nested query Style.

SELECT Firstname, Lastname, salary FROM Users WHERE DeptNumber
IN
(SELECT DeptNo FROM Departments WHERE DeptNo=1 OR DeptNo=2)

Result of the Query

Firstname	Lastname	Salary
John	Smith	1000
Mathew	Simon	3000

The Result of the query includes all the data set whose DeptNo equals to 1 and 2.

SQL IN Operator

Example # 1

Example # 2

Add new comment