O'Reilly logo

Relational Theory for Computer Professionals by Chris Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required