The UNIQUE predicate is used to determine if duplicate rows exist
in the result table of a column select expression. Note that the
UNIQUE predicate (in compliance with the SQL language standard)
ignores rows with a NULL column value and ensures uniqueness for
the other column values. Contrast this with the SINGLE predicate,
which considers a single column value of NULL as a match for any
other NULL value in the same column.
(B)0[m[1munique-predicate = [m
[1m [m
[1mqqq> [1;4mUNIQUE[m[1m qqq> ( col-select-expr ) qqq> [m
[1m [m
If any two rows in the expression are equal to one another, the
UNIQUE predicate evaluates to false.
The following example determines those cities in which one and
only one employee from the EMPLOYEES database lives.
SQL> SELECT E.LAST_NAME, E.CITY FROM EMPLOYEES E
cont> WHERE UNIQUE
cont> (SELECT * FROM EMPLOYEES EMP
cont> WHERE EMP.CITY=E.CITY);
LAST_NAME CITY
Harrison Boston
Smith Bristol
McElroy Cambridge
Kilpatrick Marlow
Sciacca Munsonville
Vormelker Rochester
Dement Sanbornton
Babbin Sanbornville
Keisling Twin Mountain
Ziemke Winnisquam
Johnston Wolfeboro
11 rows selected