PostgreSQL – IN Clause
The IN clause is used to compare a value against a list of values or the result set of a subquery. It’s a way to filter rows based on whether a certain value matches any value in a specified list.
The basic syntax of the IN clause is as follows:
SELECT column1, column2, ... FROM table_name WHERE column_name IN (value1, value2, ...);
Here’s an example to illustrate how to use the IN clause:
Suppose you have a table named employees with columns id, name, and department. You want to retrieve all employees who are either in the “IT” department or the “Sales” department.
SELECT id, name, department
FROM employees
WHERE department IN ('IT', 'Sales');
In this query, the IN clause checks whether the value in the department column is present in the list ('IT', 'Sales'). Rows where the department matches either “IT” or “Sales” will be returned.
You can also use the IN clause with subqueries to fetch data based on the results of another query. Here’s an example where you retrieve all employees whose dept_id is among the values selected by the subquery.
SELECT id, name, salary, department
FROM employees
WHERE dept_id IN
(SELECT dept_id
FROM department
WHERE dept_name IN ('IT', 'Sales')
);
In this query, the subquery (SELECT dept_id FROM department WHERE dept_name IN (‘IT’, ‘Sales’)) retrieves the department id for each department listed. The outer query then retrieves employees with department id among the retrieved values.
The IN clause is a useful tool for filtering data based on multiple values or the results of subqueries. It can help you write more concise and efficient queries when dealing with such scenarios.