SQL Server - lack of NATURAL JOIN / x JOIN y USING

问题:

I've just been reading up on NATURAL JOIN / USING - SQL92 features which are (sadly?) missing from SQL Server's current repertoire.

Has anyone come from a DBMS that supported these to SQL Server (or another non-supporting DBMS) - were they as useful as they sound, or a can of worms (which also sounds possible!)?

回答1:

I never use NATURAL JOIN because I don't like the possibility that the join could do something I don't intend just because some column name exists in both tables.

I do use the USING join syntax occasionally, but just as often it turns out that I need a more complex join condition than USING can support, so I convert it to the equivalent ON syntax after all.

回答2:

Would you consider a DBMS that was truly relational?:

in Tutorial D [a truly relational language], the only “join” operator is called JOIN, and it means “natural join”... There should be no other kind of join... Few people have had the experience of using a proper relational language. Of those who have, I strongly suspect that none of them ever complained about some perceived inconvenience in pairing columns according to their names

Source: "The Importance of Column Names" by Hugh Darwen

回答3:

Related SO question: is NATURAL JOIN any better than SELECT FROM WHERE in terms of performance ?

A NATURAL JOIN is not a handy shortcut: it's downright dangerous. It's quite rare in DBMS.

USING is explicit, but given it's limitations you can't use it everywhere so ON would be be consistent, no?

回答4:

I don't see the value of the USING or NATURAL syntax - as you've encountered, only ON is consistently implemented so it's best from a portability standpoint.

Being explicit is also better for maintenance, besides that the alternatives can be too limited to deal with situations. I'd also prefer my codebase be consistent.

回答5:

It's a matter of convenience. Not indispensable, but it should have its place, for example in interactive querying (every keystroke brings us closer to RSI, anyway), or some simple cases of hand written SQL even in production code (yes, I wrote that. And even seen JOIN USING in serious code, written by wise programmers other than myself. But, I'm digressing).

I found this question when looking for confirmation that SS is missing this feature, and I got it. I am only bewildered by the amount of hate against this syntax, which I attribute to the Sour Grapes Syndrome. I feel amused when being lectured with a patronising tone Sweets (read: syntactic sugar) is bad for your health. You don't need it anyway.

What is nice in the JOIN USING syntax, is that it works not just on column names, but also on column aliases, for example:

-- foreign key "order".customerId references (customer.id)
SELECT c.*, c.id as customerId, o.* from customer c 
join "order" o using (customerId);

I don't agree with "Join using would be better, if only (...)". Or the argument, that you may need more complex conditions. From a different point of view, why use JOIN ON? Why not be pure, and move all conditions to the WHERE clause?

SELECT t1.*, t2.* from t1, t2 where t2.t1_id = t1.id;

I could now go mad and argue, how this is the cleanest way to express a join, and you can immediately start adding more conditions in the where clause, which you usually need anyway, blah blah blah...

So you shouldn't miss this particular syntax too dearly, but there's nothing to be happy about for not having it ("Phew, that was close. So good not to have JOIN USING. I was spared a lot of pain").

So, while I personally use JOIN ON 99% of the time, I feel no Schadenfreude when there is no JOIN USING or NATURAL JOIN.