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.
It is also an optional clause of the DML (Data Manipulation Language) statement.
SELECT columns | expressions FROM table_name WHERE column_name = value;
- Single or multiple columns
- Expressions to use in the SELECT statement
- Single or multiple tables or views
- Name of the column to restrict the data
- Value to use in the condition to filter the data
- 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 email@example.com or firstname.lastname@example.org if you find mistakes or errors in any of the topics. Your suggestions to improve the course will help OracleMine and its readers.
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 email@example.com. Again I appreciate your visit. Hope to see you again and again!