Natural Join

A natural join is where either schema information of naming conventions allow the query system to perform needed joins without having to explicitly state the joins. Only the tables need to be specified, not their relation.

The existing implementations seem to have the problem that they require identically-named columns be in each table. For example, "empID" may be the primary key of the Employee table but also a foreign key of the payCheck table. This can be problematic for at least two reasons. First, an existing database may have the names entrenched, requiring lots of code rewrite to bring them into the naming convention needed by this name matching. The second is that I prefer to use "ref" as a suffix for foreign keys ("empID" verus "empRef"). It makes the schema more self-documenting in my opinion. Third, many DB's have the convention that EachColumnNameBeUniqueAmongTheTablespace. This can reduce the need for table references in the code and create less risk of ambiguity. Regardless of whether you agree with this convention, it is fairly popular among COTS systems.

Thus, a better approach would be to use relationships defined to the database (somewhere in the DataDictionary) as the way for queries to ascertain join paths.

Something like ConceptualQueries?

I don't think so. I mean like pre-stored join info such that you only have to mention join info if you deviate from the pre-defined relationships/joins between tables.

Oh, you mean like the relationships in JPAQL/HQL/ObjectRelationalMapping that match integrity rules and allow you to only have to mention join info if you deviate from the pre-defined relationships/joins between tables. Yes they are nice, but since they are affected by changes in multiplicity I think they do not help as much as they could.

It shouldn't be difficult to specify natural joins explicitly as part of the schema.

NATURAL JOIN X Y ON X.columnInX = Y.columnInY

In a more generic sense, it is also very technically feasible to use generalized queries for the natural joins.

 NATURAL JOIN Y Z IS SELECT ...

CategoryDatabase