FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

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;