Post reply

Is the SQL shorthand notation for equi-joins, by way of the USING construct deprecated?

  • March 8, 2011 at 7:49 am

    #236240

    I am working with a company right now that is used to using mySQL and now I have them using SQL Server 2008 for a project of ours and they have been trying to make use of the USING construct to shorthand some join statements. It has not been working in their statements so I explained how I would have written it using SQL Server and it worked for them. I have never used this syntax before and I noticed that in 2005 and 2008 it doesn't work.

    My questions are:

    a.) Is this a deprecated syntax in SQL 2005+ ?

    b.) Is this even an ANSI standard or just a shorthand way that other databases use? I can't find and ANSI reference to it so I would assume it is not but I am not sure.

    Thanks,

    Jim

  • March 8, 2011 at 7:55 am

    #1295889

    USING is not a SQL Server keyword. It's not that it's deprecated, it's that it's not valid.

    As for ANSI, no idea.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 8, 2011 at 8:14 am

    #1295904

    Thanks for the reply.

    From what I was reading it seems as though the USING syntax worked back in version 7 and Sql Server 2000 based on an older SQL Server book they showed me. Not that I have tried it, just going off what I read. However, there is limited documentation in regards to this for any database.

    Jim

  • March 8, 2011 at 9:10 am

    #1295948

    "Using" is too common a word to do an effective search on. I can't speak for the standards or whatever, but I've never seen it used in a join.

    Do you have a sample? If so, I can test it in a SQL 2000 instance.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • March 8, 2011 at 9:18 am

    #1295957

    Sure here is an example you could tweak to work in your case:

    SELECT *

    FROM employee INNER JOIN department

    USING (DepartmentID);

    The equivalent way I would write this today would be:

    SELECT *

    FROM employee INNER JOIN department

    ON employee.DepartmentID = department.DepartmentID

    Hope this helps.

  • March 8, 2011 at 2:21 pm

    #1296158

    I have seen Itzik talk about the USING clause for joins and the fact that it is missing from SQL Server. It is in the ANSI standard, but apparently not yet supported in SQL Server.

    It has been requested if you care to vote for it:

    http://connect.microsoft.com/SQLServer/feedback/details/153679/natural-join-and-using-clause-in-joins

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 8, 2011 at 2:35 pm

    #1296171

    Jim Rathmann (3/8/2011)

    Sure here is an example you could tweak to work in your case:

    SELECT *

    FROM employee INNER JOIN department

    USING (DepartmentID);

    The equivalent way I would write this today would be:

    SELECT *

    FROM employee INNER JOIN department

    ON employee.DepartmentID = department.DepartmentID

    Hope this helps.

    Yep, helped tremendously. It's a handy shortcut but not really necessary. I could see it keeping me from some boneheaded typo mistakes when I join a table on itself, but it's not really necessary. I can understand why M$ thinks this is a low-priority item.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • March 8, 2011 at 9:42 pm

    #1296286

    As a bit of a sidebar, although USING has been implemented in many database engines, I hope it never takes off in SQL Server... I just don't like "natural" joins (see below) even if they're partitially defined. Besides, just like ISNULL and COALESE are converted to CASE statements behind the scenes, I suspect MS would implement USING as ON behind the scenes.

    The reason I don't like "natural" joins or "semi-natural" joins is simply because they're not explicit in the code. I don't use SELECT * even in simple "pass through/synonym-like" views for the same reason. Too much can go wrong behind the scenes. If someone changes the column order in the underlying tables but the datatypes are either the same or can implicitly be converted, you'll get the wrong data in the wrong column from the view and know one will know.

    Anothr reason is that consistancy in code is precious especially when under the gun to troubleshoot a large piece of code. How many ways do we really need to be able to write a simple join?

    Last but not least, I'd like to see MS spending their time on code changes that actually do something... like maybe making PIVOT work as good as it does in access or making SUM() OVER work correctly. 😉

    T-SQL isn't a "programming" language... let's keep it that way. 😛

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

Viewing 8 posts - 1 through 8 (of 8 total)

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