login

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