PostgreSQL IN Operator

The IN operator in PostgreSQL is used to check whether a value matches any value in a list or a subquery. It simplifies multiple OR conditions and makes queries more readable and efficient.

1. Basic Syntax

SELECT column1, column2
            FROM table_name
            WHERE column_name IN (value1, value2, value3, ...);

- Matches if column_name is equal to any of the listed values.

2. Example: Using IN with Static Values

-- Select employees in IT or HR departments
            SELECT name, department
            FROM employees
            WHERE department IN ('IT', 'HR');

3. Using NOT IN

The NOT IN operator returns rows where the column does not match any value in the list.

-- Employees not in IT or HR
            SELECT name, department
            FROM employees
            WHERE department NOT IN ('IT', 'HR');

4. IN with Subquery

You can use a subquery inside IN to compare values dynamically.

-- Employees who work in departments with more than 5 employees
            SELECT name, department_id
            FROM employees
            WHERE department_id IN (
            SELECT department_id
            FROM employees
            GROUP BY department_id
            HAVING COUNT(*) > 5
            );

5. Combining IN with AND, OR

-- Employees in IT or HR with salary > 50000
            SELECT name, department, salary
            FROM employees
            WHERE department IN ('IT', 'HR') AND salary > 50000;

6. IN with JOINs

-- Employees assigned to specific projects
            SELECT e.name, p.project_name
            FROM employees e
            JOIN projects p ON e.id = p.emp_id
            WHERE p.project_name IN ('Project A', 'Project B');

7. Best Practices

• Use IN instead of multiple OR conditions for readability.
• Use NOT IN carefully, as NULL values in the list may produce unexpected results.
• Consider using EXISTS with subqueries for large datasets for better performance.
• Ensure subqueries return a single column when used inside IN.

Conclusion

The IN operator is a simple yet powerful tool for filtering rows based on a list or subquery in PostgreSQL. It improves query readability and can replace multiple OR conditions efficiently. In the next tutorial, we will explore SQL Aliases for renaming columns and tables in queries.