Back to: Oracle Tutorials for Beginners and Professionals
WHERE Clause in Oracle with Examples
In this article, I am going to discuss the WHERE Clause in Oracle with Examples. Please read our previous article where we discussed Clauses in Oracle. At the end of this article, you will understand the following pointers.
- What is the WHERE Clause in Oracle?
- How to use Where Clause in Oracle?
- Where clause with a Single condition
- Where Clause Using AND Condition
- Where Clause using OR Condition
- Where Clause using both AND & OR Conditions in Oracle
- Where clause with Update Statement
- Where clause with Delete Statement in Oracle
What is the WHERE Clause in Oracle?
The WHERE Conditional clause in the Oracle database is an optional clause used in SQL statements. The WHERE clause acts as a filter on the rows of the result set produced by the FROM clause. It extracts only those records that fulfill the specified condition. The WHERE clause mainly depends upon a condition that evaluates as either be true, false, or unknown. So, if you want to limit the number of rows to be affected by your DML statement (SELECT, INSERT, UPDATE, or DELETE), then you need to use the Where Clause in Oracle.
So, in simple words, we can say that the WHERE Clause is used for filtering rows in a one-by-one manner before grouping data in the result set.
Examples to Understand WHERE Clause in Oracle:
We are going to use the following Employee table to understand the need and use of the Where clause in Oracle with Examples.
Please execute the below SQL query to drop the existing Employee table and create a new Employee table with the required sample data.
SET linesize 300; DROP Table Employee; CREATE TABLE Employee ( Id INT PRIMARY KEY, Name VARCHAR(15), Department VARCHAR(10), Salary NUMBER(8, 2), Gender VARCHAR(10), Age INT, City VARCHAR(10) ); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 25, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 27, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 28, 'Delhi'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', 26, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 'IT', 35000, 'Male', 25, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 'HR', 45000, 'Female', 27, 'Mumbai'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 'IT', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 'IT', 50000, 'Male', 28, 'London'); INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina', 'HR', 75000, 'Female', 26, 'Mumbai');
How to use Where Clause in Oracle?
The following is the syntax to use WHERE Clause in Oracle Database.
Syntax: WHERE Conditions;
Conditions: The conditions that must be met for rows to be selected.
Where clause with a Single condition in Oracle
Let us start with the very basic example i.e. using only a single condition in the where clause in the Oracle SELECT statement. In the below example, we used the WHERE clause to filter the results from the Employee table. The below SQL Select statement will return all the records from the Employee table where Department is IT. As we use * before the FROM clause, the Select Statement will return all the columns from the Employee table.
SELECT * FROM Employee WHERE Department = ‘IT’;
When you execute the above SQL SELECT statement, you will get the following output which includes IT Department employees.
Where Clause Using AND Condition in Oracle:
Now we will see how to use the WHERE filtering clause with AND condition in the Oracle database. Our requirement is to fetch all the employees from the Employee table where Gender is Male and Salary is greater than 35000. To achieve this, we need to use the WHERE conditional clause along with the AND Operator. We generally use the AND operator to define multiple conditions. In our case, we are going to define two conditions (Gender = Male and Salary > 3500) using the AND operator as shown in the below SQL Statements.
SELECT * FROM Employee WHERE Gender = ‘Male’ AND Salary > 35000;
Once you execute the above SQL SELECT statement, then you will get the following output which includes all the employees from the Employee table where Gender is Male and Salary is greater than 35000.
Note: When using AND operator in Oracle, if both the conditions are satisfied then only it returns the records. If one of the conditions is false then it will not include that record in the result set.
Where Clause using OR Condition in Oracle:
Now we will see how to use the WHERE conditional clause along with the OR operator in Oracle. The following example uses OR Operator to define multiple conditions. The following SELECT SQL statement will retrieve all the employees from the Employee table where either Gender is Female or Department is IT.
SELECT * FROM Employee WHERE Gender = ‘Female’ OR Department = ‘IT’;
Once you execute the above SQL SELECT query, you will get the following result set which includes all the Female Employees as well as all Employees who belong to IT Department.
Where Clause using both AND & OR Conditions in Oracle
It is also possible to use both “AND” & “OR” operators in the WHERE in a single SQL statement. The following example uses the WHERE clause to define multiple conditions using AND & OR operators. The following SQL Query will return all employees from the Employee table where either department is IT or Gender is Female and Salary is greater than 35000.
SELECT * FROM Employee WHERE (Gender = ‘Female’ OR Department = ‘IT’) AND Salary > 35000;
Once you execute the above SELECT SQL statement, you will get the following result set as per the conditions.
Where clause with UPDATE Statement in Oracle:
It is also possible in Oracle to use the WHERE conditional clause in the UPDATE SQL Statement i.e. to update data based on some conditions. Let us understand this with an example. Our requirement is to increase the Salary of all IT department employees by 2000. Before updating the Salary of IT Department employees, let’s first have a look at the current salaries of IT Department employees by executing the below SQL Statement.
SELECT * FROM Employee WHERE Department = ‘IT’;
Once you execute the above SELECT statement, you will get the following result set.
Now, execute the below UPDATE SQL Statement along with the WHERE Clause to increase the salary by 2000 of all IT Department employees in the Employee table.
UPDATE Employee SET Salary = Salary + 2000 WHERE Department = ‘IT’;
Once you execute the above SQL UPDATE statement, you should get the following output saying 4 rows are updated.
Now, you can verify whether the salaries are updated or not by executing the below SELECT Statement.
SELECT * FROM Employee WHERE Department = ‘IT’;
As you can see in the below image, the Salaries of IT Department employees are increased by 2000 as expected.
WHERE clause with Delete Statement in Oracle:
It is also possible in Oracle to use the WHERE conditional clause in DELETE DML Statement i.e. to delete data based on some conditions. Let us understand this with an example. Our requirement is to delete all the IT department employees. The following DELETE DML statement will do the same.
DELETE FROM Employee WHERE Department = ‘IT’;
Once you execute the above DELETE statement, then it will delete all the IT Department employees from the Employee table. To verify the same, execute the below SQL Statement.
SELECT * FROM Employee;
Once you execute the above SQL SELECT Statement, you will get the following result and you can see all the IT Department employees are deleted from the Employee table.
Note: The “WHERE” clause in Oracle can only be used in “SELECT “, “UPDATE” and “DELETE” commands.
In the next article, I am going to discuss the ORDER BY Clause in Oracle with Examples. Here, in this article, I try to explain the WHERE Clause in Oracle with Examples and I hope you enjoy this WHERE Clause in Oracle with Examples article.