An ‘IN’ clause is used to specify 1 or more values in the WHERE clause.
Syntax:
SELECT * FROM table_name WHERE column_name IN ('value1','value2','value3',...);
Consider an employee table as shown below:
| id | first_name | last_name | department | salary |
| 101 | bryan | walker | sales | 50,000 |
| 102 | alex | carpenter | accounts | 55,000 |
| 103 | sam | white | sales | 60,000 |
| 104 | john | barry | sales | 54,000 |
| 105 | bill | wood | accounts | 65,000 |
| 106 | robert | hopkins | support | 70,000 |
| 107 | david | lee | support | 72,000 |
| 108 | jack | sears | support | 65,000 |
| 109 | tom | young | sales | 55,000 |
Below is the SQL query to return all rows from the employee table. Returning all rows does not require a WHERE clause.
SELECT * FROM employee; id first_name last_name department salary --- --------- --------- ---------- ------- 101 bryan walker sales 50000 102 alex carpenter accounts 55000 . . .
Let’s assume you need to view the details of only the employees who work in the ‘sales’ department. To filter the rows, you would use the ‘WHERE’ clause as shown below:
SELECT * FROM employee WHERE department='sales'; id first_name last_name department salary --- --------- --------- ---------- ------- 101 bryan walker sales 50000 103 sam white sales 60000 104 john barry sales 54000 109 tom young sales 55000
Now, if you need to specify more than one department in the ‘WHERE’ clause, ‘IN’ clause can be used as shown below:
SELECT * FROM employee WHERE department IN ('sales','accounts');
id first_name last_name department salary
--- --------- --------- ---------- -------
101 bryan walker sales 50000
102 alex carpenter accounts 55000
103 sam white sales 60000
104 john barry sales 54000
105 bill wood accounts 65000
109 tom young sales 55000