User's Guide
   PART 1. Working with Databases
     CHAPTER 6. Joins: Retrieving Data from Several Tables       

Cross joins

As for other types of joins, each row in a cross join is a combination of one column from the first table and one column from the second table. Unlike other joins, a cross join contains no restrictions. All possible combinations of rows are present.

Each row of the first table appears exactly once with each row of the second table. Hence, the number of rows in the join is the product of the number of rows in the individual tables.

Inner and outer modifiers do not apply to cross joins 

Except in the presence of additional restrictions, all rows of both tables always appear in the result. Thus, the keywords INNER, LEFT OUTER and RIGHT OUTER are not applicable to cross joins.

The query

SELECT *
FROM table1 CROSS JOIN table2

has a result set as follows:

• As long as table1 is not the same name as table2:

  • The result set includes all columns in table1 and all columns in table2.

  • There is one row in the result set for each combination of a row in table1 and a row in table2. If table1 has n1 rows and table2 has n2 rows, the query returns n1 x n2 rows.

• If table1 is the same table as table2, and neither is given a correlation name, the result set is simply the rows of table1.

  Self-joins and cross joins

The following self-join produces a list of pairs of employees. Each employee names appears in combination with every employee name.

SELECT a.emp_fname, a.emp_lname,
      b.emp_fname, b.emp_lname
FROM employee AS a CROSS JOIN employee AS b

emp_fname	emp_lname	Emp_fname	emp_lname
Fran	Whitney	Fran	Whitney
Matthew	Cobb	Fran	Whitney
Philip	Chin	Fran	Whitney
Julie	Jordan	Fran	Whitney
Robert	Breault	Fran	Whitney
Melissa	Espinoza	Fran	Whitney
Jeannette	Bertrand	Fran	Whitney

Since the employee table has 75 rows, this join contains 75 x 75 = 5625 rows. It includes, however, rows which that list each employee with themselves. For example, it contains the row

emp_fname	emp_lname	emp_fname	emp_lname
Fran	Whitney	Fran	Whitney

To remove that list the same employee's name twice, use the following command.

SELECT a.emp_fname, a.emp_lname,
      b.emp_fname, b.emp_lname
FROM employee AS a CROSS JOIN employee AS b
WHERE a.emp_lname <> b.emp_lname
   OR a.emp_fname <> b.emp_fname

Without these rows, the join contains 75 x 74 = 5550 rows.

This join contains rows that pair each employee with every other employee, but because each pair of names can appear in two possible orders, each pair appears twice. For example, the result of the above join contains the following two rows.

emp_fname	emp_lname	emp_fname	emp_lname
Matthew	Cobb	Fran	Whitney
Fran	Whitney	Matthew	Cobb

If the order of the names is not important, you can produce a list of the (75 x 74)/2! = 2775 unique pairs.

SELECT a.emp_fname, a.emp_lname,
      b.emp_fname, b.emp_lname
FROM employee AS a CROSS JOIN employee AS b
WHERE a.emp_lname < b.emp_lname
   OR (a.emp_lname = b.emp_lname
   AND a.emp_fname < b.emp_fname)

This statement eliminates duplicate lines by selecting only those rows in which the name of employee a is alphabetically less than that of employee b.

     For more information, see Self-joins and correlation names.