🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.
▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube
▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube
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 | |
|---|---|---|
| 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 | |
|---|---|---|
| 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 | |
|---|---|---|
| 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 | |
|---|---|---|
| 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.
My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:
Build REST APIs with Spring Boot 4, Spring Security 7, and JWT
![[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25](https://img-c.udemycdn.com/course/750x422/6852721_b512_2.jpg "[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25")
[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25
ChatGPT + Generative AI + Prompt Engineering for Beginners
Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)
Available in Udemy for Business
Building Real-Time REST APIs with Spring Boot - Blog App
Available in Udemy for Business
Building Microservices with Spring Boot and Spring Cloud
Available in Udemy for Business
Java Full-Stack Developer Course with Spring Boot and React JS
Available in Udemy for Business
Build 5 Spring Boot Projects with Java: Line-by-Line Coding
Testing Spring Boot Application with JUnit and Mockito
Available in Udemy for Business
Spring Boot Thymeleaf Real-Time Web Application - Blog App
Available in Udemy for Business
Master Spring Data JPA with Hibernate
Available in Udemy for Business
Spring Boot + Apache Kafka Course - The Practical Guide
Available in Udemy for Business
Comments
Post a Comment
Leave Comment