Relational Theory for Computer Professionals

Chapter 12. SQL Operators II

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.

MATCHING and NOT MATCHING

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:

SNO

S1

S2

S3

S4

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, ...