Cross Join Operations

NuoDB does not support CROSS Joins, per se. However, NuoDB does support the joining of two sets of tables. The result set of this type of join can be similar to a CROSS join. This join would contain all rows in the first table that meet the WHERE clause criteria, if one is provided, joined to all rows in the second table that meet the WHERE clause criteria, if one is provided. There is no join-predicate defined, therefore rows do not have to contain matching values. Without the need to match row values, this result set can become very large, depending on the number of rows in each one of the tables. For example, if table1 has 10 rows and table2 has 5 rows and they are combined to produce a Cartesian product result set of rows, the total number of rows resulting would be 10 x 5 or 50 rows.

Using the tables created at Join Operations Supported by NuoDB, we can see what it would look like if every player in the PLAYERS table, regardless of their team, were joined with every team in the TEAMS table using this type of join.

SELECT t.*,p.* FROM teams t, players p;
 TEAM_ID       TEAM_NAME      WINS  LOSSES  DIVISION_ID  PLAYERID         PLAYER_NAME       YEAR  TEAM_ID  POSITION
 -------- ------------------- ----- ------- ------------ ---------- ----------------------- ----- -------- ---------
   BOS    Boston Bruins        49     29         NE      yorkal01   Allen York              2011    CBS        G
   BOS    Boston Bruins        49     29         NE      raycran01  Andrew Raycroft         2011    DAL        G
   BOS    Boston Bruins        49     29         NE      khudoan01  Anton Khudobin          2011    BOS        G
   BOS    Boston Bruins        49     29         NE      bouchbr01  Brian Boucher           2011    CAR        G
   BOS    Boston Bruins        49     29         NE      wardca01   Cam Ward                2011    CAR        G
   BOS    Boston Bruins        49     29         NE      crawfco01  Corey Crawford          2011    CHI        G
   BOS    Boston Bruins        49     29         NE      sanfocu01  Curtis Sanford          2011    CBS        G
   BOS    Boston Bruins        49     29         NE      ellisda01  Dan Ellis               2011    AND        G
   BOS    Boston Bruins        49     29         NE      macindr01  Drew MacIntyre          2011    BUF        G
   BOS    Boston Bruins        49     29         NE      karlshe01  Henrik Karlsson         2011    CAL        G
   BOS    Boston Bruins        49     29         NE      tarkkii01  Iiro Tarkki             2011    AND        G
   BOS    Boston Bruins        49     29         NE      gigueje01  Jean-Sebastien Giguere  2011    COL        G
   BOS    Boston Bruins        49     29         NE      drouije01  Jeff Drouin-Deslauriers 2011    AND        G
   BOS    Boston Bruins        49     29         NE      enrotjh01  Jhonas Enroth           2011    BUF        G
   BOS    Boston Bruins        49     29         NE      howarji01  Jimmy Howard            2011    DET        G
   BOS    Boston Bruins        49     29         NE      macdojo01  Joey MacDonald          2011    DET        G
   BOS    Boston Bruins        49     29         NE      hillejo01  Jonas Hiller            2011    AND        G
   BOS    Boston Bruins        49     29         NE      peterju01  Justin Peters           2011    CAR        G
   BOS    Boston Bruins        49     29         NE      lehtoka01  Kari Lehtonen           2011    DAL        G
   BOS    Boston Bruins        49     29         NE      irvinle01  Leland Irving           2011    CAL        G
   BOS    Boston Bruins        49     29         NE      turcoma01  Marty Turco             2011    BOS        G
   BOS    Boston Bruins        49     29         NE      kiprumi01  Miikka Kiprusoff        2011    CAL        G
   BOS    Boston Bruins        49     29         NE      murphmi02  Mike Murphy             2011    CAR        G
   BOS    Boston Bruins        49     29         NE      emeryra01  Ray Emery               2011    CHI        G
   BOS    Boston Bruins        49     29         NE      bachmri01  Richard Bachman         2011    DAL        G
   BOS    Boston Bruins        49     29         NE      millery01  Ryan Miller             2011    BUF        G
   BOS    Boston Bruins        49     29         NE      varlasi01  Semyon Varlamov         2011    COL        G
   BOS    Boston Bruins        49     29         NE      hunwish01  Shawn Hunwick           2011    CBS        G
   BOS    Boston Bruins        49     29         NE      masonst01  Steve Mason             2011    CBS        G
   BOS    Boston Bruins        49     29         NE      thomati01  Tim Thomas              2011    BOS        G
   BOS    Boston Bruins        49     29         NE      rasktu01   Tuukka Rask             2011    BOS        G
   BOS    Boston Bruins        49     29         NE      conklty01  Ty Conklin              2011    DET        G
   BUF    Buffalo Sabres       39     32         NE      yorkal01   Allen York              2011    CBS        G
   BUF    Buffalo Sabres       39     32         NE      raycran01  Andrew Raycroft         2011    DAL        G
   BUF    Buffalo Sabres       39     32         NE      khudoan01  Anton Khudobin          2011    BOS        G
   BUF    Buffalo Sabres       39     32         NE      bouchbr01  Brian Boucher           2011    CAR        G
   BUF    Buffalo Sabres       39     32         NE      wardca01   Cam Ward                2011    CAR        G
   BUF    Buffalo Sabres       39     32         NE      crawfco01  Corey Crawford          2011    CHI        G
   BUF    Buffalo Sabres       39     32         NE      sanfocu01  Curtis Sanford          2011    CBS        G
   BUF    Buffalo Sabres       39     32         NE      ellisda01  Dan Ellis               2011    AND        G
   BUF    Buffalo Sabres       39     32         NE      macindr01  Drew MacIntyre          2011    BUF        G
   BUF    Buffalo Sabres       39     32         NE      karlshe01  Henrik Karlsson         2011    CAL        G
   BUF    Buffalo Sabres       39     32         NE      tarkkii01  Iiro Tarkki             2011    AND        G
   BUF    Buffalo Sabres       39     32         NE      gigueje01  Jean-Sebastien Giguere  2011    COL        G
   BUF    Buffalo Sabres       39     32         NE      drouije01  Jeff Drouin-Deslauriers 2011    AND        G
   BUF    Buffalo Sabres       39     32         NE      enrotjh01  Jhonas Enroth           2011    BUF        G
   BUF    Buffalo Sabres       39     32         NE      howarji01  Jimmy Howard            2011    DET        G
   BUF    Buffalo Sabres       39     32         NE      macdojo01  Joey MacDonald          2011    DET        G
   BUF    Buffalo Sabres       39     32         NE      hillejo01  Jonas Hiller            2011    AND        G
   BUF    Buffalo Sabres       39     32         NE      peterju01  Justin Peters           2011    CAR        G
   BUF    Buffalo Sabres       39     32         NE      lehtoka01  Kari Lehtonen           2011    DAL        G
   BUF    Buffalo Sabres       39     32         NE      irvinle01  Leland Irving           2011    CAL        G
   BUF    Buffalo Sabres       39     32         NE      turcoma01  Marty Turco             2011    BOS        G
   BUF    Buffalo Sabres       39     32         NE      kiprumi01  Miikka Kiprusoff        2011    CAL        G
   BUF    Buffalo Sabres       39     32         NE      murphmi02  Mike Murphy             2011    CAR        G
   BUF    Buffalo Sabres       39     32         NE      emeryra01  Ray Emery               2011    CHI        G
   BUF    Buffalo Sabres       39     32         NE      bachmri01  Richard Bachman         2011    DAL        G
   BUF    Buffalo Sabres       39     32         NE      millery01  Ryan Miller             2011    BUF        G
   BUF    Buffalo Sabres       39     32         NE      varlasi01  Semyon Varlamov         2011    COL        G
   BUF    Buffalo Sabres       39     32         NE      hunwish01  Shawn Hunwick           2011    CBS        G
   BUF    Buffalo Sabres       39     32         NE      masonst01  Steve Mason             2011    CBS        G
   BUF    Buffalo Sabres       39     32         NE      thomati01  Tim Thomas              2011    BOS        G
   BUF    Buffalo Sabres       39     32         NE      rasktu01   Tuukka Rask             2011    BOS        G
   BUF    Buffalo Sabres       39     32         NE      conklty01  Ty Conklin              2011    DET        G
   CAR    Carolina Hurricanes  33     33         SE      yorkal01   Allen York              2011    CBS        G
   CAR    Carolina Hurricanes  33     33         SE      raycran01  Andrew Raycroft         2011    DAL        G
   CAR    Carolina Hurricanes  33     33         SE      khudoan01  Anton Khudobin          2011    BOS        G
   CAR    Carolina Hurricanes  33     33         SE      bouchbr01  Brian Boucher           2011    CAR        G
   CAR    Carolina Hurricanes  33     33         SE      wardca01   Cam Ward                2011    CAR        G
   CAR    Carolina Hurricanes  33     33         SE      crawfco01  Corey Crawford          2011    CHI        G
   CAR    Carolina Hurricanes  33     33         SE      sanfocu01  Curtis Sanford          2011    CBS        G
   CAR    Carolina Hurricanes  33     33         SE      ellisda01  Dan Ellis               2011    AND        G
   CAR    Carolina Hurricanes  33     33         SE      macindr01  Drew MacIntyre          2011    BUF        G
   CAR    Carolina Hurricanes  33     33         SE      karlshe01  Henrik Karlsson         2011    CAL        G
   CAR    Carolina Hurricanes  33     33         SE      tarkkii01  Iiro Tarkki             2011    AND        G
   CAR    Carolina Hurricanes  33     33         SE      gigueje01  Jean-Sebastien Giguere  2011    COL        G
   CAR    Carolina Hurricanes  33     33         SE      drouije01  Jeff Drouin-Deslauriers 2011    AND        G
   CAR    Carolina Hurricanes  33     33         SE      enrotjh01  Jhonas Enroth           2011    BUF        G
   CAR    Carolina Hurricanes  33     33         SE      howarji01  Jimmy Howard            2011    DET        G
   CAR    Carolina Hurricanes  33     33         SE      macdojo01  Joey MacDonald          2011    DET        G
   CAR    Carolina Hurricanes  33     33         SE      hillejo01  Jonas Hiller            2011    AND        G
   CAR    Carolina Hurricanes  33     33         SE      peterju01  Justin Peters           2011    CAR        G
   CAR    Carolina Hurricanes  33     33         SE      lehtoka01  Kari Lehtonen           2011    DAL        G
   CAR    Carolina Hurricanes  33     33         SE      irvinle01  Leland Irving           2011    CAL        G
   CAR    Carolina Hurricanes  33     33         SE      turcoma01  Marty Turco             2011    BOS        G
   CAR    Carolina Hurricanes  33     33         SE      kiprumi01  Miikka Kiprusoff        2011    CAL        G
   CAR    Carolina Hurricanes  33     33         SE      murphmi02  Mike Murphy             2011    CAR        G
   CAR    Carolina Hurricanes  33     33         SE      emeryra01  Ray Emery               2011    CHI        G
   CAR    Carolina Hurricanes  33     33         SE      bachmri01  Richard Bachman         2011    DAL        G
   CAR    Carolina Hurricanes  33     33         SE      millery01  Ryan Miller             2011    BUF        G
   CAR    Carolina Hurricanes  33     33         SE      varlasi01  Semyon Varlamov         2011    COL        G
   CAR    Carolina Hurricanes  33     33         SE      hunwish01  Shawn Hunwick           2011    CBS        G
   CAR    Carolina Hurricanes  33     33         SE      masonst01  Steve Mason             2011    CBS        G
   CAR    Carolina Hurricanes  33     33         SE      thomati01  Tim Thomas              2011    BOS        G
   CAR    Carolina Hurricanes  33     33         SE      rasktu01   Tuukka Rask             2011    BOS        G
   CAR    Carolina Hurricanes  33     33         SE      conklty01  Ty Conklin              2011    DET        G
   FLO    Florida Panthers     38     26         SE      yorkal01   Allen York              2011    CBS        G
   FLO    Florida Panthers     38     26         SE      raycran01  Andrew Raycroft         2011    DAL        G
   FLO    Florida Panthers     38     26         SE      khudoan01  Anton Khudobin          2011    BOS        G
   FLO    Florida Panthers     38     26         SE      bouchbr01  Brian Boucher           2011    CAR        G
   FLO    Florida Panthers     38     26         SE      wardca01   Cam Ward                2011    CAR        G
   FLO    Florida Panthers     38     26         SE      crawfco01  Corey Crawford          2011    CHI        G
   FLO    Florida Panthers     38     26         SE      sanfocu01  Curtis Sanford          2011    CBS        G
   FLO    Florida Panthers     38     26         SE      ellisda01  Dan Ellis               2011    AND        G
   FLO    Florida Panthers     38     26         SE      macindr01  Drew MacIntyre          2011    BUF        G
   FLO    Florida Panthers     38     26         SE      karlshe01  Henrik Karlsson         2011    CAL        G
   FLO    Florida Panthers     38     26         SE      tarkkii01  Iiro Tarkki             2011    AND        G
   FLO    Florida Panthers     38     26         SE      gigueje01  Jean-Sebastien Giguere  2011    COL        G
   FLO    Florida Panthers     38     26         SE      drouije01  Jeff Drouin-Deslauriers 2011    AND        G
   FLO    Florida Panthers     38     26         SE      enrotjh01  Jhonas Enroth           2011    BUF        G
   FLO    Florida Panthers     38     26         SE      howarji01  Jimmy Howard            2011    DET        G
   FLO    Florida Panthers     38     26         SE      macdojo01  Joey MacDonald          2011    DET        G
   FLO    Florida Panthers     38     26         SE      hillejo01  Jonas Hiller            2011    AND        G
   FLO    Florida Panthers     38     26         SE      peterju01  Justin Peters           2011    CAR        G
   FLO    Florida Panthers     38     26         SE      lehtoka01  Kari Lehtonen           2011    DAL        G
   FLO    Florida Panthers     38     26         SE      irvinle01  Leland Irving           2011    CAL        G
   FLO    Florida Panthers     38     26         SE      turcoma01  Marty Turco             2011    BOS        G
   FLO    Florida Panthers     38     26         SE      kiprumi01  Miikka Kiprusoff        2011    CAL        G
   FLO    Florida Panthers     38     26         SE      murphmi02  Mike Murphy             2011    CAR        G
   FLO    Florida Panthers     38     26         SE      emeryra01  Ray Emery               2011    CHI        G
   FLO    Florida Panthers     38     26         SE      bachmri01  Richard Bachman         2011    DAL        G
   FLO    Florida Panthers     38     26         SE      millery01  Ryan Miller             2011    BUF        G
   FLO    Florida Panthers     38     26         SE      varlasi01  Semyon Varlamov         2011    COL        G
   FLO    Florida Panthers     38     26         SE      hunwish01  Shawn Hunwick           2011    CBS        G
   FLO    Florida Panthers     38     26         SE      masonst01  Steve Mason             2011    CBS        G
   FLO    Florida Panthers     38     26         SE      thomati01  Tim Thomas              2011    BOS        G
   FLO    Florida Panthers     38     26         SE      rasktu01   Tuukka Rask             2011    BOS        G
   FLO    Florida Panthers     38     26         SE      conklty01  Ty Conklin              2011    DET        G
...