6

Possible Duplicates:
Oracle “(+)” Operator
Oracle (Old?) Joins - A tool/script for conversion?

I have been somewhat spoiled by using Oracle for years. Now I am using mysql and cannot find a non-ansi version/shorthand version of outer joins in MySQL.

In oracle I could do this

select a.country acountry,
        a.stateProvince aStateProvince,
        b.countryName bcountry,
        b.name bstateProvince
  from User a,
          stateprovince b
  where a.country*=b.countryName **(+)**
          and a.stateProvince*=b.name **(+)**

to get an outer join. Can mysql do something similar?

Community
  • 1
  • 1
benstpierre
  • 32,833
  • 51
  • 177
  • 288
  • 5
    I am curious. Why would you want to use non-ansi syntax? Ansi syntax is much easier to understand, especially in complex queries. – Gareth Jul 12 '11 at 00:42
  • You can do one join per line with the oracle style join. I cannot remember where the (+) goes though. – benstpierre Jul 12 '11 at 00:57
  • 1
    Actually, what you have there is not an outer join... – Bohemian Jul 12 '11 at 01:13
  • 1
    @Bohemian: it IS a left outer join in Oracle 8. – ypercubeᵀᴹ Jul 12 '11 at 01:17
  • 1
    possible duplicate of [Oracle "(+)" Operator](http://stackoverflow.com/questions/4020786/oracle-operator). Also: http://stackoverflow.com/questions/2425960/oracle-old-joins-a-tool-script-for-conversion – OMG Ponies Jul 12 '11 at 02:04
  • 2
    @ypercube, it's still a left outer join in Oracle 11g, too, if you must use it ;-) – DCookie Jul 12 '11 at 03:19
  • `spoiled by using Oracle`: I can only imagine the horrible things that must have occurred to corrupt a developer so deeply as to make that statement. – Charles Burns Oct 23 '13 at 23:49

1 Answers1

3

Simpler than this:

select a.country acountry,
        a.stateProvince aStateProvince,
        b.countryName bcountry,
        b.name bstateProvince
  from User a
        left join
          stateprovince b
    on  a.country = b.countryName 
          and a.stateProvince = b.name

No.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • 1
    The `(+)` notation is definitely more complex to understand. The SQL-92 join notations (that's 20 year's old next year!) takes a bit of getting used to at first, but is much better once mastered, which is usually only a matter of a few days work. – Jonathan Leffler Jul 12 '11 at 04:58