Oracle Where Clause

The Oracle WHERE clause is used to filter rows based on a particular condition. The WHERE clause is used with SELECT, INSERT, UPDATE and DELETE statements. Oracle allows you to use operator with Where clause.

Oracle WHERE CLAUSE Syntax

To fetch the data based on specific condition, use the following syntax:

Syntax

SELECT * FROM tablename WHERE conditions;

Parameters:

conditions: To specify the condition that must be fulfilled for records to be selected.

Oracle WHERE CLAUSE example with single condition

The following Oracle statement to fetch data from a table called "tblCustomerLoan":

Example

SELECT * FROM tblCustomerLoan WHERE cellNo = 2888888980;

Note:

In the above example, we have fetched record from "tblCustomerLoan" table with cellNo "2888888980".

Oracle WHERE CLAUSE example with AND condition

The following Oracle statement to retrieve data from a table called "tblCustomerLoan" with AND condition:

Example

SELECT * FROM tblCustomerLoan WHERE cellNo = 2888888980 AND aadharNo = '123456789012';

Note:

In the above example, we have fetch records from "tblCustomerLoan" table with cellNo "2888888980" AND aadharNo equal to "123456789012".

Oracle WHERE CLAUSE example with OR condition

The following Oracle statement to retrieve data from a table called "tblCustomerLoan" with OR condition:

Example

SELECT * FROM tblCustomerLoan WHERE cellNo = 2888888980 OR aadharNo = '123456789012';

Note:

In the above example, we have fetch records from "tblCustomerLoan" table with cellNo "2888888980" OR aadharNo equal to "123456789012".

Oracle WHERE CLAUSE example with AND & OR conditions

The following Oracle statement that allows you to use multiple condition like AND & OR with WHERE clause to retrieve data from a table called "tblCustomerLoan":

Example

SELECT * FROM tblCustomerLoan WHERE cellNo = 2888888980 OR aadharNo = '123456789012' AND address = 'A/12, Opp. PVR Cinema, Hyderabad';

Note:

In the above example, we have fetch records from "tblCustomerLoan" table with cellNo "2888888980" OR aadharNo equal to "123456789012".

More on Oracle WHERE clause...

Some more useful operators that you can use with WHERE clause to form a complex conditions such as:

  1. BETWEEN - To select value within given range.
  2. LIKE - To matche value based on pattern matching.
  3. IN - To matches value in a given list.
  4. IS - To check NULL value.

The WHERE clause is used with SELECT, INSERT, UPDATE and DELETE statements as well.