Oracle Where Condition Usage

**Understanding Oracle's WHERE Condition: A Step-by-Step Guide for Beginners**

The `WHERE` condition is a fundamental aspect of Oracle SQL that allows you to filter rows based on specified criteria. For beginners just starting with Oracle, understanding how to use the `WHERE` condition is crucial for retrieving specific data and performing targeted queries.


In this blog, we'll walk you through the `WHERE` condition step by step, providing clear explanations and real-time examples to help you grasp its significance and usage effectively.

### What is the WHERE Condition?
The `WHERE` condition is used to filter rows returned by a query based on one or more specified conditions.
It allows you to narrow down the result set to meet specific criteria.
The syntax for `WHERE` is straightforward and easy to understand.


SELECT column1, column2


FROM table_name


WHERE condition;


### Basic Usage of WHERE


Let's start with a simple example to understand the basic usage of `WHERE`.


Suppose we have an `employees` table with `salary` and `department` columns.


To retrieve employees who belong to the 'IT' department:


SELECT employee_name, salary


FROM employees


WHERE department = 'IT';


### Real-time Examples:


1. **Filtering by Numeric Value**


To retrieve employees with a salary greater than 5000:


SELECT employee_name, salary


FROM employees


WHERE salary > 5000;


2. **Filtering by Text Pattern**


To fetch products whose name starts with 'A':


SELECT product_name, unit_price


FROM products


WHERE product_name LIKE 'A%';


3. **Filtering by Date Range**


To retrieve orders placed in January 2022:


SELECT order_id, order_date


FROM orders


WHERE order_date BETWEEN '01-JAN-2022' AND '31-JAN-2022';


4. **Filtering by Multiple Values**


To fetch employees from either 'IT' or 'HR' departments:


SELECT employee_name, department


FROM employees


WHERE department IN ('IT', 'HR');


5. **Excluding Specific Values**


To retrieve products that are not out of stock:


SELECT product_name, stock_quantity


FROM products


WHERE stock_quantity > 0;


6. **Combining Conditions with AND**


To fetch employees with a salary greater than 5000 and belong to the 'IT' department:


SELECT employee_name, salary


FROM employees


WHERE salary > 5000 AND department = 'IT';


7. **Combining Conditions with OR**


To retrieve products with a unit price less than 50 or quantity greater than 100:


SELECT product_name, unit_price, stock_quantity


FROM products


WHERE unit_price < 50 OR stock_quantity > 100;


8. **Using IS NULL**


To fetch employees without a manager:


SELECT employee_name


FROM employees


WHERE manager_id IS NULL;


9. **Using IS NOT NULL**


To retrieve products with a description:


SELECT product_name


FROM products


WHERE description IS NOT NULL;


10. **Using NOT LIKE**


To fetch products not manufactured by 'ABC Corp':


SELECT product_name


FROM products


WHERE manufacturer_name NOT LIKE 'ABC Corp%';


11. **Using BETWEEN with Dates**


To retrieve orders placed between January 3, 2022, and March 31, 2022:


SELECT order_id, order_date


FROM orders


WHERE order_date BETWEEN '01-JAN-2022' AND '31-MAR-2022';


12. **Using UPPER or LOWER**


To fetch employees whose names start with 'J' (case-insensitive):


SELECT employee_name


FROM employees


WHERE UPPER(employee_name) LIKE 'J%';


Important Notes: To know more about Oracle Queries, Please go through the below youtube video links:-----------

Topics that are covered in Videos: Visit Oracle Database tutorial


Oracle SQL Query Part 1


Oracle SQL Query part 2


Oracle Dual table and Alias Column


Oracle fetch clause, Oracle in and not in operator


Oracle between, Oracle Like Operator


Oracle IS NULL operator, , Oracle Joins (inner, left, right and full outer join)


Oracle Cross Join, Oracle self join


Oracle UNION, Oracle intersect, Oracle minus


Oracle Subquery, Oracle corelated subquery with exists operator


Oracle exists update stmt, ,Oracle Any operator


Oracle All operator, view creation, Oracle Rollup


Oracle PIVOT clause, Oracle UNPIVOT


Oracle INSERT , Oracle INSERT INTO SELECT


Oracle Update, Oracle Delete


Oracle MERGE statement


UnConditional Oracle INSERT ALL statement, Conditional Oracle INSERT ALL statement


Oracle CASE Expression


### Conclusion


The `WHERE` condition is a powerful feature in Oracle SQL that allows you to filter rows based on specified criteria.


From basic filtering to complex conditions, the `WHERE` clause offers versatility to meet various data retrieval needs.


By understanding its syntax and applying it with real-time examples, beginners can master the `WHERE` condition and enhance their SQL skills.


Practicing and experimenting with different scenarios will help you gain confidence in using the `WHERE` condition effectively in your Oracle database queries.