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 values. 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)
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
Read On