Using SQL IN
SQL IN allows you to limit the result set in a specified value list. Here is the syntax of using SQL IN :
SELECT column_list FROM table WHERE column IN (value_list_expression)
The value_list_expression can be any constant value list or selection query which returns a list of value. Let take a look at SQL IN in the following examples. We will use two sample tables
employees table data
employee_id name department_id job_id salary
----------- -------- ------------- ------ -------
1 jack 1 1 3000.00
2 mary 2 2 2500.00
3 newcomer (NULL) 0 2000.00
4 anna 1 1 2800.00
5 Tom 2 2 2700.00
6 foo 3 3 4700.00
and departments table data
department_id name
------------- --------------------
1 Software Engineering
2 Sale
3 Marketing
4 HR
Here is an example of using SQL IN to retrieve employee which has salary is 2000 or 3000
SELECT name,salary FROM employees WHERE salary IN (2000,3000)
The output is
name salary -------- ------- jack 3000.00 newcomer 2000.00
And here is another example of using SQL IN which retrieves all in employees in software engineering department:
SELECT name, salary
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE departments.name = 'Software Engineering'
)
The database system returns two employees which work in software engineering department
name salary ------ ------- jack 3000.00 anna 2800.00