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:
emp_id | name | department | salary |
|---|
1 | Alice | HR | 5000 |
2 | Bob | IT | 6000 |
3 | Carol | Finance | 5500 |
4 | David | IT | 7000 |
Query:
SELECT name, department, salary
FROM employees
WHERE department = 'IT';
Output:
name | department | salary |
|---|
Bob | IT | 6000 |
David | IT | 7000 |
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:
name | department | salary |
|---|
David | IT | 7000 |
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:
name | department | salary |
|---|
Bob | IT | 6000 |
Carol | Finance | 5500 |
David | IT | 7000 |
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:
name | department | salary |
|---|
Bob | IT | 6000 |
Carol | Finance | 5500 |
David | IT | 7000 |
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.
Similar Reads
SQL | WHERE Clause
The SQL WHERE clause allows to filtering of records in queries. Whether you're retrieving data, updating records, or deleting entries from a database, the WHERE clause plays an important role in defining which rows will be affected by the query. Without it, SQL queries would return all rows in a tab
4 min read
MySQL WHERE Clause
The MySQL WHERE clause is essential for filtering data based on specified conditions and returning it in the result set. It is commonly used in SELECT, INSERT, UPDATE, and DELETE statements to work on specific data. This clause follows the FROM clause in a SELECT statement and precedes any ORDER BY
5 min read
PL/SQL WITH Clause
The PL/SQL WITH clause is a powerful feature that enhances the readability and performance of your SQL queries. It allows you to define temporary result sets, which can be referenced multiple times within a single query. This feature is particularly useful for simplifying complex queries and improvi
5 min read
SQLite WHERE Clause
SQLite is the serverless database engine that is used most widely. It is written in c programming language and it belongs to the embedded database family. In this article, you will be learning about the where clause and functionality of the where clause in SQLite. Where ClauseSQLite WHERE Clause is
3 min read
PostgreSQL - WHERE clause
The PostgreSQL WHERE clause is a critical component of SQL queries, allowing users to filter records based on specified conditions. In this tutorial, we'll explore how the WHERE clause works in PostgreSQL, its integration with the SELECT statement, and various examples. By using the WHERE clause, we
6 min read
Python SQLite - WHERE Clause
Where clause is used in order to make our search results more specific, using the where clause in SQL/SQLite we can go ahead and specify specific conditions that have to be met when retrieving data from the database. If we want to retrieve, update or delete a particular set of data we can use the wh
4 min read
SQL | WITH Clause
SQL queries can sometimes be complex, especially when you need to deal with multiple nested subqueries, aggregations, and joins. This is where the SQL WITH clause also known as Common Table Expressions (CTEs) comes in to make life easier. The WITH Clause is a powerful tool that simplifies complex SQ
6 min read
Having vs Where Clause in SQL
In SQL, filtering data is important for extracting meaningful insights from large datasets. While both the WHERE and HAVING clauses allow us to filter data, they serve distinct purposes and operate at different stages of the query execution process. Understanding the difference between these clauses
4 min read
PL/SQL CASE Statement
PL/SQL stands for Procedural Language Extension to the Structured Query Language and it is designed specifically for Oracle databases it extends Structured Query Language (SQL) capabilities by allowing the creation of stored procedures, functions, and triggers. The PL/SQL CASE statement is a powerfu
4 min read
Python PostgreSQL - Where Clause
In this article, we are going to see how to use the Where clause in PostgreSQL using Psycopg2 in Python. Where Clauses help us to easily deal with the databases. As we know we have a huge amount of data stored in our database, so extracting only useful and required information clauses is helpful. Th
2 min read