Ch 7 pp 328 -329
Cross Joins
The reading makes reference to the importance of including the WHERE clause when queries involve multiple tables.
To see why, look at the following query which finds the customer name for each invoice number.
SELECT CustName, InvoiceNo
FROM Customer, Invoice
WHERE Customer.CustABN = Invoice.CustABN;
Logon to SQL*Plus and run the query without the WHERE clause
SELECT CustName, InvoiceNo
FROM Customer, Invoice;
1. What is the output? What has happened?
The output is 400 records. Below are the first lines:
Most of these rows are clearly wrong as Invoice Number 3 is for Mellow Yellow Lighting. This output is an example of a cross join, every invoice number is joined with every customer. As there are 20 customers and 20 invoices the total number of rows generated is 20 * 20 = 400 rows.
When the WHERE clause is added, the incorrect rows are weeded out. This is revisited in the activity Have a go: Processing order.