One of my favorite interview questions is to as the candidate, “what is a Cartesian Join?” Sometimes the candidate won’t know what a Cartesian is, but they are familiar with the SQL Server syntax of a Cross Join. The answer is that a Cartesian join returns all the records in one table joined to all the records in a second table. This can be a REALLY BAD thing, especially if you perform a join like this on two very large tables.
If you have two tables with 10 records each, and perform a Cartesian join across those two tables, the number of records returned will be 10 X 10 = 100 records.
This behavior can be quite useful in some cases. There are times you want to know every possible combination of a Cartesian product, and then use that result as a basis to query other information. Let’s say you have some sales facts and some region facts. You could Cartesian the region to the periods of interest. Then, using that Cartesian product, you could outer join to the sales facts in order to find performance by Region over Time. This is a common use of a Cartesian join for reporting purposes.
So, as part of my interview questions I want to learn how someone thinks, and how they interact with new information. I find it useful when a candidate does not know the term Cartesian. They have the opportunity to show how they could use a Cartesian in a meaningful way. I used to just dump the question on them and ask them to come up with someone. That’s not really very fair in an interview situation. So, I found that giving them an overview, and then asking them how they could use it to produce a report, like the one I describe above, still gives them enough information to get through the nervous concerns, and yet demonstrate the absorbing of new information.
Cheers,
Ben