Alternatives To Natural Joins

RDBMS "natural joins" usually require that keys use the same name across tables. This is limiting because it assumes a certain convention is used, a convention which an existing organization may not necessary follow, and which I personally don't like. ( I prefer x_id and x_ref).

An alternative is an "autojoin" DataDictionary table, something like:

table: autoKeys ---------------- table_name_1 column_name_1 table_name_2 column_name_2 join_type // outer, left inner, right inner, etc. display_sequence

The SQL would then resemble:

  SELECT * 
  AUTOJOIN tableX, tableY, tableZ

The AUTOJOIN clause checks the autoKeys table for matches and "substitutes" the appropriate clauses (long form of join).

A fancier version would allow named groups so that more customized versions of autojoins can be created.

-top

Whilst I can appreciate the value of using a DataDictionary to retrieve information about permanent foreign key relationships, it is rather awkward and unwieldy for ad-hoc purposes, and it presumes some infrastructure that will keep the "autoKeys" table synchronised with other aspects of the schema.

I assumed one would use the "regular" old-fashsioned join approach for atypical joins. This supplements it, not replaces it. Most joins I use in practice even in ad-hoc queries could use the above pattern, at least for non-transient tables. -t

In TutorialDee, there is a RENAME operator which allows differing attribute names to temporarily be made the same to support natural JOINs, or temporarily different to avoid them. For example, given the following RelVars:

 var departments real relation {dname char, city char} key {dname};
 var employees real relation {ename char, address char, dname_fk char} key {ename};

We can join the two via dname_fk <--> dname as follows:

 employees RENAME (dname_fk AS dname) JOIN departments

SMEQL (TqlRoadmap) has a similar feature. It seems we agree that common SQL lacks sufficient de/re-composability features (SqlFlaws).

Obviously, it would be preferable to use user-defined types to prevent inadvertent JOINs on semantically incompatible attributes. We don't want to accidentally (or deliberately) join the department name to the employee name. Furthermore, we can use types to clearly indicate JOINable attributes, thus rendering unnecessary the "autoKeys" table described above. So:

 type dname possrep {name char};
 type ename possrep {name char};

 var departments real relation {dname dname, city char} key {dname};
 var employees real relation {ename ename, address char, dname_fk dname} key {ename};

Join as follows:

 employees RENAME (dname_fk AS dname) JOIN departments

Note that the JOIN query is the same as the example using CHAR instead of a user-defined type, but with the added benefit of TypeSafety.