|
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:
| DocNo | CustomerID | CustomerName | InvoiceDate |
| 1 | 1 | Angelina Alba | 1999-01-14 |
| 1 | 1 | Jessica Simpson | 1999-01-14 |
| 1 | 1 | Barbara Spears | 1999-01-14 |
| 1 | 1 | Pestiana Oliviera | 1999-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:
| DocNo | CustomerID | CustomerName | InvoiceDate |
| 1 | 1 | Angelina Alba | 14/01/99 |
| 2 | 4 | Pestiana Oliviera | 22/01/99 |
| 3 | 8 | Enrice Durance | 14/02/99 |
| 4 | 2 | Jessica Simpson | 10/03/99 |
| 5 | 10 | Erika Nass | 12/03/99 |
| .. | .. | .. | .. |
In some cases execution of Cartesian join between two tables can be
disastrous for DBMS system especially when large tables are involved.
|