SQL IN Operator

Introduction

In this chapter, we will learn how to use the IN operator in SQL. The IN operator is used to filter the result set based on a list of specified values. This chapter will cover the syntax, usage, and provide examples to help you understand how to use the IN operator effectively.

What is the IN Operator?

The IN operator is used to filter rows based on whether a specified column's value matches any value in a given list. It simplifies the syntax and improves readability when checking for multiple values in a WHERE clause.

Syntax for IN

Basic Syntax

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

• column1, column2, ...: The columns you want to retrieve.
• table_name: The name of the table from which you want to retrieve data.
• column_name: The column to be checked.
• value1, value2, ...: The list of values to check against.

Example

Assume we have a table named employees:

SELECT first_name, last_name, email
FROM employees
WHERE department_id IN (1, 2);

This command retrieves the first_name, last_name, and email columns from the employees table where the department_id is either 1 or 2.

Step-by-Step Example

1. Create a Sample Table

First, we will create a sample tables - employees and departments.

CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);

CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
department_id INT,
salary DECIMAL(10, 2)
);

2. Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');


INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ramesh', 'Kumar', 'ramesh.kumar2@example.com', 3, 50000);

3. Using the IN Operator

To retrieve employees in departments 1 and 2:

SELECT first_name, last_name, email
FROM employees
WHERE department_id IN (1, 2);

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com

4. Using NOT IN Operator

The NOT IN operator is used to filter rows where the column value does not match any value in the given list.

Example

To retrieve employees not in departments 1 and 2:

SELECT first_name, last_name, email
FROM employees
WHERE department_id NOT IN (1, 2);

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar2@example.com

Using IN with Subqueries

You can use the IN operator with subqueries to filter rows based on a dynamic list of values.

Example

Assume we have another table named departments:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

To retrieve employees in departments with names 'HR' and 'Finance':

SELECT first_name, last_name, email
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('HR', 'Finance')
);

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Arjun	Singh	arjun.singh@example.com
Priya	Sharma	priya.sharma@example.com

Using IN with Strings

The IN operator can also be used with string values.

Example

To retrieve employees with the first name 'Ramesh' or 'Sita':

SELECT first_name, last_name, email
FROM employees
WHERE first_name IN ('Ramesh', 'Sita');

Output

first_name	last_name	email
Ramesh	Kumar	ramesh.kumar@example.com
Sita	Patel	sita.patel@example.com
Ramesh	Kumar	ramesh.kumar2@example.com

Conclusion

The IN operator is used for filtering rows based on a list of specified values. This chapter covered the basic syntax, using IN with subqueries, and using NOT IN to exclude values. Understanding how to use the IN operator effectively will enhance your ability to query and analyze your database data.