Previous

SQL IN Operator

The IN operator in SQL is used to filter records where a column value matches any value in a given list of values. It simplifies multiple OR conditions.

 

Basic Syntax of IN Operator

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);
  • column_name: The column to filter.
  • value1, value2, ...: A list of values to compare against.

 

Key Features of IN

1. Simplifies Multiple OR Conditions

IN replaces long OR conditions.

SELECT name, department  
FROM employees  
WHERE department IN ('HR', 'IT', 'Finance');
  • Retrieves employees working in HR, IT, or Finance departments.
  • Equivalent to:
WHERE department = 'HR' OR department = 'IT' OR department = 'Finance';

2. Works with Numbers

IN can match numeric values.

SELECT product_name, price  
FROM products  
WHERE price IN (100, 200, 300);
  • Retrieves products with prices of 100, 200, or 300.

3. Works with Text Values

IN also matches text values.

SELECT customer_name, city  
FROM customers  
WHERE city IN ('New York', 'Los Angeles', 'Chicago');
  • Retrieves customers from New York, Los Angeles, or Chicago.

4. Works with Subqueries

You can use a subquery to dynamically provide values for IN.

SELECT name, salary  
FROM employees  
WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');
  • Retrieves employees working in departments located in New York.

5. NOT IN

NOT IN excludes values from the specified list.

SELECT name, department  
FROM employees  
WHERE department NOT IN ('HR', 'IT');
  • Retrieves employees not working in HR or IT.

 

Example Queries

1. Filter by Specific Cities

SELECT customer_name, city  
FROM customers  
WHERE city IN ('Boston', 'Chicago', 'San Francisco');
  • Retrieves customers located in specific cities.

2. Filter Products by Price

SELECT product_name, price  
FROM products  
WHERE price IN (50, 150, 300);
  • Retrieves products priced at 50, 150, or 300.

3. Exclude Departments

SELECT name, department  
FROM employees  
WHERE department NOT IN ('Sales', 'Marketing');
  • Retrieves employees not working in Sales or Marketing.

4. Use IN with Subqueries

SELECT order_id, customer_id  
FROM orders  
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Los Angeles');
  • Retrieves orders made by customers living in Los Angeles.

 

Combining IN with Other Conditions

You can combine IN with AND or OR for advanced filtering.

SELECT name, salary  
FROM employees  
WHERE salary IN (3000, 4000, 5000) AND department = 'IT';
  • Retrieves IT employees with specific salaries.

 

Summary

The IN operator is a concise way to filter records that match any value from a list. It replaces multiple OR conditions and supports numbers, text, and subqueries. For exclusion, use the NOT IN variation. department = 'Finance';

Previous