SELECT DISTINCT Statement in Oracle SQL | Oracle SQL Tutorials -3

Hi,

I will explain SELECT DISTINCT Statement in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

https://ittutorial.org/sql-select-statement-and-desc-command-in-oracle-sql-oracle-sql-tutorials-1/

SELECT DISTINCT Statement

SELECT DISTINCT statement is used to query only different or unique values.

If there are lots of duplicate values in a table, when you want to display only different (distinct) values, then you should use DISTINCT statement.

SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, column3... FROM table_name;

When you query the location id of hr.departmens table, you can list the multiple duplicate values in this table without using DISTINCT.

SQL> SELECT LOCATION_ID FROM HR.DEPARTMENTS;

LOCATION_ID
-----------
1700
1800
1700
2400
1500
1400
2700
2500
1700
1700
1700

LOCATION_ID
-----------
1700
1700
1700
1700
1700
1700
1700
1700
1700
1700
1700

LOCATION_ID
-----------
1700
1700
1700
1700
1700

27 rows selected.

SQL>

But If you use DISTINCT, then you can display only unique or distinct values as follows.

SQL> SELECT DISTINCT LOCATION_ID FROM HR.DEPARTMENTS;

LOCATION_ID
-----------
1800
2400
1400
2500
1700
2700
1500

7 rows selected.

SQL>

You can use DISTINCT for multiple columns as follows.

SQL> SELECT DISTINCT DEPARTMENT_NAME,LOCATION_ID FROM HR.DEPARTMENTS;

DEPARTMENT_NAME                LOCATION_ID
------------------------------ -----------
Administration                        1700
Marketing                             1800
Shareholder Services                  1700
Operations                            1700
Sales                                 2500
Control And Credit                    1700
IT Helpdesk                           1700
Purchasing                            1700
Finance                               1700
Corporate Tax                         1700
Manufacturing                         1700

DEPARTMENT_NAME                LOCATION_ID
------------------------------ -----------
Contracting                           1700
NOC                                   1700
IT                                    1400
Treasury                              1700
Government Sales                      1700
Executive                             1700
Construction                          1700
Payroll                               1700
Shipping                              1500
Accounting                            1700
Benefits                              1700

DEPARTMENT_NAME                LOCATION_ID
------------------------------ -----------
Retail Sales                          1700
Human Resources                       2400
Public Relations                      2700
IT Support                            1700
Recruiting                            1700

27 rows selected.

SQL>

DISTINCT operator causes sorting which slows down the SQL execution time.

If it is possible, it is better that remove Distinct clause in Select for the SQL Query performance.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )