怎样在SQL语句中使用EXISTS, UNIQUE, DISTINCT, 和OVERLAPS



Within the WHERE clause lies many possibilities for modifying your SQL statement. Among these possibilities are the EXISTS, UNIQUE, DISTINCT, and OVERLAPS predicates. Here are some examples of how to use these in your SQL statements.

EXISTS

You can use the EXISTSpredicate in conjunction with a subquery to determine whether the subquery returns any rows. If the subquery returns at least one row, that result satisfies theEXISTScondition, and the outer query executes. Consider the following example:

SELECT FirstName, LastName FROM CUSTOMER WHERE EXISTS  (SELECT DISTINCT CustomerID  FROM SALES  WHERE SALES.CustomerID = CUSTOMER.CustomerID);

Here the SALES table contains all of your company’s sales transactions. The table includes theCustomerIDof the customer who makes each purchase, as well as other pertinent information. The CUSTOMER table contains each customer’s first and last names, but no information about specific transactions.

The subquery in the preceding example returns a row for every customer who has made at least one purchase. The outer query returns the first and last names of the customers who made the purchases that the SALES table records.

EXISTSis equivalent to a comparison of COUNTwith zero, as the following query shows:

SELECT FirstName, LastName FROM CUSTOMER WHERE 0 <>  (SELECT COUNT(*)  FROM SALES  WHERE SALES.CustomerID = CUSTOMER.CustomerID);

For every row in the SALES table that contains a CustomerIDthat’s equal to a CustomerIDin the CUSTOMER table, this statement displays the FirstNameand LastNamecolumns in the CUSTOMER table. For every sale in the SALES table, therefore, the statement displays the name of the customer who made the purchase.

UNIQUE

As you do with the EXISTSpredicate, you use the UNIQUEpredicate with a subquery. Although theEXISTSpredicate evaluates to True only if the subquery returns at least one row, theUNIQUEpredicate evaluates to True only if no two rows returned by the subquery are identical. In other words, theUNIQUEpredicate evaluates to True only if all the rows that its subquery returns are unique.

Consider the following example:

SELECT FirstName, LastName FROM CUSTOMER WHERE UNIQUE  (SELECT CustomerID FROM SALES   WHERE SALES.CustomerID = CUSTOMER.CustomerID);

This statement retrieves the names of all new customers for whom the SALES table records only one sale. Because a null value is an unknown value, two null values aren’t considered equal to each other; when theUNIQUEkeyword is applied to a result table that contains only two null rows, theUNIQUEpredicate evaluates to True.

DISTINCT

The DISTINCTpredicate is similar to the UNIQUEpredicate, except in the way it treats nulls. If all the values in a result table areUNIQUE, then they’re alsoDISTINCTfrom each other.

However, unlike the result for the UNIQUEpredicate, if the DISTINCTkeyword is applied to a result table that contains only two null rows, theDISTINCTpredicate evaluates to False. Two null values arenot considered distinct from each other, while at the same time they are considered to be unique.

This strange situation seems contradictory, but there’s a reason for it. In some situations, you may want to treat two null values as different from each other — in which case, use theUNIQUEpredicate. When you want to treat the two nulls as if they’re the same, use theDISTINCTpredicate.

OVERLAPS

You use the OVERLAPSpredicate to determine whether two time intervals overlap each other. This predicate is useful for avoiding scheduling conflicts. If the two intervals overlap, the predicate returns a True value. If they don’t overlap, the predicate returns a False value.

You can specify an interval in two ways: either as a start time and an end time or as a start time and a duration. Here are some examples:

(TIME '2:55:00', INTERVAL '1' HOUR)OVERLAPS(TIME '3:30:00', INTERVAL '2' HOUR)

This first example returns a True because 3:30 is less than one hour after 2:55.

(TIME '9:00:00', TIME '9:30:00')OVERLAPS(TIME '9:29:00', TIME '9:31:00')

This example returns a True because you have a one-minute overlap between the two intervals.

(TIME '9:00:00', TIME '10:00:00')OVERLAPS(TIME '10:15:00', INTERVAL '3' HOUR)

This example returns a False because the two intervals don’t overlap.

(TIME '9:00:00', TIME '9:30:00')OVERLAPS(TIME '9:30:00', TIME '9:35:00')

This example returns a False because even though the two intervals are contiguous, they don’t overlap.

关于null和nique的关系可以参考：http://ocelot.ca/blog/blog/2013/09/11/null-and-unique/

DBMSMaximum number of NULLs when there is a UNIQUE constraintIBM (DB2)OneInformixOneIngresZeroInterBaseZeroMicrosoft (SQL Server)OneMySQLMany [although theBDB storage enginewas an exception]OracleManySybaseOne