Skip to main content

SQL IN Operator

Body

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.

Add new comment