Differences between UNIQUE and DISTINCT in select statements




Answers were Sorted based on User's Feedback



Differences between UNIQUE and DISTINCT in select statements..

Answer / rob reid

Howard Cheon is correct. They are THE SAME. There is no
difference.
The following is from the Oracle 9i SQL Reference (Note the
statement "these two keywords are synonymous" - that means
they mean the same thing):

DISTINCT | UNIQUE
Specify DISTINCT or UNIQUE if you want Oracle to return
only one copy of each set of duplicate rows selected (these
two keywords are synonymous). Duplicate rows are those with
matching values for each expression in the select list.

Restrictions on DISTINCT and UNIQUE Queries
When you specify DISTINCT or UNIQUE, the total number of
bytes in all select list expressions is limited to the size
of a data block minus some overhead. This size is specified
by the initialization parameter DB_BLOCK_SIZE.
You cannot specify DISTINCT if the select_list contains LOB
columns.

Is This Answer Correct ?    59 Yes 6 No

Differences between UNIQUE and DISTINCT in select statements..

Answer / manoj

UNIQUE(Constraint):- Can be only once for a column in a
table


DISTINCT(Clause):- Useed in select query to retrive distinct
(non repeated) data from table(s)

Is This Answer Correct ?    114 Yes 68 No

Differences between UNIQUE and DISTINCT in select statements..

Answer / chandra sekhar

the diff between unique and distinct is
unique is applied before insertion and retrival.it consists
of non duplicate values.if unique constraint is given it
does not take duplicate values.distinct is used in retrival
it gives the suppressed row(ex if two rows are same it will
show single row and non duplicate row) therefore distinct is
the combination of suppressed duplicate and non duplicate rows.

Is This Answer Correct ?    35 Yes 11 No




Differences between UNIQUE and DISTINCT in select statements..

Answer / gourvendra singh

Buddy i dont found any difference in using distinct and
unique in the select statement, as both are performing same
operation in the select statement, almost every diff you
have discussed i want to add one that is u cant use
distinct in the create whereas unique u can.

Is This Answer Correct ?    19 Yes 9 No

Differences between UNIQUE and DISTINCT in select statements..

Answer / manoj

see cautiously the major difference b/w unique and
distinct

UNIQUE :Allways take part on DATA INSERTION (In brief)
DINTINCT:Allways concern on data retrival (In brief)

Now we will take following case
when

case 1: UNIQUE KEY is defined (may b 4 one or more column)

It means this constraint will not allow us to
INSERT duplicate record/column_value(s)(i.e. exactly
same record/column_value(s) again for specified key column
(s)only)


NOW if u use DISTINCT clause in query
(select DISTINCT.from.)the will b same as simple query
(select....from...)

Bcoz data are already not repeated (bcoz of constraint)


case 2:if UNIQUE not defined

data may b repeated and DISTINCT will now useful


Have eye on Major difference

(and we never use UNIQUE CONS.. in select.....)

Is This Answer Correct ?    40 Yes 31 No

Differences between UNIQUE and DISTINCT in select statements..

Answer / cutepa1

yeah Manoj, this was analysed with your previous answer
itself but please try these statements and help me with
it's significance on the statements.
select unique(<clomun_name>) from <table_name>;
select distinct(<clomun_name>) from <table_name>;

Is This Answer Correct ?    16 Yes 8 No

Differences between UNIQUE and DISTINCT in select statements..

Answer / gvmahesh

unique is a constraint it's declare for preventing the data
duplication.we create it along with table creation.
DISTINCT is a clause it is used for retrieving the data
without duplication.but it can't prevent the data
duplication.It just hiding the duplicated data.

Is This Answer Correct ?    11 Yes 3 No

Differences between UNIQUE and DISTINCT in select statements..

Answer / karthicksharma

As said earlier UNIQUE is a constraint and can be used
during a table creation to prevent duplication of data .

-----------------------------------------------------
CREATING A TABLE USING UNIQUE CONSTRAINT:
=================================================

eg:
SQL> create table empl1 (name varchar2(15),no number(5)
unique);

Table created.

-----------------------------------------------------

So from this it is clearly implied that UNIQUE constraint
is used to prevent INSERTION AS WELL AS RETRIVAL of non-
duplicate data.

whereas DISTINCT clause can only be used for RETRIVAL of
non-duplicate data.

Whereas DISTINCT is a clause and cannot be used during a
table creation to limit the duplication of data.

-----------------------------------------------------
CREATING A TABLE USING DISTINCT CLAUSE:
===========================================
eg:
create table empl2(name varchar2(15),no number(5) distinct)
*
ERROR at line 1:
ORA-00907: missing right parenthesis

-----------------------------------------------------

Is This Answer Correct ?    6 Yes 0 No

Differences between UNIQUE and DISTINCT in select statements..

Answer / david alexander

There is some confusion here. Yes, UNIQUE is a constraint,
but the question is not about constraints, its about a
select statement. And, to answer it quite simply, its a
trick question. There is no difference. Although I always
see it as DISTINCT. Here is the quote from Oracle® Database
SQL Reference 10g Release 2 (10.2):

"Specify DISTINCT or UNIQUE if you want the database to
return only one copy of each set of duplicate rows
selected. These two keywords are synonymous. Duplicate rows
are those with matching values for each expression in the
select list."

Is This Answer Correct ?    8 Yes 4 No

Differences between UNIQUE and DISTINCT in select statements..

Answer / qman*

The two keywords are only synonymous in regards to a SELECT
statement, and also ONLY in an Oracle environment. DISTINCT
is an ANSI standard where as UNIQUE can only be used this
way in Oracle. To further the differences between the two,
UNIQUE may also be used in a table creation statement to
specify that inputs into the specified column cannot share
the same value, and if attempted will result in a
constraint violation error, I hope this clears things up.

Is This Answer Correct ?    5 Yes 1 No

Post New Answer



More SQL PLSQL Interview Questions

How will you debug your procedure? If your procedure is around 2000 lines and the expected output is 10 and we get only output 5.So how will you debug it? Somebody pls give the correct answer?

2 Answers   Fidelity,


I have a Employee table with columns ename,eid,salary,deptno. How to retrieve sum of salary for each deptno?

7 Answers   L&T,


how will you find out the last three records in a table with n no of records and delete them

3 Answers  


i have a table like this. i want to output like this c1 c2 c1 c2 1 10 1 10 2 20 2 30 3 30 3 60 4 40 4 100 5 5 5 105 c1 and c2 are columns in a table .i want output like this c2 values are 10,10+20,10+20+30,10+20+30+40.10+20+30+40+5. write a sql query.pls help this i want urgent.

2 Answers   IBM, OFSS, Loginworks,


How will we see framework of a table?

2 Answers   Accenture,






What is the first action to perform a query? Suppose I have four tables and five joins and my query is getting slow day by day.

4 Answers   Hexaware,


what is the correct way of selection statement a. select/from/table_name/orderby/groupby/having b. select/from/table_name/groupby/having/orderby

5 Answers   HCL, JPMorgan Chase,


What is out parameter used for eventhough return statement can also be used in pl/sql?

0 Answers  


how to retrieve the top 2 salaried persons from a database?

7 Answers   Orion Laboratories,


what command is used to create a table by copying the structure of another table including constraints ?

6 Answers   eicc, CMC,


What is the main difference between a UNION statement and a UNION ALL statement? 1. A UNION statement eliminates duplicate rows; a UNION ALL statement includes duplicate rows. 2. A UNION statement can be used to combine any number of queries; a UNION ALL statement can be used to combine a maximum of two queries. 3. A UNION statement can only combine queries that have parallel fields in the SELECT list; a UNION ALL statement can combine queries with differing SELECT list structures. 4. A UNION statement cannot be used with aggregate functions; a UNION ALL statement can be used with aggregate functions. 5. There is no difference between the two statements; they are interchangeable.

2 Answers   Sonata, Saman Bank,


When do we create bitmap indexes

2 Answers   CTS,






Categories