Oracle Tutorials Structured Query Language SQL - WHERE clause
| 
| 
|
|
|
|
|
WHERE Clause - Part 1 |
WHERE Clause - Part 2 |
WHERE Clause - Part 3 |
In this article, you will learn how to restrict data in a table. Restricting data allows you to query data based on a user-defined condition. When we write a query without restriction, the database retrieves all the rows from the table. If we wish to view specific rows based on a condition, we perform the action called restriction, and this is done by using the WHERE clause.
The syntax of the SELECT statement with the WHERE clause is shown below:
SELECT [*, column [, column,...]]
FROM table_name
[WHERE user_defined_condition];
The user_defined_condition always takes the format: Column_name operator Value
The column_name is a column of the table, the operator is a comparison operator or a relational operator and the value is a specific value or expression that is being used in the comparison.
An example, would be : WHERE salary > 5000, here salary is the columname, > is the "greater than" operator, and 5000 is the value.
Operators that can be used in the WHERE clause include:
| Operator | Description | Example |
| = | Equal To | SELECT * FROM EMPLOYEES WHERE SALARY =4000; |
| > | Greater Than | SELECT * FROM EMPLOYEESWHERE SALARY > 4500; |
| < | Less Than | SELECT * FROM EMPLOYEESWHERE SALARY < 6000; |
| != or <> | Not Equal To | SELECT * FROM EMPLOYEESWHERE SALARY <> 4500; |
| >= | Greater than or Equal To | SELECT * FROM EMPLOYEES WHERE SALARY >= 4500; |
| <= | Less than or Equal To | SELECT * FROM EMPLOYEES WHERE SALARY <= 4500; |
When comparing a character or date type of column, the value must be enclosed in single quotes. Character values are case sensitive. Date values are format sensitive and must be written in a 'DD-MON-YY' format.
In the example below, the names of movies that were released by Warner Bros is being retrieved.
SELECT MOVIE_NAME
FROM MOVIE
WHERE STUDIO='Warner Bros';
Other Comparison Operators
| Operator | Description | Example |
| BETWEEN lower_limit AND upper_limit | Used to match rows corresponding to a range of values delimited by the lower_limit and upper_limit (both inclusive) |
SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN 4500 AND 7000; |
| LIKE 'pattern' | Used to match data based on a pattern. Symbols used: % : substitute 0 or more characters _ : substitute 1 single character |
|
| IN (v1,v2…) | Used to match a column with a list of values based on equality | SELECT *FROM EMPLOYEES WHERE SALARY IN (5000,6000); |
|
IS NULL |
Used to compare a NULL value in a column | SELECT *FROM EMPLOYEES WHERE COMMISSION_PCT IS NULL; |
The NOT operator can be included in the WHERE clause, to negate a given condition. The usage of the NOT operator is :
NOT BETWEEN lower_limit and upper_limit
NOT LIKE 'pattern'
NOT IN (v1, v2...)
IS NOT NULL
Multiple conditions can be joined together using the AND or the OR operators. The AND operators combines conditions and retrieves only those rows that satisfy all the conditions matched with the AND operator. The OR operator combines condtions and retrieves the rows that satisfy any of the conditions that are matched with the OR operator. The AND operator has precedence over the OR operator. This default order of precedence can be changed by using parantheses.
In the example below, you are retrieving details of employees who belong to department 80 and have a manager whose ID is 100. Along with this data you are displaying details of all employees whose salary is greater than 8000:
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID=80 and MANAGER_ID=100 OR SALARY > 8000;
In the next modified example,you are retrieving the details of all employees whose manager_id is 100 or earn a salary > 8000 and belong to department 80.
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID=80 and (MANAGER_ID=100 OR SALARY > 8000);
In this article you learnt about the restriction operation in Oracle.
Try the hands-on exercise on Querying the database (Restricting Data), using our Sample Tables
