WHERE Clause in Oracle

Where clause in Oracle filters the retrieval of data from a single or multiple objects.

Most of the time, it is not mandatory to fetch the entire table’s data for a piece of information.

The WHERE selects the rows based on a boolean expression. If the condition mentioned in the where clause turns out to be true, data can be shown in the result set.

The main use of WHERE is in SELECT, UPDATE and DELETE statement. It restricts the data to be retrieved, updated or deleted.

It is also an optional clause of the DML (Data Manipulation Language) statement.

Learn Oracle Step by Step

WHERE CLAUSE

Syntax

SELECT columns | expressions
  FROM table_name
 WHERE column_name = value;
Parameters Explanation

columns

  • Single or multiple columns

expressions

  • Expressions to use in the SELECT statement

table_name

  • Single or multiple tables or views

column_name

  • Name of the column to restrict the data

value

  • Value to use in the condition to filter the data

Example

  • Where clause using equal to “=” operator
SELECT *
  FROM employees
 WHERE dept_no = 10;

Instead of entire data, this query fetches the data of only department number 10.

  • Where clause using IN operator
SELECT *
  FROM employees
 WHERE dept_no IN (10, 30);

In this example, IN clause restricts the data up to departments 10 and 30.

  • The where clause using BETWEEN operator
SELECT *
  FROM employees
 WHERE TRUNC(hire_date) BETWEEN SYSDATE – 10 AND SYSDATE;

Here, the employees who are recruited in last 10 days are fetched using BETWEEN operator.

Subsequently, the WHERE is also used with <, >, =, >=, <=, EXISTS and other operators.

The use of where clause in ANSI SQL is identical to what I have described in this article.

Please feel free to write us at connect@oraclemine.com or hioraclemine@gmail.com if you find mistakes or errors in any of the topics. Your suggestions to improve the course will help OracleMine and its readers.

You can also Subscribe here to stay updated on the latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.