SQL Server - lack of NATURAL JOIN / x JOIN y USING(field)

views:

answers:

SQL Server - lack of NATURAL JOIN / x JOIN y USING(field)

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!)?

+6 A:

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.

Bill Karwin 2010-07-18 23:01:07

+1 for sticking with the ON syntax.

Fosco 2010-07-18 23:03:44

I was thinking along the same lines with `NATURAL JOIN` - it would be nicer if this join type instead did some 'foreign key' join (as it stands, `NATURAL JOIN`ing tables with e.g. IsDeleted columns would be nonsensical). Thanks Bill.

Will A 2010-07-18 23:04:15

Quite. It's easy when adding a column to a table to overlook this point and create a join condition where one was not intended (he says, having done precisely that on a number of occasions).

Brian Hooper 2010-07-19 01:12:00

I can think of no circumstance where I would even be tempted to use a natural join, the very idea gives mea migraine.

HLGEM 2010-07-20 17:25:18

If SQL somehow prohibited columns from using the same name *unless* they have the same logical type (not merely the same data type, but they really mean the same thing), then natural join might make more sense.

Bill Karwin 2010-07-20 17:34:47

+2 A:

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.

OMG Ponies 2010-07-19 00:06:31

+2 A:

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?

gbn 2010-07-19 03:41:32

+2 A:

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

onedaywhen 2010-07-19 08:10:13

ansaurus

tags:

views:

answers:

SQL Server - lack of NATURAL JOIN / x JOIN y USING(field)

related questions