IN Operator in PostgreSQL

The IN operator is used in PostgreSQL to check if a value matches any value in a list or a subquery result.

1. Using IN with a List of Values

SELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance');

✅ Retrieves employees working in IT, HR, or Finance.

2. Using NOT IN to Exclude Values

SELECT * FROM employees WHERE department NOT IN ('Sales', 'Marketing');

✅ Retrieves employees not in the Sales or Marketing departments.

3. Using IN with a Subquery

SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

✅ Retrieves employees whose department is located in New York.

4. Using IN with Numeric Values

SELECT * FROM orders WHERE order_status IN (1, 2, 3);

✅ Retrieves orders where order_status is 1, 2, or 3.

5. IN vs = (Performance Consideration)

• IN is better than multiple OR conditions:

  -- ❌ Less efficientSELECT * FROM employees WHERE department = 'IT' OR department = 'HR' OR department = 'Finance';
  

  -- ✅ More efficientSELECT * FROM employees WHERE department IN ('IT', 'HR', 'Finance');
  

• For large datasets, use EXISTS or JOIN instead of IN with subqueries for better performance.