Re: Different syntax for LEFT JOIN

ruslan00_at_yahoo.com (Ruslan Kogan) wrote in message news:<d562b81c.0411241456.c0b68c_at_posting.google.com>...
> Does anyone else have any view points on the performance differences
> between the ISO and ANSI sql standards?
>
> Is one an industry preferred standard?

Just to make things clear, today the ISO and the ANSI SQL standards are the same.

ANSI is a member of the ISO SQL Standards Committee. When an ISO SQL standard is published, ANSI makes it an ANSI standard.

The SQL standard defines joins to be written in several, equivalent ways, for example the old school (SQL-89) style:

    SELECT t1.c1, t2.col1
    FROM t1, t2
    WHERE t1.somecolumn = t2.anothercolumn

This query can also be written using the JOIN keyword. (This is what people often call "ANSI joins") This inner join syntax was introduced in SQL-92:

    SELECT t1.c1, t2.col1
    FROM t1 JOIN t2
    ON t1.somecolumn = t2.anothercolumn

Different syntax, but the same meaning. Both ways are ISO/ANSI compliant. (However some DBMS may evaluate the queries in different orders, so performance may differ.)

Besides, your original posting's

    SELECT *
    FROM TableA
    LEFT JOIN TableB ON
    TableA.id = TableB.id

is a LEFT OUTER JOIN. The LEFT keyword indicates its an outer join. The OUTER keyword is optional. The ISO/ANSI left outer join syntax is as:

table-reference-1 LEFT [OUTER] JOIN table-reference-2 etc.

Regards,
Jarl Received on Thu Nov 25 2004 - 05:22:27 CST