Post reply

NATURAL JOIN

  • May 23, 2009 at 8:01 pm

    #999401

    RBarryYoung (5/23/2009)

    Say what!!! You're quoting ANSI standards??? ...

    Alright buddy, Who are you and what have you done with Jeff!

    😀

    BWAA-HAAA!!! No, I haven't lost my tin hat, Barry. 😀 Someone said they weren't in the Ansi standards so I had to quote them instead of someone just taking my word for it. The only reason why I got a copy of them in the first place was as a form of self-defense against ANSI-ONLY zealots. :hehe:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 3, 2015 at 5:44 am

    #1837328

    Why doesn't Microsoft SQL Server implement NATURAL JOIN?

    It is in the SQL Standard.

    I know it can be tricky (when fields have the same name such as DateUpdated), but it is an exception and I would be very glad if they have it implemented.

    ________________
    DBA Cabuloso
    Lucas Benevides

  • November 3, 2015 at 12:03 pm

    #1837461

    DBA Cabuloso (11/3/2015)

    Why doesn't Microsoft SQL Server implement NATURAL JOIN?

    It is in the SQL Standard.

    I know it can be tricky (when fields have the same name such as DateUpdated), but it is an exception and I would be very glad if they have it implemented.

    Personally, I see no benefit from a natural join. Care to explain why you think they worth while? I did work with Oracle for a little while and never found a need for it there even though it was available.

  • November 4, 2015 at 6:20 am

    #1837624

    I can try to explain,

    In the regular DBA job, we query our databases everyday.

    If we use Natural Join, it is more concise, we write less.

    We don't have to declare the ON clause if the fields have the same name. In my database models, very often (almost everytime) the Primary key and the Foreign Key have the same name. So we can JOIN tables faster and more clearly.

    In addition, the Natural JOIN implictly resolves the ambiguity, so, if the two fields are equal, we don't have to choose one to display. And in the Natural Join, if we use SELECT * the resultSet comes with only one ocurrence of the join fields.

    But as I said before, Natural Join should never come into Application code. Because if in the future we add to our tables some fields with the same name, such as (DateAltered) it will have an unexpected behavior. Maybe that's why Microsoft never implemented.

    ________________
    DBA Cabuloso
    Lucas Benevides

  • November 4, 2015 at 9:04 am

    #1837713

    DBA Cabuloso (11/4/2015)

    I can try to explain,

    In the regular DBA job, we query our databases everyday.

    If we use Natural Join, it is more concise, we write less.

    We don't have to declare the ON clause if the fields have the same name. In my database models, very often (almost everytime) the Primary key and the Foreign Key have the same name. So we can JOIN tables faster and more clearly.

    In addition, the Natural JOIN implictly resolves the ambiguity, so, if the two fields are equal, we don't have to choose one to display. And in the Natural Join, if we use SELECT * the resultSet comes with only one ocurrence of the join fields.

    But as I said before, Natural Join should never come into Application code. Because if in the future we add to our tables some fields with the same name, such as (DateAltered) it will have an unexpected behavior. Maybe that's why Microsoft never implemented.

    Don't see any benefit from what you mention above. Besides, I prefer to make sure things are explicitly defined especially in my joins.

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply