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 you're more "standard" but it's 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 didn't hit. It's hard to explain, but it's a little above your original question anyway :)

 

-ner

[JABE]

But it's definitely true that Oracle (at least w/ 8i, the last version I've worked with) doesn't 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 I've gotten the hang of the ANSI standard, well... jury's 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 I'm an anal "keep the code clean looking" kinda guy).

 

-Ner