Outer Joins

• Akhileswar

  Newbie

  Points: 5

  More actions

  April 19, 2004 at 5:07 am

  #82560

  Hi

  till today I am working in Oracle I would like to know how to use outer join concept with *= and =* operators.

  I used both of them but could not findout the difference.

  

   

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  April 19, 2004 at 5:27 am

  #503293

  Strange deja vu

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=111847

  

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• neoteric45

  SSC Journeyman

  Points: 86

  More actions

  April 20, 2004 at 1:06 am

  #503427

  I would not use the =* *= syntax; BOL states that syntax may not be supported in a future version of SQL Server. Instead use the ANSI SQL-92 syntax LEFT OUTER JOIN etc. Note that Oracle 9i supports the ANSI SQL-92 join syntax so your queries can use the same join syntax as sql server. If you are are using Oracle 8i then you must use the Oracle =+ and += join synax ( see below)

  MS SQL Server	ORACLE 8i
  SELECT PERSON.* FROM ADDRESS LEFT OUTER JOIN     PERSON ON ADDRESS.PERSON_ID = PERSON.ID	SELECT PERSON.* FROM ADDRESS, PERSON WHERE ADDRESS.PERSON_ID = PERSON.ID (+)

  MS SQL Server	ORACLE 8i
  SELECT PERSON.* FROM ADDRESS RIGHT OUTER JOIN     PERSON ON ADDRESS.PERSON_ID = PERSON.ID	SELECT PERSON.* FROM ADDRESS, PERSON WHERE ADDRESS.PERSON_ID (+) = PERSON.ID