Access Structured Query Language (SQL) - Access Database Design and Programming - page 93

projection does nothing; since each row of the TablesClause result table must come from
a distinct combination of rows of the result tables, we deduce that DISTINCTROW has
exactly the same effect as ALL. To put it another way, DISTINCTROW is ignored.

It is useful to compare DISTINCTROW and DISTINCT. We can see that the only
difference is that a DISTINCT statement will return distinct values, rather than values
from distinct rows. However, these will be the same if the requested columns from each
return table uniquely identify their rows.

Let us illustrate with the PUBLISHERS example. Suppose we return a key (PubID) for
PUBLISHERS, as in the statement:

SELECT DISTINCTROW PubID, PubName
FROM PUBLISHERS INNER JOIN BOOKS
ON PUBLISHERS.PubID = BOOKS.PubID;

Then the result table will return all PUBLISHERS rows that have at least one book in the
BOOKS table, as Table 6-7 shows.

Table 6-7. Publ i shers with at least one book in BOOKS
PubID

PubName

3 Small House

1 Big House

2 Alpha Press

4 Small House

This is, in fact, the semi-join:

PUBLISHERS semi-joinPUBLISHERS.PubID=BOOKS.PubID BOOKS

Recall that the semi-join is the projection of the join onto one of the tables (in this case,
the PUBLISHERS table). Thus, as Microsoft itself says, the purpose of the
DISTINCTROW option is to return an updatable semi-join .

Of course, the same statement with DISTINCT in place of DISTINCTROW will return
the same result table. However, there is one big difference. Since DISTINCT statements
can completely hide the origin of the returned values, it would be a disaster if Access
allowed such a result table to be updatable—and indeed it does not. For instance, recall
the table tblDISTINCT discussed earlier and shown in Table 6-8.

Table 6-8. The tblDISTINCT table
PubName

Alpha Press
B ig House
S mall House