Hi,
I will explain SQL WHERE Clause in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
https://ittutorial.org/select-distinct-statement-in-oracle-sql-oracle-sql-tutorials-1/
SQL WHERE Clause
WHERE clause in the SQL is used to filter records returned by a query.
You can display only specific records that fulfill a specified condition.
WHERE Syntax
SELECT column1, column2, … FROM table_name
WHERE condition;
Firstly query all table, then apply any condition using where clause as follows.
SQL>
SQL> SELECT * FROM HR.DEPARTMENTS;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
27 rows selected.
List the departments only location id=1700 as follows.
SQL> SELECT * FROM HR.DEPARTMENTS WHERE LOCATION_ID=1700;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
30 Purchasing 114 1700
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
21 rows selected.
SQL>
You can use The following operators in the WHERE clause.
| Operator | Description |
| = | Equal |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal |
| <= | Less than or equal |
| <> or != | Not equal. |
| BETWEEN | Between a certain range |
| LIKE | Search for a pattern |
| IN | To specify and search multiple possible values for a column |
You can use these compariton operators in the Where Clause as follows.
List the departments which is location id greater than 1700.
SQL> SELECT * FROM HR.DEPARTMENTS WHERE LOCATION_ID>1700;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
20 Marketing 201 1800
40 Human Resources 203 2400
80 Sales 145 2500
70 Public Relations 204 2700
SQL>
SQL>
List the departments which department name is Construction.
SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_NAME='Construction';
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
180 Construction 1700
SQL>
SQL>
List the departments which is department id less than 120.
SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID<120;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
11 rows selected.
SQL>
SQL>
List the departments which department name starting with A
SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_NAME LIKE 'A%';
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
110 Accounting 205 1700
SQL>
SQL>
List the departments which department id between 100 and 120, or greater than or equal to 100, or less than or equal to 120.
SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID BETWEEN 100 AND 120;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_NAME LIKE '%man';
no rows selected
SQL>
SQL>
SQL>
List the departments which department name contains man string.
SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_NAME LIKE '%man%';
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
40 Human Resources 203 2400
SQL>
SQL>
List the departments which department id equal to (30,40,50) list, or department id equal to 30 or 40 or 50.
SQL> SELECT * FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID IN(30,40,50);
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
SQL>
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )