Where are Cartesian Joins used in real life?

Question

Where are Cartesian Joins used in real life?

Can some one please give examples of such a Join in any SQL database.

Answer 1

Every time you make a LEFT JOIN or RIGHT JOIN.

Answer 2

just random example. you have a table of cities: Id, Lat, Lon, Name. You want to show user table of distances from one city to another. You will write something like

SELECT c1.Name, c2.Name, SQRT( (c1.Lat - c2.Lat) * (c1.Lat - c2.Lat) + (c1.Lon - c2.Lon)*(c1.Lon - c2.Lon))
FROM   City c1, c2

Answer 3

Usually, to generate a superset for the reports.

In PosgreSQL:

SELECT  COALESCE(SUM(sales), 0)
FROM    generate_series(1, 12) month
CROSS JOIN
        department d
LEFT JOIN
        sales s
ON      s.department = d.id
        AND s.month = month
GROUP BY
        d.id, month

Answer 4

Here are two examples:

To create multiple copies of an invoice or other document you can populate a temporary table with names of the copies, then cartesian join that table to the actual invoice records. The result set will contain one record for each copy of the invoice, including the "name" of the copy to print in a bar at the top or bottom of the page or as a watermark. Using this technique the program can provide the user with checkboxes letting them choose what copies to print, or even allow them to print "special copies" in which the user inputs the copy name.

 CREATE TEMP TABLE tDocCopies (CopyName TEXT(20))
 INSERT INTO tDocCopies (CopyName) VALUES ('Customer Copy')
 INSERT INTO tDocCopies (CopyName) VALUES ('Office Copy')
 ...
 INSERT INTO tDocCopies (CopyName) VALUES ('File Copy')
 SELECT * FROM InvoiceInfo, tDocCopies WHERE InvoiceDate = TODAY()

To create a calendar matrix, with one record per person per day, cartesian join the people table to another table containing all days in a week, month, or year.

  SELECT People.PeopleID, People.Name, CalDates.CalDate
     FROM People, CalDates

Answer 5

You might want to create a report using all of the possible combinations from two lookup tables, in order to create a report with a value for every possible result.

Consider bug tracking: you've got one table for severity and another for priority and you want to show the counts for each combination. You might end up with something like this:

select severity_name, priority_name, count(*)
from (select severity_id, severity_name, 
             priority_id, priority_name 
        from severity, priority) sp 
      left outer join 
      errors e 
      on e.severity_id = sp.severity_id 
         and e.priority_id = sp.priority_id
group by severity_name, priority_name

In this case, the cartesian join between severity and priority provides a master list that you can create the later outer join against.

Answer 6

This is the only time in my life that I've found a legitimate use for a Cartesian product.

At the last company I worked at, there was a report that was requested on a quarterly basis to determine what FAQs were used at each geographic region for a national website we worked on.

Our database described geographic regions (markets) by a tuple (4, x), where 4 represented a level number in a hierarchy, and x represented a unique marketId.

Each FAQ is identified by an FaqId, and each association to an FAQ is defined by the composite key marketId tuple and FaqId. The associations are set through an admin application, but given that there are 1000 FAQs in the system and 120 markets, it was a hassle to set initial associations whenever a new FAQ was created. So, we created a default market selection, and overrode a marketId tuple of (-1,-1) to represent this.

Back to the report - the report needed to show every FAQ question/answer and the markets that displayed this FAQ in a 2D matrix (we used an Excel spreadsheet). I found that the easiest way to associate each FAQ to each market in the default market selection case was with this query, unioning the exploded result with all other direct FAQ-market associations.

The Faq2LevelDefault table holds all of the markets that are defined as being in the default selection (I believe it was just a list of marketIds).

SELECT FaqId, fld.LevelId, 1 [Exists]
FROM Faq2Levels fl
  CROSS JOIN Faq2LevelDefault fld
WHERE fl.LevelId=-1 and fl.LevelNumber=-1 and fld.LevelNumber=4
UNION
SELECT Faqid, LevelId, 1 [Exists] from Faq2Levels WHERE LevelNumber=4

Answer 7

When running a query for each date in a given range. For example, for a website, you might want to know for each day, how many users were active in the last N days. You could run a query for each day in a loop, but it's simplest to keep all the logic in the same query, and in some cases the DB can optimize the Cartesian join away.