UNIQUE and DISTINCT

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)

Yeah Manoj, Thatz ok, UNIQUE as a constraint. But I like to
know about the diff while its usage in select statement. For
me it seems very similar to DISTINCT, can anyone give me if
there is any difference, please.

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.....)

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>;

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.

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.

DISTINCT AND UNIQUE are same operation.
But, You cannot specify DISTINCT if the select_list 
contains LOB columns.

Hi Howard Cheon,
               you are currect... but we cant able to use 
both DISTINCT and UNIQUE, if the select_list contains LOB, 
LONG columns. It may not a difference.

Could anyone plz give some another major difference..

Thanks in Advance

Vijay Reddy.