ANSI x T-SQL

[Cassio]

My workmate told me that INNER JOINS, LEFT JOINS, etc, work only on microsoft databases and if I want to make SQL code that will work for all databases Ill have to use WHERE clauses instead.
Is it true?!

Thanks!

 

[Nerseus]

The exact opposite is true. ANSI standard SQL looks like:

SELECT *
FROM Table1 
INNER JOIN Table2 ON Table1.Col1 = Table2.Col1
LEFT OUTER JOIN Table3 ON Table1.Col1 = Table3.Col1

The T-SQL version (Microsoft SQL Server specific):

SELECT *
FROM Table1, Table2, Table3
WHERE Table1.Col1 = Table2.Col1
AND Table1.Col1 = Table3.Col1

If you use ANSI standard youre more "standard" but its a bit harder to read. You can do a little bit more with it, since on OUTER joins you can put more than one expression in the ON portion (...ON Table1.Col1=Table2.Col1 AND Table1.Col2 = 5). If you did the same thing in the WHERE clause (still using ANSI sytax for the join), the OUTER might not return rows if the WHERE clause didnt hit. Its hard to explain, but its a little above your original question anyway

-ner

 

[JABE]

But its definitely true that Oracle (at least w/ 8i, the last version Ive worked with) doesnt support ANSI-style JOINs. Weird, huh?

 

[Cassio]

I guess the INNER JOIN syntax is not supported by Oracle until Oracle 9i.

 

[Nerseus]

Weird indeed

I used to use the T-SQL syntax in SQL Server (in version 6, and a bit in version 7) and loved it. Now that Ive gotten the hang of the ANSI standard, well... jurys still out. I still miss the convenience of the T-SQL code (looks cleaner to me), but the ANSI version is nice (just harder to line things up and Im an anal "keep the code clean looking" kinda guy).

-Ner