DISTINCT means unique. Using DISTINCT will filter out duplicates from your results table. In the EMPLOYEE table the JOB field is populated for each employee. If you wanted a list of all the individual job titles without any repeating rows you would use DISTINCT to filter out the duplicates.

SQL> SELECT DISTINCT JOB ç DISTINCT verb

The results table would look like this:

JOB

----

Anlt

Clrk

Drvr

Mngr

Slsm

Spvr

The use of the above SELECT statement without DISTINCT would return a value for JOB for each and every row in the table, duplicates included as shown.

This query returns the following results table.

JOB

----

Clrk

Slsm

Anlt

Mngr

Mngr * ç note duplicates

Clrk *

Mngr *

Mngr *

Clrk *

Drvr

Spvr

Slsm *

è If duplicate rows do not cause a problem avoid the use of DISTINCT. Since the use of DISTINCT causes a sort to be invoked to remove the duplicates, it adds overhead to the system.

è When coding your select statements, try to code the most restrictive predicate first. Why? So you can eliminate the greatest number of rows with the first predicate before searching on the second predicate.

For you to try:

1. Write a SQL query that will return all the unique department numbers that are represented in the EMPLOYEE table, ordered by department number.

Go here to check your answer.

Last revised on 10/21/97 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems

Return to Oracle/SQL Tutorial Contents Page | Return to Exercise 3 | Go to SQL Exercise 5 - Logical Operators - And and Or