Using WHERE Clause in Oracle

This article is written about how to use the Oracle WHERE clause with syntax and examples.

Description

The Oracle WHERE clause is used to filter the consequences from a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the WHERE clause in Oracle/PLSQL is:

WHERE conditions;

Parameters or Arguments

conditions The prerequisites that ought to be met for archives to be selected.

Example – With Single condition

It is hard to provide an explanation for the syntax for the Oracle WHERE clause, so let’s look at some examples.

SELECT *
FROM customers
WHERE last_name = 'Anderson';

In this Oracle WHERE clause example, we have used the WHERE clause to filter our results from the clients table. The SELECT assertion above would return all rows from the customers desk where the last_name is Anderson. Because the * is used in the SELECT, all fields from the clients desk would show up in the end result set.

Example – Using AND condition

SELECT *
FROM suppliers
WHERE state = 'California'
AND supplier_id <= 750;

This Oracle WHERE clause example uses the WHERE clause to outline a couple of conditions. In this case, this SELECT declaration uses the AND condition to return all suppliers that are placed in the nation of California and whose supplier_id is less than or equal to 750.

Example – Using OR condition

SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'Apple'
OR supplier_name = 'Microsoft';

This Oracle WHERE clause instance makes use of the WHERE clause to define multiple conditions, but alternatively of the usage of the AND condition, it uses the OR condition. In this case, this SELECT assertion would return all supplier_id values where the supplier_name is Apple or Microsoft.

Example – Combining AND & OR conditions

SELECT *
FROM suppliers
WHERE (state = 'Florida' AND supplier_name = 'IBM')
OR (supplier_id > 5000);

This Oracle WHERE clause example makes use of the WHERE clause to outline a couple of conditions, but it combines the AND situation and the OR condition. This example would return all suppliers that stay in the kingdom of Florida and whose supplier_name is IBM as well as all suppliers whose supplier_id is higher than 5000.

The parentheses determine the order that the AND and OR conditions are evaluated. Just like you discovered in the order of operations in Math class!

Example – Joining Tables

SELECT suppliers.suppler_name, orders.order_id
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.state = 'California';

This Oracle WHERE clause example makes use of the WHERE clause to be part of more than one tables together in a single SELECT statement. This SELECT declaration would return all supplier_name and order_id values the place there is a matching record in the suppliers and orders tables primarily based on supplier_id, and the place the supplier’s country is California.

Learn more about Oracle joins.