SQL .

SQL Cartesian Joins

Cartesian Join example one

Q: What is a Cartesian product and how do I get one?
A: A Cartesian join will get you a Cartesian product.
A Cartesian join is when you join every row of one table to every row of another table.
You can also get one by joining every row of a table to every row of itself.
   
 Select *
   From Film_Table, 
        Director_Film_Table;
   
 A Nous la Liberte, 1932, 87, 
   Aaron, Paul, Deadly Force
 A Nous la Liberte, 1932, 87, 
   Aaron, Paul, Different Story
 A Nous la Liberte, 1932, 87, 
   Aaron, Paul, Force of One
 A Nous la Liberte, 1932, 87, 
   Aaron, Paul, Imperial Navy
 A Nous la Liberte, 1932, 87, 
   Aaron, Paul, In Love and War
 A Nous la Liberte, 1932, 87, 
   Aaron, Paul, Maxia
 A Nous la Liberte, 1932, 87, 
   Aaron, Paul, The Miracle Worker
 A Nous la Liberte, 1932, 87,   
   Abashidze, Dodo, Ashik Karib
 A Nous la Liberte, 1932, 87, 
   Abbott, Charles, The Adventures of the Masked Phantom
 A Nous la Liberte, 1932, 87, 
   Abbott, George, Damn Yankees

....etc until every movie is joined to every director and each of their movies. Not very useful!
Note: A Cartesian join is very cpu intensive, and is usually done by mistake.

Cartesian Join example two

Q: You said you can have a Cartesian join by joining a table to itself. Show that!
   
 Select *
   From Film_Table T1,
        Film_Table T2;
   
 A Nous la Liberte, 1932, 87, 
  A Nous la Liberte, 1932, 87
 A Nous la Liberte, 1932, 87, 
  Aaron Loves Angela, 1975, 99
 A Nous la Liberte, 1932, 87, 
  Abbott and Costello go to Mars, 1953, 77
 A Nous la Liberte, 1932, 87,
  Abbott and Costello in Hollywood, 1945, 111
 A Nous la Liberte, 1932, 87,
  Abbott and Costello in the Foreign Legion, 1950, 80
 A Nous la Liberte, 1932, 87, 
  Abbott and Costello Meet Captain Kidd, 1952, 70
 A Nous la Liberte, 1932, 87, 
  Abbott and Costello Meet Dr. Jeckyl and Mr. Hyde, 1952, 77
 A Nous la Liberte, 1932, 87, 
  Abbott and Costello Meet Frankenstein, 1948, 83
 A Nous la Liberte, 1932, 87,
   Abbott and Costello Meet the Invisible Man, 1951, 82
 A Nous la Liberte, 1932, 87,
   Abbott and Costello Meet the Keystone Cops, 1954, 79

....etc until every movie is joined to every movie. Not very useful!
Note: To make your own Cartesian join at home all you really need is to take your basic select with two or more tables and remove the where clause.
Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.
Comment by Larry on 2012-09-26 Rate this Comment

It isn't so much that you use the same table twice, it's that you join the whole table to itself.

In general a cartesian join is very resource intensive, and it's usually done by accident.

The way you show might be the most efficent way to do it, if you were actually intending to do it. It might depend on the DBMS to optimize the query, high end stuff like DB2 or Oracle probably would.

Comment by archive on 2012-10-05 Rate this Comment

I've read your page on the cartesian product but have one simple question on it. You say that to make one you use the Table twice or more in a select. well what if that table is a subquery? like:
select * from (select * from orders) a1, (select * from orders) a2

Isnt this double work for the server? Is there a way to run the subquery once and use it in a cartesian product?

hope to hear from you soon

Comment by archive on 2012-09-26 Rate this Comment

I have just one problem with the cartesian product. You saw my example right? Well imagine the database has 100 million entries in (as has mine). That is 100 million to the power of 2. I'm using the cartesian product to get every 20th row out a certain table. The cartesian product way allow for this to be done. But only if you have a small table.

Or that's my experience anyway

Comment by RAJadhav on 2006-12-09 Rate this Comment

Good explaination

 
Sign in to comment on SQL Cartesian Joins.