Is this query from Modern Database Management wrong?

Question

This is from the Modern Database Management text, chapter 12. Given:

Supplier_T(SupplierNumber,City)                10,000 records, stored in Detroit
Part_T(PartNumber, Color)                      100,000 records, stored in Chicago
Shipment_T(SupplierNumber, PartNumber)         1,000,000 records, stored in Detroit

A query written in SQL, is made to list the supplier numbers for Cleveland suppliers of red parts is given on page 19 is:

SELECT Supplier_T.SupplierNumber
FROM Supplier_T, Shipment_T, Part_T
WHERE Supplier_T.City = 'Cleveland'
AND Shipment_T.PartNumber = Part_T.PartNumber
AND Part_T.Color = 'Red';

But isn't it missing in the WHERE clause,

AND Supplier_T.SupplierNumber = Shipment_T.SupplierNumber

Maybe I'm just being too pedantic.

are you really using `‘ ’` and not `' '` – John Woo May 04 '13 at 04:06 — John Woo, May 04 '13 at 04:06
@JW웃, copy pasted from the pdf. #gosh – Irwin May 04 '13 at 04:06 — Irwin, May 04 '13 at 04:06

score 1 · Accepted Answer · answered May 04 '13 at 04:26

It might well be that the example is given in order to show what happens when one forgets to join between tables! This is called a Cartesian Join, and the result is that every row joins to every other row, producing a much much larger result set than one had expected.

I imagine that if the point of the query is to show red parts which are stored in Cleveland, then the query would be

SELECT Supplier_T.SupplierNumber
FROM Supplier_T
inner join Shipment_T on Shipment_T.suppliernumber = supplier_t.suppliernumber
inner join Part_T on Part_T.PartNumber = Shipment_T.PartNumber
WHERE Supplier_T.City = 'Cleveland'
AND Part_T.Color = 'Red';

The fact that the query uses SQL-89 implicit join syntax (from table1, table2, table3) as opposed to SQL-92 explicit join syntax (from table1 inner join table2) should hint that something is wrong! What is written in the text after this query?

Is this query from Modern Database Management wrong?

1 Answers1