In this section in the theory book I first describe the relational difference operator, named MINUS in
Example 4.10: Difference in SQL
SELECT StudentId FROM IS_CALLED
WHERE Name = 'Devinder' EXCEPT DISTINCT CORRESPONDING SELECT StudentId
FROM IS_ENROLLED_ON WHERE CourseId = 'C1'
The syntax for EXCEPT exactly parallels that for UNION. The key words DISTINCT, ALL, and
CORRESPONDING have exactly the same significance as in UNION, and DISTINCT remains the default option. When CORRESPONDING is not given, columns are paired by ordinal position, as in UNION.
American online
LIGS University
▶
enroll by September 30th, 2014 and
▶
save up to 16% on the tuition!
▶
pay in 10 installments / 2 years
▶
Interactive Online education
▶
visitwww.ligsuniversity.comto
find out more!
is currently enrolling in the
Interactive Online BBA, MBA, MSc,
DBA and PhD programs:
Note: LIGS University is not accredited by any
nationally recognized accrediting agency listed
by the US Secretary of Education.
102
t1EXCEPT DISTINCTt2 returns the table consisting of a single appearance of each row that appears in t1 but not in t2.t1EXCEPT ALLt2 returns the table consisting of n−m appearances of each row that appears n times in t1 and m times in t2, with n>m0.
Thanks to the implicit exclusion of noncommon attributes, EXCEPT CORRESPONDING can also sometimes be used to obtain a semidifference (r1NOT MATCHINGr2 in Tutorial D), but only when every column of the first operand is a common column. That is not the case in the theory book’s Example 4.11, IS_CALLED NOT MATCHING IS_ENROLLED_ON. In general, therefore, semidifference needs to be expressed in a more elaborate longhand in SQL. Example 4.11a does it by joining the result of Example 4.10 with IS_CALLED. Example 4.11b does it by using SQL’s comparison operator NOT IN, meaning “is not a member of”, in a WHERE condition. Example 4.11c
Example 4.11a: Semidifference via EXCEPT and JOIN
SELECT *
FROM (SELECT StudentId FROM IS_CALLED
WHERE Name = 'Devinder' EXCEPT DISTINCT CORRESPONDING SELECT StudentId
FROM IS_ENROLLED_ON
WHERE CourseId = 'C1') AS T1 NATURAL JOIN IS_CALLED
Example 4.11b: Semidifference via NOT IN and a subquery
SELECT StudentId FROM IS_CALLED
WHERE Name = 'Devinder'
AND StudentId NOT IN (SELECT StudentId
FROM IS_ENROLLED_ON WHERE CourseId = 'C1')
Example 4.11c: Semidifference via “quantified comparison” and a subquery
SELECT StudentId FROM IS_CALLED
WHERE Name = 'Devinder'
AND StudentId <> ALL (SELECT StudentId
FROM IS_ENROLLED_ON WHERE CourseId = 'C1')
The NOT IN expression in Example 4.11b appears to be testing for the appearance of a character string in a table, but in fact the first operand in this context is short for ROW(StudentId)—and recall from Chapter 2 that the key word ROW is optional, even when the row to be specified has more than one column value. Thus the expression tests for the nonappearance of a given row in a given table. (You won’t be surprised to hear that if the word NOT is omitted, then the expression becomes a test for appearance rather than nonappearance.)
In Example 4.11c <> ALL replaces NOT IN and in the absence of NULL has the same effect. It reads, somewhat ambiguously, as “not equal to all”. In fact the expression yields TRUE if and only if the condition comparing StudentId values is TRUE for every row in the result of the subquery.
Effects of NULL
The treatment of NULL in invocations of EXCEPT is as for UNION. This is different from its treatment in those of NOT IN and quantified comparisons. In the case of t1EXCEPTt2, row r of t1 appears in the result if and only if there does not exist a row s in t2 such that rIS NOT DISTINCT FROM s
evaluates to TRUE. Note that xNOT IN (t) evaluates to UNKNOWN whenever x = x does, including in particular the case where xIS NULL evaluates to TRUE because every field of the row x is the null value. Recall that when the condition given in a WHERE clause evaluates to UNKNOWN for row r, then r
does not appear in the result.
Now, suppose that IS_ENROLLED_ON contains the rows ('S4', 'C1'), (NULL, 'C1'), and no other rows for course C1. Then 'S4' NOT IN (SELECT StudentId FROM IS_ENROLLED_ON WHERE CourseId = 'C1') evaluates to FALSE, but 'S4' <> ALL (SELECT StudentId FROM IS_ENROLLED_ON WHERE CourseId = 'C1') evaluates to UNKNOWN. So examples 4.11b and 4.11c are not equivalent in the presence of NULL. Nevertheless, Examples 4.11b and 4.11c are equivalent because WHERE treats FALSE and UNKNOWN alike—a WHERE condition applied to a row has to yield TRUE for the row to appear in the result.
Historical Notes
The grammar given in the appendix to the SEQUEL paper uses the mathematical symbol “-” as an alternative to ∪ for union, strongly suggesting that it stands for set difference. There is no mention of this
operator in the body of the paper. However, EXCEPT was missing from original SQL and didn’t appear in the standard until 1992. Curiously, EXCEPT without ALL is now a mandatory conformance feature while EXCEPT ALL and both varieties of INTERSECT (ALL and DISTINCT) remain optional ones. (INTERSECT is mentioned in the discussion on semijoin in Chapter 5, Section 5.2.) The membership tests using IN and NOT IN were in original SQL but not in SEQUEL. SEQUEL did, however, support quantified comparisons, those these were limited to rows and tables of degree one.
104