Basically for all new Oracle SQL learning people it is quite necessary to get knowledge on DISTINCT clause. As experienced persons are very much well-versed with DISTINCT clause they can skip this article and relax in their arm chair for time being (though there are many interesting articles you can go through in OracleMine).
Simple use of DISTINCT clause is to retrieve only single set of duplicate records if used in SQL statement. SELECT clause retrieves total number of rows when run. Using DISTINCT clause will restrict duplicate records and instead show only one copy of it.
SELECT [DISTINCT] [COLUMN LIST] FROM [TABLE NAME] [WHERE] [CONDITIONS];
SELECT DISTINCT dept_id FROM emp_master; dept_id ------------- 10 20 30
DISTINCT clause can also be used with multiple fields to return unique combination of those columns.
SELECT DISTINCT dept_id, salary FROM emp_master; dept_id salary ------------- ------- 10 1000 10 3500 20 2300 30 1200 30 4600 30 8000
How does DISTINCT clause differs from UNIQUE clause?
DISTINCT and UNIQUE clause are similar to each other. Only difference is UNIQUE clause is proprietary of Oracle while DISTINCT clause is an SQL standard. For other databases like MySQL, MSSQL etc. UNIQUE clause is not identifiable.
In general practice we use DISTINCT clause instead of UNIQUE clause.
Syntax of UNIQUE clause
SELECT [UNIQUE] [COLUMN LIST] FROM [TABLE NAME] [WHERE] [CONDITIONS];
SELECT UNIQUE dept_id FROM emp_master; dept_id ------------- 10 20 30
If you liked the above post, please leave your comments.
Hi I am Paras.
Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at firstname.lastname@example.org. Again I appreciate your visit. Hope to see you again and again!