SQL IN Operator
The SQL IN operator allows you to specify multiple values in a WHERE clause. It’s particularly useful when you want to filter rows based on a set of values rather than a single value. Here’s the basic syntax:
|
1 2 3 |
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...); |
Here’s an example to illustrate its usage:
Let’s say we have a table named students with columns student_id, student_name, and age. We want to retrieve the details of students who are either 18, 19, or 20 years old
|
1 2 3 |
SELECT student_id, student_name, age FROM students WHERE age IN (18, 19, 20); |
This query will fetch all the rows where the age column contains values 18, 19, or 20.
Another example: Suppose you have a table named orders with a column order_id, and you want to retrieve details of orders with specific order IDs.
|
1 2 3 |
SELECT * FROM orders WHERE order_id IN (1001, 1005, 1009); |
This query will return all rows from the orders table where the order_id matches any of the specified values (1001, 1005, or 1009).
Suppose we have a table called employees with the following structure:
| employee_id | employee_name | department | salary |
|---|---|---|---|
| 1 | Alice | HR | 50000 |
| 2 | Bob | Sales | 60000 |
| 3 | Charlie | Marketing | 55000 |
| 4 | David | HR | 48000 |
| 5 | Eve | Sales | 62000 |
Now, let’s say we want to retrieve the details of employees whose departments are either ‘HR’ or ‘Marketing’. We can use the IN operator to achieve this:
|
1 2 3 |
SELECT employee_id, employee_name, department, salary FROM employees WHERE department IN ('HR', 'Marketing'); |
When you run this query, it will return the following result:
| employee_id | employee_name | department | salary |
|---|---|---|---|
| 1 | Alice | HR | 50000 |
| 3 | Charlie | Marketing | 55000 |
| 4 | David | HR | 48000 |
Here, the WHERE clause filters out rows where the department column matches either ‘HR’ or ‘Marketing’, resulting in the specified rows being returned.
This is just a basic example. The IN operator can be used with more complex queries and with different types of data, but the principle remains the same: it allows you to specify multiple values for filtering in a concise and readable way.
