To be ANSI or Not to be

What’s the difference between the following two queries?

FROM table1 t1, table2 t2
WHERE t1.col1 = t2.col2


     table2 t2 on t1.col1 = t2.col

Well, nothing really. At least in the most popular databases.

Except that the second query is an ANSI standard and is pretty much guaranteed to work with any RDBMS that is worth its salt. The First one is the query language supported by some earlier versions of proprietary databases like Oracle and SQL server, and they continue to support them for legacy purposes, but there is no real need to write new code in that fashion.

What’s more – the ANSI syntax is much cleaner, much more precise in stating which join to use and what are the columns to be used in that join and just so much easier to write and read. It’s much harder to miss joining conditions and end up with Cartesian joins. It is much easier to modify, if you want to add or remove tables from the select query. All-in-all, there is no reason really why all new queries should not be written in the ANSI Standard format, so if you ever asked yourself that question, hope you got your answer.

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    Markdown is turned off in code blocks:
     [This is not a link](

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>