Full Outer Join Vs Cross Join (Cartesian Product)
daniel Tue, 07/12/2011 - 11:09am
I had a fuzzy notion of what a Cartesian Product was as related to a Full Outer Join. Therefore I did not realize there is a specific syntax for a Cartesian Product. Here's an example, and that syntax:
Example Tables:
Table animals
Name | Continent_Name |
---|---|
Kangaroo | Australia |
Opussum | North America |
Panda | Asia |
Penguin | Antarctica |
Table continents
Continent_Name | Percent of Land Mass |
---|---|
Aftrica | 20.4% |
Asia | 29.5% |
Australia | 5.9% |
Europe | 6.8% |
North America | 16.5% |
South America | 12% |
Full Outer Join Syntax and Resultselect * from animals full outer join continents using(Continent_Name)
Result:
Name | Continent_Name | Percent of Land Mass |
---|---|---|
Aftrica | 20.4% | |
Panda | Asia | 5.9% |
Kangaroo | Australia | 5.9% |
Europe | 6.8% | |
Opussum | North America | 5.9% |
South America | 12% | |
Penguin | Antarctica |
Notice that the results have all the rows from the animals and continents tables, with blanks/nulls where there is no data. Nothing new for me there.
Cartesian Product (Cross Join)select * from animals cross join continents
Since I'm lazy, and don't want to type out a lot of angle brackets, I'll just describe the result. For each row in the animals table, you will get an output row for all of the continents rows.
Here is the first set of rows:
Name | Continent_Name | Continent_Name | Percent of Land Mass |
---|---|---|---|
Kangaroo | Australia | Aftrica | 20.4% |
Kangaroo | Australia | Asia | 29.5% |
Kangaroo | Australia | Australia | 5.9% |
Kangaroo | Australia | Europe | 6.8% |
Kangaroo | Australia | North America | 16.5% |
Kangaroo | Australia | South America | 12% |
Just repeat that pattern for each row in the animal table.
If you would have asked me to describe both the Cartesian Product and the Full Outer Join before, I could have done it. However I did not make the explicit connection that they are indeed separate things. This is just an example of book learning versus street smarts. Cartesian Products are used so rarely. I'm using them for the first time ever for some Data warehouse Junk Dimensions.
References:
- Log in to post comments