SQL Simple -> Cartesian join between two tables

If our task is to retrive pieces of information stored in different tables using single query statement, we will use join between two or more tables. Let's see example where we want to retrive Customer name in addition to the information stored in Invoice table. This information includes purchase detailes and CustomerID but misses details about the Customer (like his name or phone number). This details are stored in Customers table. We will use ANSI SQL-1989 syntax. At first we pick up two tables, give them aliases and chose certain fields from each table:

SELECT a.DocNo, a.CustomerID, b.CustomerName, a.DocDate AS InvoiceDate
FROM Invoice AS a, Customers AS b

Such query is correct from the syntax point of veiew, but when executed will return Cartesian style result. Like this:
DocNoCustomerIDCustomerNameInvoiceDate
11Angelina Alba1999-01-14
11Jessica Simpson1999-01-14
11Barbara Spears1999-01-14
11Pestiana Oliviera1999-01-14
........

Cartesian style query retrives all records from Customers table for each record retrieved from Invoice table. Clearly this is not a desirable result for us. The reason for getting Cartesian results is the lack of apropriate WHERE condition. The presence of such condition creates logicaly correct query:
SELECT a.DocNo, a.CustomerID, b.CustomerName, a.DocDate AS InvoiceDate
FROM Invoice AS a, Customers AS b
WHERE a.CustomerID = b.CustomerID

Results:
DocNoCustomerIDCustomerNameInvoiceDate
11Angelina Alba14/01/99
24Pestiana Oliviera22/01/99
38Enrice Durance14/02/99
42Jessica Simpson10/03/99
510Erika Nass12/03/99
........


In some cases execution of Cartesian join between two tables can be disastrous for DBMS system especially when large tables are involved.


sqlexamples.info