FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

Cross joins

A cross join produces the full set product — or Cartesian product — of the two data sources.This means that it successfully matches every row in the left source to every row in the right source.

Syntax
FROM <table-reference> [, <table-reference> ...]

<cross-join>
  <table-reference> CROSS JOIN <table-primary>

Use of the comma syntax is discouraged, and we recommend using the explicit join syntax.

Cross-joining two sets is equivalent to joining them on a tautology (a condition that is always true).The following two statements have the same effect:

select * from TA
  cross join TB;
select * from TA
  join TB on TRUE;

Cross joins are inner joins, because they only include matching records –- it just so happens that every record matches!An outer cross join, if it existed, wouldn’t add anything to the result, because what outer joins add are non-matching records, and these don’t exist in cross joins.

Cross joins are seldom useful, except if you want to list all the possible combinations of two or more variables.Suppose you are selling a product that comes in different sizes, different colors and different materials.If these variables are each listed in a table of their own, this query would return all the combinations:

select m.name, s.size, c.name
  from materials m
  cross join sizes s
  cross join colors c;
Implicit Joins

In the SQL:89 standard, the tables involved in a join were specified as a comma-delimited list in the FROM clause (in other words, a cross join).The join conditions were then specified in the WHERE clause among other search terms.This type of join is called an implicit join.

An example of an implicit join:

/*
 * A sample of all Detroit customers who
 * made a purchase.
 */
SELECT *
FROM customers c, sales s
WHERE s.cust_id = c.id AND c.city = 'Detroit'