INNER and OUTER JOIN

[Salivador7]

Hello Friends,

I've recently bumped into a problem when porting a small application from SQL Server to Oracle. The application uses quite a number of sql statements with INNER and OUTER JOIN syntax. While pure INNER JOINS are easy to rewrite by specifing the JOIN condition in WHERE clause, how to deal with OUTER JOINS?

For example: sql statement

SELECT * FROM A LEFT OUTER JOIN B ON A.ID = B.ID

How to implement it in Oracle? Also, I connect to Oracle using Oracle ODBC Driver - shouldn't it support ANSI SQL syntax? Why doesn't it recognize INNER and OUTER JOINS?

Thank you for your replies!

[hrishy]

Hello

Oracle does support outer joins you can sue somethin similar to this

SELECT * FROM A
Where A.ID = B.ID(+);

note the (+) is the outer join operator

regards
hrishy

[Salivador7]

Hi,
So basically there's no way for oracle to recognize ANSI SQL syntax such as LEFT OUTER JOIN? Why doesn't Oracle ODBC driver support it?
I thought all odbc drivers need to support ansi standard?

My questions might sound stupid, but I'm trying to understand this problem. It seems like writing ansi compliant sql queries will not be a cure for cross-database code problem...

[dknight]

Only snide comments:

Keeping the business logic on the middle tier does not guarantee that one can replace the database with a completely different brand and version if the the data models are identical.

DB2 uses the same outer join syntax as SQL Server. In reading the first DB2 manual, I have come across numerous differences between DB2 and Oracle.

Good luck.

[Salivador7]

Thanks, at least some databases follow standards...
But what are the reasons Oracle doesn't want to support ansi... to make our life harder?

[Heath]

I doubt that it will make you feel better, but Oracle is supporting the ANSI syntax in 9<i>i</i>. They even recommend that you use the ANSI syntax instead of the Oracle syntax (+). ;)

http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/queries2.htm#2054625

Heath

[jmodic]

Virtually all mayor DB vendors have reached *ENTRY LEVEL* compliance with SQL92 standard, but one of them have reached a higher level than this. ENTRY level is the lowest of four levels that defines compliance with standard.

All of them specify they are ANSI/ISO compliant, but they never tell you the whole story.... Oracle is no better than any of other DB vendors regarding this isue, but also no worse than the others. INNER/OUTER join syntax is just one of the fragments in the standard, there are *many* others where Oracle is "more compliant" than others...