ORACLE WHERE Syntax
ORACLE
The Oracle WHERE clause is an optional part of SELECT, UPDATE, and DELETE statements, used to filter records based on specific conditions.
Oracle WHERE Clause
➔ The WHERE clause must come after the FROM clause but before other clauses such as GROUP BY, HAVING, or ORDER BY, and is a standard SQL specification.
➔ The WHERE clause applies conditions to filter the rows retrieved by the SELECT clause.
➔ In addition to the SELECT statement, the WHERE clause can also be used in the DELETE or UPDATE statement to specify which rows to update or delete.
Basic Syntax
SELECT column_list
FROM table_name
WHERE condition;
Column aliases defined in the SELECT statement cannot be used in the WHERE condition.
Clause order in Syntax structure:
SELECT first_name AS "NAME", employee_id, salary AS "INCOME"
FROM hr.employees
WHERE salary > 11000
ORDER BY employee_id;
Actual Clause order when executing internally
FROM hr.employees
WHERE salary > 11000
SELECT first_name AS "NAME", employee_id, salary AS "INCOME"
ORDER BY employee_id;
Explanation: Because Oracle evaluates the WHERE clause before the SELECT clause, column aliases in the SELECT statement cannot be used in the WHERE clause. This is because when Oracle evaluates the WHERE clause, the column aliases in the SELECT statement are not available.
Selecting rows using the most common equality operator:
To compare string or datetime data in the WHERE clause, the value must be enclosed in single quotes.
SELECT first_name AS "NAME", employee_id, salary AS "INCOME"
FROM hr.employees
WHERE first_name = 'Steven';
To compare numeric data in a WHERE clause, you don't need to enclose the value in single quotes, just put the value without the quotes.
SELECT first_name AS "NAME", employee_id, salary AS "INCOME"
FROM hr.employees
WHERE employee_id = 100;
Comparison operators used in WHERE clause
| Operato | Description |
|---|---|
| = | Equality |
| !=,<> | Inequality |
| > | Greater than. |
| < | Less than. |
| >= | Greater than or equal to. |
| <= | Less than or equal to. |
| IN | Equal to any value in a list of values. |
| ANY/ SOME / ALL | Compare a value to a list or subquery. It must be preceded by another operator such as =, >, <. |
| NOT IN | Not equal to any value in a list of values. Oposite to IN. |
| [NOT] BETWEEN n and m | Equivalent to [Not] >= n and <= y. |
| [NOT] EXISTS | Return boolean true if the subquery returns at least one row. |
| IS [NOT] NULL | NULL test for data. |
Filtering rows by multiple conditions:
The AND, OR, and NOT logical operators can be used to apply multiple conditions and create complex queries.
SELECT first_name AS "NAME", employee_id, salary AS "INCOME"
FROM hr.employees
WHERE employee_id > 198 AND salary <8000;
Filtering rows according to string patterns using the like operator.
To select rows where the value is part of a string, the LIKE operator can be used to specify the pattern of the string value.
SELECT first_name AS "NAME", employee_id, salary AS "INCOME"
FROM hr.employees
WHERE first_name LIKE '%st%';