FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 
Named columns joins

Equi-joins often compare columns that have the same name in both tables.If this is the case, we can also use the second type of qualified join: the named columns join.

Note

Named columns joins are not supported in Dialect 1 databases.

Named columns joins have a USING clause which states only the column names.So instead of this:

select * from flotsam f
  join jetsam j
  on f.sea = j.sea
  and f.ship = j.ship;

we can also write:

select * from flotsam
  join jetsam using (sea, ship)

which is considerably shorter.The result set is a little different though — at least when using “SELECT *”:

  • The explicit-condition join — with the ON clause — will contain each of the columns SEA and SHIP twice: once from table FLOTSAM, and once from table JETSAM.Obviously, they will have the same values.

  • The named columns join — with the USING clause — will contain these columns only once.

If you want all the columns in the result set of the named columns join, set up your query like this:

select f.*, j.*
  from flotsam f
  join jetsam j using (sea, ship);

This will give you the same result set as the explicit-condition join.

For an OUTER named columns join, there’s an additional twist when using “SELECT *” or an unqualified column name from the USING list:

If a row from one source set doesn’t have a match in the other but must still be included because of the LEFT, RIGHT or FULL directive, the merged column in the joined set gets the non-NULL value.That is fair enough, but now you can’t tell whether this value came from the left set, the right set, or both.This can be especially deceiving when the value came from the right hand set, because “*” always shows combined columns in the left hand part — even in the case of a RIGHT join.

Whether this is a problem or not depends on the situation.If it is, use the “a.*, b.*” approach shown above, with a and b the names or aliases of the two sources.Or better yet, avoid “*” altogether in your serious queries and qualify all column names in joined sets.This has the additional benefit that it forces you to think about which data you want to retrieve and where from.

It is your responsibility to make sure the column names in the USING list are of compatible types between the two sources.If the types are compatible but not equal, the engine converts them to the type with the broadest range of values before comparing the values.This will also be the data type of the merged column that shows up in the result set if “SELECT *” or the unqualified column name is used.Qualified columns on the other hand will always retain their original data type.

Tip

If, when joining by named columns, you are using a join column in the WHERE clause, always use the qualified column name, otherwise an index on this column will not be used.

SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE x = 0;

-- PLAN JOIN (A NATURAL , B INDEX (RDB$2))

However:

SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE a.x = 0;
-- PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

SELECT 1 FROM t1 a JOIN t2 b USING (x) WHERE b.x = 0;
-- PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

The fact is, the unspecified column in this case is implicitly replaced by `COALESCE(a.x, b.x).This trick is used to disambiguate column names, but it also interferes with the use of the index.

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.