Would “dereferencing” a foreign key in SQL theoret

问题:

Suppose we have 2 tables, a and b:

CREATE TABLE a (id_a INT NOT NULL PRIMARY KEY, id_b INT NOT NULL)
  INDEX fk_id_b (id_b ASC),
    CONSTRAINT fk_id_b FOREIGN KEY (id_b)
    REFERENCES b (id_b);
CREATE TABLE b (id_b INT NOT NULL PRIMARY KEY, b_data INT NOT NULL);

So a has the following columns: id_a and id_b, where id_b is a foreign key to bs id_b. When I want to get the associated b_data from a, I have to do a join:

SELECT id_a, b_data FROM a JOIN b ON a.id_b=b.id_b;

It works fine, but it's long, I repeat myself (which I shouldn't according to the ruby guys), so I thought of a way to make this query shorter, easier to understand and still unambiguous:

SELECT id_a, id_b->b_data FROM a;

foreign_key->column would behave like a pointer to a structure, the database would automatically join the needed tables.

I know this doesn't exist, that making it a standard would probably take so much time I wouldn't live to see it in production ready database systems and some people wouldn't want it as "it looks weird", but I would at least like to know, if it would be possible, and if not, why.

回答1:

One of the major advantages of the relational model of data is that it eliminates the need to rely on hard coded links/pointers/navigational structures between tables. Data access is via table and attribute names using relational expressions like joins.

A model that persisted navigational structures in the database would be less flexible and dynamic - when you changed table structures you would invalidate or have to change the navigational structures as well. Your question also only addresses those joins which happen to be equijoins on foreign keys. Joins are much more general than that.

回答2:

First

• Ruby isn't SQL, SQL isn't Ruby
• SQL also predates almost every current mainstream or fashionable language

However, one thing to bear in mind, and the most important...

Repeating the JOIN is not the same as repeating the query. You'll have different

• WHERE filters
• SELECT list
• Maybe an aggregate

Each of these is different query and will require different indexes/plans

Using a view to mask the JOIN will be the next great idea suggestion to "encapsulate" it. However, you'll end up with view joining view joining view... and a view is just a macro that expands. So your queries will start to run poorly.

Using an indexed view may not be a solution because of different filters etc

Edit, from Dems:

These types of ideas work in simple cases, but create more problems in complex cases. The current syntax handles expression of set based queries equally well/poorly across a very wide range of complexity.

回答3:

SQL has a NATURAL JOIN operator e.g. your query would be:

SELECT DISTINCT * 
  FROM a NATURAL JOIN b;

However, it looks like you want to do a semi-join, for which SQL has no specific operator :(

As you are interested in language design, consider the truly relational language Tutorial D (designed for academic purposes) has a semi-join operator MATCHING e.g. your query would simply be:

a MATCHING b;