PL/SQL WHERE Clause

The WHERE clause in PL/SQL is essential for filtering records based on specified conditions. It is used in SELECT, UPDATE, and DELETE statements to limit the rows affected or retrieved, allowing precise control over data manipulation and retrieval.

In this article, We will learn about the WHERE Clause in PL/SQL by understanding various examples and so on.

PL/SQL WHERE Clause

• In PL/SQL, the WHERE clause is a powerful tool used to filter records that meet specific conditions.
  
• It is often used in SELECT, UPDATE, DELETE, and other SQL statements to restrict the rows affected by these operations.

Syntax of the WHERE Clause:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Parameters:

• column1, column2, ...: The names of the columns you want to retrieve.
  
• table_name: The name of the table from which to retrieve the data.
  
• condition: The condition to filter the rows. Only rows that meet this condition will be included in the result set.

Using the WHERE Clause in PL/SQL

The WHERE clause can be used in various PL/SQL operations to filter records. Below are examples demonstrating its use in SELECT, UPDATE, and DELETE statements.

1. WHERE Clause in SELECT Statement

Example:

Let's say we have a table 'employees' with the following structure:

Query:

SELECT name, department, salary
FROM employees
WHERE department = 'IT';

Output:

Explanation: This query retrieves the names, departments, and salaries of employees who work in the IT department.

2. WHERE Clause in UPDATE Statement

Example:

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'HR';

Explanation: This query increases the salary of all employees in the HR department by 10%.

3. WHERE Clause in DELETE Statement

Example:

DELETE FROM employees
WHERE emp_id = 3;

Explanation: This query deletes the record of the employee with emp_id 3 from the employees table.

4. WHERE Clause with Multiple Conditions

The WHERE clause can also include multiple conditions using logical operators such as AND, OR, and NOT.

Example:

SELECT name, department, salary
FROM employees
WHERE department = 'IT' AND salary > 6500;

Output:

5. WHERE Clause with IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

Example:

SELECT name, department, salary
FROM employees
WHERE department IN ('IT', 'Finance');

Output:

Explanation: This query retrieves the names, departments, and salaries of employees who work in either the IT or Finance departments.

6. WHERE Clause with BETWEEN Operator

The BETWEEN operator is used to filter records within a certain range.

Example:

SELECT name, department, salary
FROM employees
WHERE salary BETWEEN 5500 AND 7000;

Output:

Explanation: This query retrieves the names, departments, and salaries of employees whose salary is between 5500 and 7000.

Conclusion

The WHERE clause is an essential component of SQL and PL/SQL for filtering records based on specific conditions. By using the WHERE clause effectively, you can retrieve, update, or delete precise subsets of data, making your database operations more efficient and targeted.