is this SQL incomplete?

jackdaw

select * from ( alarmactivity join alarminfo using (alarm_key)) join model using (model_h)



this code someone sent me ... surely the joins in the () are incomplete...

ronivek

It looks like it should work I think; assuming the relations are correctly defined.

Are you having some sort of problem trying to execute the statement or...?

showry

It looks fine; as long as the column name is the same in both tables that syntax is fine and will reduce the duplication of the column from both tables in the result set.

jackdaw

jackdaw wrote: »

select * from ( alarmactivity join alarminfo using (alarm_key)) join model using (model_h)



this code someone sent me ... surely the joins in the () are incomplete...

surely it should be something like select * from alarmactivity a , alarminfo b,
where a.alarm_key = b.alarm_key ....


i dunno ... i just never saw it in that syntax ... i thought the joins had to be specified ..

showry

jackdaw wrote: »

surely it should be something like select * from alarmactivity a , alarminfo b,
where a.alarm_key = b.alarm_key ....


i dunno ... i just never saw it in that syntax ... i thought the joins had to be specified ..

Try it both ways and see.
If you specify the joins you'll see the column from each table in your result set e.g. alarmactivity.alarm_key, alarminfo.alarm_key.
Using the other syntax you'll only see 1 alarm_key column.

kayos

jackdaw wrote: »

surely it should be something like select * from alarmactivity a , alarminfo b,
where a.alarm_key = b.alarm_key ....


i dunno ... i just never saw it in that syntax ... i thought the joins had to be specified ..

It’s an Oracle/MySQL thing and is not ANSI compliant (afaik) so if you don’t use either of those RDBMS you will not have seen it. It is akin to the 9i NATURAL JOIN statement except it allows you to restrict the number of same name columns used to join the tables. All it boils down to is syntax shorthand the query plan would be the same using an ANSI join as the USING syntax.

The problems are if the schema has any name changes then your query breaks and if you are porting over to another RDBMS then you are going to have to go rewrite your query. Performance wise it is the exact same as using an ANSI compliant join syntax.

From my point of view there would be no production query I would write without specifying the output columns as it leaves you open to schema changes possibly causing problems. Just like in MS SQL I opt to use COALESCE rather than the non ANSI more commonly used ISNULL. I don’t know but I prefer to type a few lines of code and avoid problems rather than using short cuts.

bonkey

kayos wrote: »

It’s an Oracle/MySQL thing and is not ANSI compliant (afaik) so if you don’t use either of those RDBMS you will not have seen it.

I'd have said its entirely ANSI compliant. It was also supported in DB2, MSSQL and Sybase long before in Oracle. Oracle was, in fact, the last major vendor to support "ANSI JOIN" syntax.

In the ANSI definition for JOIN, the join type (INNER, OUTER, etc.) is optional. When not supplied, INNER is assumed. While it is fairly conventional to always explicitly define it, "a JOIN b" is perfectly valid and means the same as "a INNER JOIN b".

ANSI also specifies two ways of defining the JOIN columns. One is the (more traditional) ON statement:

FROM  a 
JOIN  b
  ON  a.someColumn = b.someColumn
  AND a.someOtherColumn = b.someOtherColumn

The other, less traditional (and perhaps less widely supported) is what the example supplied used...the USING keyword. With USING, the specified column-name(s) must exist in both tables of the join. So the example above can be rewritten as:

FROM    a 
JOIN    b
  USING ( someColumn
        , someOtherColumn 
        )

How jackdaw thought it should be written (multiple, comma-seperated tables in the FROM clause, with the column-joining in the WHERE clause is also ANSI-compliant.

There tend to be religious wars over whether one should use one syntax or the other, as well as which is more efficient. Mostly, it boils down to preference.