The Oracle WHERE clause is used to filter out the number of rows/records returned from a SELECT query. It mainly lets you ignore unnecessary data and result only the specific fields/columns that you want to see.
This post is a part of many other topics demonstrating how to use WHERE clause and other related clauses and operators. Click on any of the below to read more about them.
- Oracle WHERE Clause
- SQL Comparison Operators
- SQL IN Operator
- SQL BETWEEN Operator
- SQL LIKE Operator
- IS NULL Operator
- AND and OR Operator
Oralce WHERE Clause
The Oracle WHERE Clause is used to restrict or filter the rows returned from a SELECT query. A SELECT query would consist the below elements, but, WHERE is an optional clause.
- a SELECT clause
- a FROM clause
- a WHERE clause – Optional
- ends with semi-colon ;
The order of the keywords in a SELECT statement must be followed in the same way it is given above.
Syntax to write a SELECT query with WHERE clause in Oracle is –
SELECT * FROM table_name WHERE column_name <operator> value;
column_name is the name of the field you want to apply your restriction.
operator must be an comparison operator and they are denoted by the below symbols;
- = equal to operator
- > greater than operator
- < less than operator
- <> not equal to operator
- <> less than or equal to operator
- >= greater than or equal to operator
For example, the below query displays all the columns/fields from the table Employees.
SELECT * FROM EMPLOYEES;
If you want to see the Name of Employees who are working in Sales department, you would write something like this to achieve it;
SELECT EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT = 'SALES';
SQLFiddle for thus example.
And, the result would be;
To view the Employees who are greater than 55 years old, you can use > greater than operator.
SELECT EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME FROM EMPLOYEES WHERE AGE > 55;
To view the Employees who are receiving 100,000 or more salary in Sales department, you can use >= greater than equal to operator.
SELECT EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME FROM EMPLOYEES WHERE SALARY >= 100000 AND DEPARTMENT = 'SALES';
- You must note that the Value you are specifying in a WHERE clause must be the same as the column it is compared against. Otherwise, Oracle will throw conversion errors.
Thank you for reading.
Please visit https://www.sql-scripts.com for anything that is related SQL Queries, Scripts and How to stuffs.
Tags: Oracle WHERE Clause | SQL WHERE Clause | WHERE Clause in ORACLE | SQL Basics