BowerStudios.com

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

Table continents

Full Outer Join Syntax and Result
select * from animals full outer join continents using(Continent_Name)
Result:

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:

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:

• Sql Joins on Wikipedia
• Full Outer vs Cross Join Discussion on Stackoverflow