sql ansi-92 Compliant Question

Question

Are the following select statements SQL92 compliant?

SELECT table1.id, table2.id,* FROM table1, table2 WHERE table1.id = table2.id

SELECT table1.Num, table2.id,* FROM table1, table2 WHERE table1.Num = table2.id

Answer 1

I might be wrong, but my understanding is that the SQL92 convention is to join tables using the JOIN statement (e.g. FROM table1 INNER JOIN table2).

Answer 2

Unfortunately, I believe they are, but that join syntax is more difficult to read and maintain.

I know that with MSSQL, there is no perfomance difference between either of these two join methods, but which one is easier to understand?

SELECT table1.id, table2.id,* 
FROM table1, table2 
WHERE table1.id = table2.id


SELECT 
 table1.id, 
 table2.id,
 * 
FROM table1
   INNER JOIN table2 
     ON table1.id = table2.id

Answer 3

Following on from StingyJack...

SELECT 
    table1.id, 
    table2.id,
    * 
FROM
    table1
    INNER JOIN 
    table2 ON table1.id = table2.id
WHERE
    table1.column = 'bob'

SELECT table1.id, table2.id,* FROM table1, table2 WHERE table1.id = table2.id and table1.column = 'bob'

Where's the JOIN? Where's the filter?

JOIN also forces some discipline and basic checking: easier to avoid cross join or partial cross joins

Answer 4

Yes, the queries you show use SQL92 compliant syntax. My copy of "Understanding the New SQL: A Complete Guide" by Jim Melton & Alan R. Simon confirms it.

SQL92 still supports joins using the comma syntax, for backward compatibility with SQL89. As far as I know, all SQL implementations support both comma syntax and JOIN syntax joins.

In most cases, the SQL implementation knows how to optimize them so that they are identical in semantics (that is, they produce the same result) and performance.