PL/SQL IN Operator

The PL/SQL IN operator is a powerful tool used in SQL queries to check if a value matches any value in a list or a subquery result. It simplifies querying multiple values and can make your SQL code cleaner and more readable.

The IN operator is typically used in the WHERE clause to filter results based on a specified list of values. In this article, we will explain the concept of PL/SQL IN Operator in detail with proper syntax, examples, and output.

What is PL/SQL IN Operator?

The IN operator allows you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions. You can use it to simplify queries that involve checking a column for multiple possible values.

Simple Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE column_name IN (value1, value2, ...);

Explanation :

This query selects specific columns (column1, column2, etc.) from a table where the values in column_name match any of the values listed in the IN clause (value1, value2, etc.). The IN operator simplifies filtering by allowing multiple criteria in a single condition.

With Subquery:

SELECT column1, column2, ...

FROM table_name

WHERE column_name IN (SELECT column_name FROM other_table WHERE condition);

Explanation :

This query retrieves specific columns from a table where the values in column_name match any values returned by a subquery. The subquery selects column_name from another table based on a specified condition, allowing you to filter data in the main query based on dynamically retrieved results from the subquery.

Example of PL/SQL IN Operator

The IN operator in PL/SQL is used to filter data by specifying a set of values or a subquery that the target column must match. It simplifies SQL queries by allowing you to compare a column against multiple values in a single condition, making the code more concise and readable.

The IN operator is especially useful when you need to check if a column value exists within a predefined list or a result set from a subquery. It eliminates the need for multiple OR conditions and helps in managing complex queries more effectively.

1. Employees Table

Query:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT
);

INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (1, 'Alice', 10);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (2, 'Bob', 20);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (3, 'Charlie', 10);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (4, 'David', 30);
INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (5, 'Eve', 20);

Output:

Explanation:

The output shows the contents of the employees table after inserting five records. Each row represents an employee with a unique emp_id, their emp_name, and the dept_id indicating the department they belong to. The table accurately reflects the data inserted through the provided SQL queries.

2. Departments Table

Query:

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

INSERT INTO departments (dept_id, dept_name) VALUES (10, 'HR');
INSERT INTO departments (dept_id, dept_name) VALUES (20, 'Finance');
INSERT INTO departments (dept_id, dept_name) VALUES (30, 'IT');

Output:

Explanation:

The output displays the contents of the departments table after inserting three records. Each row shows a unique dept_id paired with its corresponding dept_name, representing different departments: HR, Finance, and IT. This table correctly reflects the data inserted through the provided SQL queries

Example 1: Retrieve Employees in Specific Departments

The query selects the names of employees who belong to specific departments identified by their IDs. It checks if the dept_id of each employee matches any of the given department IDs (e.g., 10 or 20).

Query:

SELECT emp_name
FROM employees
WHERE dept_id IN (10, 20);

Output:

Explanation:

• The result includes names of employees who belong to departments 10 (HR) or 20 (Finance).
  
• This output displays all employees whose dept_id matches either of these values.

Example 2: Find Departments with Employees

This query identifies the departments that have at least one employee. It uses a subquery to retrieve department IDs from the employees table and then checks if each department in the departments table matches any of these IDs.

Query:

SELECT dept_name
FROM departments
WHERE dept_id IN (SELECT dept_id FROM employees);

Output:

Explanation:

• The result lists department names that have at least one employee.
  
• The departments returned are those with IDs present in the employees table.

Example 3: List Employees Not in Specific Departments

The query lists employees who are not in a particular department. By excluding a specific dept_id (e.g., 30), it retrieves names of employees from all other departments.

Query:

SELECT emp_name
FROM employees
WHERE dept_id NOT IN (30);

Output:

Explanation:

• The result displays names of employees not working in the department with ID 30 (IT).
  
• Employees from all other departments are included in the result.

Example 4: Retrieve Employees from Departments Listed in Another Table

This query finds employees who work in departments that are listed in the departments table with specific names (e.g., 'HR' or 'Finance'). It uses a subquery to select department IDs based on department names and then retrieves employee names corresponding to those IDs.

Query:

SELECT e.emp_name
FROM employees e
WHERE e.dept_id IN (SELECT d.dept_id FROM departments d WHERE d.dept_name IN ('HR', 'Finance'));

Output:

Explanation:

• The result shows names of employees working in departments named 'HR' or 'Finance'.
  
• This output is filtered based on department names found in the departments table.

Conclusion

The PL/SQL IN operator is a powerful and flexible tool for querying data based on a set of values or the result of a subquery. It allows you to efficiently filter records by specifying multiple criteria in a single condition, simplifying your SQL code and improving its readability.

The IN operator is particularly useful when you need to match a column against a list of possible values or when working with subqueries that return multiple rows. By using IN, you can reduce the need for complex OR conditions and streamline your queries, making your PL/SQL programs more maintainable and efficient.