3

I have the following JOIN:

SELECT * FROM tableA INNER JOIN tableB USING (commonColumn)

I get an error:

"commonColumn" 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.

The following instead works:

SELECT * FROM tableA INNER JOIN tableB ON tableA.commonColumn = tableB.commonColumn

The compatibility level in my case is set to 100 (SQL Server 2008), while, by the way, I am working with SQL Server 2012.

What am I doing wrong? I find it very difficult to find example of the use of the keyword USING, as it is almost impossible to do a relevant web search. Yet, it seems the right thing to use when the "joining columns" have the same name...

Antonio
  • 19,451
  • 13
  • 99
  • 197

2 Answers2

1

USING is not supported SQL Server syntax. It's not a reserved keyword, either, so the query engine is using that as a table alias.

It is an ODBC keyword, but those are handled somewhat differently. The engine won't always complain if you use them, but you're not supposed to use them anyways.

It is also listed as a possible future reserved keyword. It's common for new editions of SQL Server to add words to the core reserved list.

Personally, I don't see them adding NATURAL JOIN syntax support, even with USING. A lot of DBAs consider NATURAL JOINs problematic.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • Tricky, the ANSI SQL spec has USING as reserved. – jarlh Sep 09 '15 at 12:53
  • It's not an "ODBC keyword", this is a keyword defined in the SQL standard to define the join columns. –  Sep 09 '15 at 13:50
  • @a_horse_with_no_name Microsoft calls it an ODBC keyword in the link I provoded. Talk to them if you don't like the terminology. – Bacon Bits Sep 09 '15 at 13:58
1

The USING keyword is used to specify the source data for MERGE statements (called <table source>) in the documentation.

Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137