Rewriting a where statement in SQL without using "unique"

Rewrite the where clause

wher开发者_如何学编程e unique (select title from course)

without using the unique construct

The definition of the UNIQUE predicate is as follows:

1) Let T be the result of the <table subquery>.

2) If there are no two rows in T such that the value of each column
in one row is non-null and is equal to the value of the cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.

(Revision) Thus, the equivalent results of the Unique predicate as you have written it would be:

Where Not Exists    (
                    Select 1
                    From Course
                    Where Title Is Not Null
                    Group By Title
                    Having Count(*) > 1
                    )

The DISTINCT clause returns unique values for the column(s) specified in the SELECT clause. Depending on the data, as the number of columns increases -- so does the possibility that there will be duplicate values in a single column because other columns contain unique values.

SELECT DISTINCT 
       c.title
  FROM COURSE c

...will return a unique list of titles. But if you add columns that can have different values for each title:

SELECT DISTINCT 
       c.title,
       c.instructor
  FROM COURSE c

...the title column will likely hold duplicates when 2+ instructors teach the same course title because DISTINCT returns unique rows across all columns involved.

The GROUP BY clause is another alternative:

  SELECT c.title
    FROM COURSE c
GROUP BY c.title

...will produce the same result set as the first example using DISTINCT.

WHERE 1 = ALL(SELECT COUNT(*) FROM course GROUP BY title)

UNIQUE keyword test for Absence of Duplicate Tuples

• The unique construct tests whether a subquery has any duplicate tuples in its result.
• The unique construct evaluates to “true” on an empty set.

consider below data set with name and age,

sameera___ 20

john_______ 22

mac_______26

john_______22

if we use unique keyword for age to this instance, result would be,

sameera___20

mac______26

but if we use distinct keyword for age, we get,

sameera___20

john______22

mac______26

by removing only duplicates but not the original one.