Oracle WHERE Clause

Introduction:

WHERE clause is used in Oracle to limit the rows in output using one or more conditions. Sometimes we need only limited data from the columns, instead of entire rows. We use WHERE clause to filter the data.

SELECT
    column_names
FROM
    table_name
WHERE
    condition;

We can also use ORDER BY statement after the WHERE condition to sort the data in ascending or descending order. WHERE clause is also used with DELETE and UPDATE statements to specify which rows to delete.

WHERE Clause Examples :

We will perform SQL queries on Employees Table using WHERE clause. Lets have a look at the table:

If we need to fetch the employee with last name as ‘Roger’ we use this query.

SELECT employee_id, first_name, salary 
FROM employees 
WHERE last_name = 'Rogers';

Result:

2. WHERE clause using Comparison Operators (= > >= < <= <>)

We have several Comparison operators in Oracle (– = > >= < <= <>) which we can use with WHERE clause to compare data in rows and give us specific results.

Some Examples of WHERE clause with comparison operators:

To find employees who have salary greater than 4000, we use this query:

SELECT first_name, salary 
FROM employees 
WHERE salary >= 4000;

Result:

To find employees whose salary is not equal to 4500, we use this query. It will give us all the employees with salary other than 4500.

SELECT first_name, salary 
FROM employees 
WHERE salary <> 4500;

Result:

To find employees whose salary is not equal to 5000:

SELECT first_name, salary 
FROM employees 
WHERE salary != 5000;

Result:

3. WHERE clause using multiple conditions ( AND)

We can also use WHERE clause with multiple conditions using the AND, OR or NOT operators. Lets have a look at one of the examples:

Suppose we want to find employees from Department_ID= 50 with Salary greater than 3000, we use this query:

SELECT first_name, salary FROM employees 
WHERE salary>= 3000
AND
department_id = 50;

Result:

If we want to find employees with salary greater than 3000 or less than 1000, we use this query:

SELECT first_name, salary FROM employees 
WHERE salary >= 3000 
OR salary <= 1000;

Result:

4.WHERE clause using Range ( BETWEEN, IN)

We can check whether the value of rows is within a specific range using WHERE clause with BETWEEN and AND operators.

To find employees within the range of 1000 to 4000 we use this query:

SELECT first_name, salary 
FROM employees 
WHERE salary BETWEEN 1000 AND 4000;

Result:

— IN

Using the IN operator, we can fetch rows whose value match against a set of values given by us.

If we only want to return employees from department 30, 50 and 100 we use this query:

SELECT employee_id, first_name, salary, manager_id 
FROM employees 
WHERE department_id IN (30, 50, 100);

Result:

5. WHERE clause using LIKE operator

The WHERE clause can be used with LIKE operator to spot patterns in string data type or text. This is done by using LIKE operator with two special symbols (‘%’ and ‘_’). Lets see how these symbols work using some queries.

Suppose we want to get employees whose first name starts with ‘A’, then we use this query. ‘%’ denotes that there can be as many characters after letter A.

SELECT first_name 
FROM employees 
WHERE first_name LIKE 'A%';

Result:

Suppose we want to get employees whose first name starts with ‘A’, then we use this query. ‘_’ denotes that there can be only one character after letter A. Ex. aa, ab, ac etc.

SELECT first_name 
FROM employees 
WHERE first_name LIKE 'A_';

Result:

6. WHERE clause with ORDER BY

ORDER BY is used with WHERE clause to sort data in a specific way. ASC denotes ascending order and DESC denotes descending order.

If we want name of all employees arranged in alphabetical order than we can do it this way:

SELECT first_name, job_id, department_id, hire_date 
FROM employees 
WHERE salary > 10000
ORDER BY first_name ASC;

Result: