What is a Cartesian Join?

Let us attempt to explain a cartesian join in theThe (*) means return all records. The records are
simplest of terms, so that even a beginner couldcoming from the customer table and the
understand.customer_address table. So, each row returned
First, it is helpful to understand how databaseswill have all the columns from the customer table
store data. They use tables, with rows andAND from the customer_address table:first
columns. Each row is a unique record, containingname, last name, cust_id, address, city, state, zip,
the same types of data, each stored in a column.address_id, cust_id
For example, a table of customer data mightNote the 'where' clause in the query above. That
contain columns for the first name, the last name,specifies to ONLY join the record in the
and a customer ID (unique to each customer, tocustomer_address table to the record in the
help separate two John Smiths, for example). Incustomer table where the cust_ids match. That is
that table, then, when a new customer is addeda correct way to join.
to the database, a new row is added with a newA Cartesian join, also called a product join, results
ID, and the first and last name in the appropriatewhen all of the records from one table are joined
column.to all of the records in another. In the example
Next, it is useful to know that data can beabove, we can create a Cartesian join by
retrieved from the database using somethingremoving the 'where' clause.
called Structured Query Language, or SQLLet's assume that there are 200 records in the
(see-kwul) for short. Different types of databasescustomer table, and that each customer has a
(Oracle, Teradata, SQL Server, etc.) each haverecord in the customer_address table (so,
their own individual SQL syntax, but they areanother 200 records in the customer_address
generally similar.table). With the query uses the correct syntax to
All forms of SQL allow you to pull data from thejoin the two tables, the result set should be 200
table(s) of a database into a set of results, androws, with one row per customer also containing
they all allow you to JOIN more than one table.the address information.
For example, in addition to the customer tableHowever, if we remove the where clause, then
mentioned above, there could also be a Customerthe SQL will return EACH customer attached to
Address table. That table could contain a columnEACH address. Customer 1 will have a record for
for address, one for city, one for state, one foraddress 1, and address 2, and address 3, etc., up
zip, and an Address ID (unique number for eachto address 200. So will customer 2, 3, etc. all the
address). And, for the sake of keeping this simple,way through customer 200.
even though you would rarely, if ever, design aThe result set would be 40,000 records long,
database this way, it would also contain theinstead of 200. It would display each of the 200
Customer ID of the customer whose address iscustomers for 200 times, and 200*200=40,000.
in that record.That is why a cartesian join is also called a
That way, you could write a query that selectsproduct join...because it is the multiplication of the
the name information from the Customer table,records instead of just the joining of records.
and JOIN the Customer table to the CustomerTo avoid cartesian joins, always specify the
Address table, using some kind of syntax similarcriteria for joining tables so that any given record
to this:select * from customer,joins ONLY to the proper matching records in the
customer_addresswhere customer.cust_id =second table.
customer_address.cust_id