Query Languages and the Relational Algebra - Access Database Design and Programming - page 72

5.3.11 Semi-Joins

A semi-join is formed from an inner join (or -join) by projecting onto one of the tables
that participated in the join. In other words, we first form the join:

and then just keep the columns that came from S or from T. Thus, the formula for the left
semi-join is:

Similarly, the formula for the right semi-join is:

The concept of a semi-join occurs in relation to the DISTINCTROW keyword of the
SELECT clause in Access SQL, which we will discuss in Chapter 6. For now, let us
consider an example of the semi-join, which should indicate why semi-joins are useful.

Imagine that we add a new publisher to the PUBLISHERS table (Another Press in Table
5-17), but do not add any books for this publisher to the BOOKS table. Consider the
inner join of the tables PUBLISHERS and BOOKS:

PUBLISHERS join PUBLISHERS.PubID = BOOKS.PubID BOOKS
Table 5-17. The PUBLISHERS ( new) table

PubID PubName PubPhone

1 Big House 123-456-7890

2 Alpha Press 999-999-9999

3 Small House 714-000-0000

4 Another Press 111-222-3333

For the LIBRARY database, the result table resulting from this join is shown in Table 5-
18.

Table 5 -18. Result t a ble from an in ner join
PUBLISHERS.PubID Pub-Name PubPhone ISBN Title BOOKS.PubID Price

Small
House

714-000-
0000

0-12-345678-
9

3 Jane Eyre 3 $49.00

Small
House

714-000-
0000

0-11-345678-
9

3 Moby-Dick 3 $49.00

Small
House

714-000-
0000

0-321-32132-
1

3 Balloon 3 $34.00

Small
H ouse

714-000-
0 000

0-55-123456-
9

3 Main Street 3 $22.95