The article demonstrates the use of DISTINCT clause in SQL which is used to restrict the duplicate rows in your SELECT Queries.
1. Introduction
A SELECT query displays all the data contained by the table, irrespective of quality and authenticity of the data. Data gets filtered only if restricting conditions are specified explicitly along with the SELECT query, using WHERE clause. The result set data may have duplicate records too. To eliminate the duplicate records, oracle provides DISTINCT clause to filter the duplicate records and show only one copy for the duplicate records.
Syntax
Code :
SELECT [DISTINCT] [COLUMN LIST] FROM [TABLE NAME] [WHERE] [CONDITIONS]
Example 1: Select distinct departments from Employees table
Code sql:
SELECT DISTINCT DEPARTMENT_ID FROM EMPLOYEE DISTINCT DEP ----------------- 10 20 30
Example 2: Select distinct Job id from Employees table
Code sql:
SELECT DISTINCT JOB_ID FROM EMPLOYEE DISTINCT JO ----------------- DEV DDIR HR JMGR TEM TMGR SMGR
2. DISTINCT clause for more than one field
DISTINCT clause can be used with more than one field to get the unique combination of columns. Count of unique combinations of multiple columns would surely be more than the unique count of individual columns.
Example 2.1. , Select distinct department and job from Employees table
Code sql:
SELECT DISTINCT DEPARTMENT_ID, JOB_ID FROM EMP
3. UNIQUE clause
UNIQUE clause also functions synonymously to DISTINCT. It eliminates duplicate records from the result set and shows only one copy of the duplicated record.
Performance wise, both are same. Above examples can be re-executed using UNIQUE clause. Execution plan in the below example supports the point firmly.
Code sql:
SQL> SEELCT * FROM MYTAB; A ---------- 12 3 5 1 2 34 23 7 ROWS selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=7 Bytes=91) 1 0 TABLE ACCESS (FULL) OF 'MYTAB' (TABLE) (Cost=3 Card=7 BY tes=91) SQL> SELECT * FROM MYTAB; A ---------- 1 34 2 5 23 3 12 7 ROWS selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=7 Bytes=91) 1 0 HASH (UNIQUE) (Cost=4 Card=7 Bytes=91) 2 1 TABLE ACCESS (FULL) OF 'MYTAB' (TABLE) (Cost=3 Card=7 Bytes=91)
Example 3.1. Below SQL uses UNIQUE to get non duplicate combinations of Department id and Job id
Code sql:
SELECT UNIQUE DEPARTMENT_ID, JOB_ID FROM EMPLOYEE
4. Restrictions of DISTINCTClause
- DISTINCT degrades performance when working in large applications.
- DISTINCT clause does not works with LOB columns
Example 4.1. Demonstration of above restriction
Code sql:
SQL> SELECT DISTINCT APP_XML_INFO FROM MNS_R_PROJECT; SELECT DISTINCT APP_XML_INFO FROM MNS_R_PROJECT * ERROR AT LINE 1: ORA-00932: INCONSISTENT DATATYPES: EXPECTED - GOT BLOB
References
2 Comments
