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 )