BowerStudios.com

  • My Lab
  • Family
  • Friends
  • Professional
  • About
Home

Full Outer Join Vs Cross Join (Cartesian Product)

daniel —Tue, 07/12/2011 - 11:09am

  • SQL

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 Result
select * 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:

  • Sql Joins on Wikipedia
  • Full Outer vs Cross Join Discussion on Stackoverflow
  • Log in to post comments

Navigation

  • Search
  • Recent content
  • Contact Me
  • Mail
  • Pass Hasher
  • Bower Studios Admin

Quotes

daniel —Mon, 02/26/2007 - 12:29pm

The three great essentials to achieve anything worth while are: Hard work, Stick-to-itiveness, and Common sense.

—

Thomas A. Edison

  • Log in to post comments
  • daniel's quotes

Popular content

Last viewed:

  • Excerpts from a Dog's Diary
  • Dynamically scheduling jobs with Spring
  • Maven Properties (Available Constants)
  • Thomas Jefferson
  • Groovy retainAll example with Closure

Copyright 2018 Daniel Bower
  • My Lab
  • Family
  • Friends
  • Professional
  • About