SQL Cartesian Products, Cross JOIN how are they useful?

Question

I am reading about the cross join/cartesian product on tables in Tutorialspoint and do not see the usefulness of cartesian products/cross joins.

Example:

Pet table:

+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-03-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
| Jenny    | Robert | dog     | f    | 2004-01-01 | 2014-05-04 |
+----------+--------+---------+------+------------+------------+

Event table:

+----------+------------+----------+------------------------------+
| name     | date       | type     | remark                       |
+----------+------------+----------+------------------------------+
| Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 females, 1 male |
| Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male  |
| Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female          |
| Chirpy   | 1999-03-21 | vet      | needed beak streightened     |
| Slim     | 1997-08-03 | vet      | broken rib                   |
| Bowser   | 1991-10-12 | kennel   | NULL                         |
| Fang     | 1991-10-12 | kennel   | NULL                         |
| Fang     | 1998-08-28 | birthday | Gave him new chew toy        |
| Claws    | 1998-03-17 | birthday | Gave him a flea collar       |
| Whistler | 1998-12-09 | birthday | First birthday               |
+----------+------------+----------+------------------------------+

Cross join: select * from pet, event; or select * from pet cross join event;

Outputs a table being the product of 10 rows X 10 (100 rows) rows in several different permutations. How is this useful and how can it be used?

possible duplicate of [Where are Cartesian Joins used in real life?](http://stackoverflow.com/questions/2380194/where-are-cartesian-joins-used-in-real-life) — potashin, Jul 16 '15 at 00:33
http://stackoverflow.com/questions/219716/what-are-the-uses-for-cross-join — Drew, Jul 16 '15 at 00:34

score 2 · Accepted Answer · answered Jul 16 '15 at 00:34

One example:

Say that all pets should have a record for all events, how do you find which rows are missing?

Using a cross join gets you the cartesian product of pets/events, which is what the event table should contain if all pets had a record for all events. This you then can join with (or use set difference) to find the missing rows.

score 0 · Answer 2 · answered Jul 16 '15 at 10:17

Last time I used it:

We have a notification service where we have a many to many connections between processes that can throw errors, and people who might want to receive them. A processes will notify multiple people when something fail, and people will get notifications for multiple processes.

We migrated from one db to another and lost all the ids (they were autogenerated so they changed when we inserted in a new DB). So I needed to connect all of the processes with all of the users. So I just did a insert based on a cross join.

SQL Cartesian Products, Cross JOIN how are they useful?

2 Answers2