FirebirdSQL logo

Natural joins

Taking the idea of the named columns join a step further, a natural join performs an automatic equi-join on all the columns that have the same name in the left and right table.The data types of these columns must be compatible.

Note

Natural joins are not supported in Dialect 1 databases.

Syntax
<natural-join> ::=
  <table-reference> NATURAL [<join-type>] JOIN <table-primary>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Given these two tables:

create table TA (
  a bigint,
  s varchar(12),
  ins_date date
);
create table TB (
  a bigint,
  descr varchar(12),
  x float,
  ins_date date
);

A natural join on TA and TB would involve the columns a and ins_date, and the following two statements would have the same effect:

select * from TA
  natural join TB;
select * from TA
  join TB using (a, ins_date);

Like all joins, natural joins are inner joins by default, but you can turn them into outer joins by specifying LEFT, RIGHT or FULL before the JOIN keyword.

Caution

If there are no columns with the same name in the two source relations, a CROSS JOIN is performed.We’ll get to this type of join next.

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'
Mixing Explicit and Implicit Joins

Mixing explicit and implicit joins is not recommend, but is allowed.However, some types of mixing are not supported by Firebird.

For example, the following query will raise the error “Column does not belong to referenced table”

SELECT *
FROM TA, TB
JOIN TC ON TA.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2

That is because the explicit join cannot see the TA table.However, the next query will complete without error, since the restriction is not violated.

SELECT *
FROM TA, TB
JOIN TC ON TB.COL1 = TC.COL1
WHERE TA.COL2 = TB.COL2