locked
Why would an equi-join with USING construct cause an error? RRS feed

  • Question

  • SQL 2008 R2, I tried the following equi-join, and it worked fine:

    SELECT *
    FROM employee INNER JOIN department 
    ON employee.DepartmentID = department.DepartmentID;
    
    

    But when I did the following equi-join with the USING construct, I got an error:

    SELECT *
     FROM employee INNER JOIN department 
      USING (DepartmentID);
    
    Msg 321, Level 15, State 1, Line 4
    "DepartmentID" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.
    

    Why would the second equi-join cause an error?


    Thanks
    Jeff in Seattle
    Friday, August 27, 2010 5:49 AM

Answers

  • Using construct is proprietary to MySQL

    in T-SQL we use JOIN and ON Clause during the join...

    • Proposed as answer by Kent Waldrop Friday, August 27, 2010 8:13 PM
    • Marked as answer by jeff00seattle_2 Thursday, September 2, 2010 3:46 PM
    Friday, August 27, 2010 4:41 PM

All replies

  • What are you hoping to achieve with the "USING()"?
    Friday, August 27, 2010 7:20 AM
  • Jeff, 

    Your syntax is wrong. I never see anyone used "USING". After Table/View name has to be "ON". 

    http://msdn.microsoft.com/en-us/library/aa213233(v=SQL.80).aspx

     

    Friday, August 27, 2010 4:05 PM
  • Where is no such syntax in T-SQL. Where did you read it? Are you trying LINQ query?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 27, 2010 4:13 PM
    Answerer
  • Using construct is proprietary to MySQL

    in T-SQL we use JOIN and ON Clause during the join...

    • Proposed as answer by Kent Waldrop Friday, August 27, 2010 8:13 PM
    • Marked as answer by jeff00seattle_2 Thursday, September 2, 2010 3:46 PM
    Friday, August 27, 2010 4:41 PM
  • There is no kind of USING clause used with JOINS in MS SQL Server, any version.

    You have to live with ON clause to match the columns.

     

    USING clause though a good way to match identical columns in separate tables.

    It only exists in MySQL, Oracle & DB2 databases.

     

    SELECT *

    FROM A

    JOIN B

    USING(id)

     

    ... is equivalent to:

     

    SELECT *

    FROM A

    JOIN B

    ON A.id = B.id


    -Manoj

    Friday, September 3, 2010 9:15 AM