Function is smothered in surmise
—William Shakespeare: Macbeth (1606)
In Chapter 5 I described certain additional relational operators, over and above the ones originally defined by Codd in the late 1960s and early 1970s. Specifically, I discussed (a) MATCHING and NOT MATCHING; (b) EXTEND; (c) image relations; and (d) aggregation, summarization, and related matters. Let’s see what functionality SQL provides in these areas.
Here’s the query I used in Chapter 5 to introduce the semijoin operator (MATCHING, in Tutorial D): “Get full supplier details for suppliers who supply at least one part.” Tutorial D formulation:
S MATCHING SP
SQL has no direct analog of MATCHING as such, but IN with a subquery works:
SELECT SNO , SNAME , STATUS , CITY FROM S WHERE SNO IN ( SELECT SNO FROM SP )
Recall from the answer to Q: in Chapter 11 that (simplifying slightly) a subquery in SQL is basically just a SELECT – FROM – WHERE expression enclosed in parentheses. As for the operator IN, the boolean expression
rx IN tx
is defined to return TRUE if the row r denoted by the row expression rx appears in the table t denoted by the table expression tx, and FALSE if it doesn’t. In the example, therefore, the subquery is evaluated first and returns the following table:
Then the outer query is evaluated, and it returns just that subset of table S where the supplier number is one of these four.
Actually the query under discussion—like just about every query, ...