Selecting Data that Satisfies Specified Conditions

To select only data that matches a specified condition, include the WHERE clause in the SELECT statement. The condition in the WHERE clause can be any SQL condition (for information about SQL conditions, see Oracle Database SQL Language Reference).

The query in the following example selects data only for employees in department 90.

Selecting Data from One Department

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;

Result is similar to:

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
-------------------- ------------------------- -------------
Steven               King                                 90
Neena                Kochhar                              90
Lex                  De Haan                              90

The query in the following example selects data only for employees in departments 100, 110, and 120.

Selecting Data from Specified Departments

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (100, 110, 120);

Result is similar to:

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
-------------------- ------------------------- -------------
John                 Chen                                100
Daniel               Faviet                              100
William              Gietz                               110
Nancy                Greenberg                           100
Shelley              Higgins                             110
Luis                 Popp                                100
Ismael               Sciarra                             100
Jose Manuel          Urman                               100
 
8 rows selected.

There are no employees in department 120.

The query in the following example selects data only for employees whose last names start with "Ma".

Selecting Data for Last Names that Start with the Same Substring

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE 'Ma%';

Result is similar to:

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jason                Mallin
Steven               Markle
James                Marlow
Mattea               Marvins
Randall              Matos
Susan                Mavris
 
6 rows selected.

The query in the following example selects data only for employees whose last names include "ma".

Selecting Data for Last Names that Include the Same Substring

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%ma%';

Result is similar to:

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Sundita              Kumar
Jose Manuel          Urman
Shanta               Vollman

The query in the following example tests for two conditions—whether the salary is at least 11000, and whether the commission percentage is not null.

Selecting Data that Satisfies Two Conditions

SELECT FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT "%"
FROM EMPLOYEES
WHERE (SALARY >= 11000) AND (COMMISSION_PCT IS NOT NULL);

Result is similar to:

FIRST_NAME           LAST_NAME                     SALARY          %
-------------------- ------------------------- ---------- ----------
John                 Russell                        14000         .4
Karen                Partners                       13500         .3
Alberto              Errazuriz                      12000         .3
Gerald               Cambrault                      11000         .3
Lisa                 Ozer                           11500        .25
Ellen                Abel                           11000         .3
 
6 rows selected.

Selecting Data that Satisfies Specified Conditions

Related Topics