SQL: Visual QuickStart Guide

Creating a Cross Join with CROSS JOIN

A cross join:

• Returns all possible combinations of rows of two tables. The result contains all rows from the first table; each row from the first table is combined with all rows from the second table.

• Doesn’t use a join condition. To create a cross join, omit the ON clause if you’re using JOIN syntax, or omit the WHERE clause if you’re using WHERE syntax.

• Seldom is used in practice, as the result is hard to interpret.

• Can produce a huge result, even with small tables. If one table contains m rows and the other contains n rows, the result contains m × n rows.

• Is a computationally expensive and time-consuming query.

• Also is called a Cartesian product or cross product.

To create a cross join:

• Type:

  SELECT columns ...