Mysql's equivalent to Oracle's (+) for null values

[JBBHF]

Hi i'm working on a web project, and i would like to make my oracle
query work in mysql.

select match.numero "nummatch",
to_char(match.d atematch, 'yyyy-MM-dd') "datematch" ,
p1.numjoueur "j1",
p2.numjoueur "j2",
po1.p1 "m1p1",
po1.p2 "m1p2",
po2.p1 "m2p1",
po2.p2 "m2p2",
po3.p1 "m3p1",
po3.p2 "m3p2"
from participant p1,
participant p2,
match,
point po1,
point po2,
point po3
where p1.nummatch(+) = match.numero and
p2.nummatch(+) = match.numero and
po1.nummatch(+) = match.numero and
po2.nummatch(+) = match.numero and
po3.nummatch(+) = match.numero and
match.type = 'D' and
p1.position(+) = 1 and
p2.position(+) = 2 and
po1.manche(+) = 1 and
po2.manche(+) = 2 and
po3.manche(+) = 3 and
p1.numjoueur = 1;


i can't seem to find an equivalent to Oracle's (+) for null values in
MYSQL.

Any help is appreciated, thanks
-John

[Bill Karwin]

Re: Mysql's equivalent to Oracle's (+) for null values

JBBHF wrote:[color=blue]
> Hi i'm working on a web project, and i would like to make my oracle
> query work in mysql.[/color]

I don't have a lot of experience with Oracle, but my understanding is
that the (+) syntax is Oracle's pre-SQL92 solution to implement what
eventually became standardized with the syntax OUTER JOIN.

MySQL pretty much uses the ANSI/ISO SQL92 standard syntax for outer
joins. You could probably benefit from a SQL book on writing queries
including outer joins. No need for it to be specific to MySQL.
[color=blue]
> select match.numero "nummatch",[/color]
.. . .[color=blue]
> from participant p1,
> participant p2,
> match,
> point po1,
> point po2,
> point po3
> where p1.nummatch(+) = match.numero and
> p2.nummatch(+) = match.numero and
> po1.nummatch(+) = match.numero and
> po2.nummatch(+) = match.numero and
> po3.nummatch(+) = match.numero and
> match.type = 'D' and
> p1.position(+) = 1 and
> p2.position(+) = 2 and
> po1.manche(+) = 1 and
> po2.manche(+) = 2 and
> po3.manche(+) = 3 and
> p1.numjoueur = 1;[/color]

I think the following query would be equivalent:

SELECT match.numero AS nummatch, ...etc...
FROM match
LEFT OUTER JOIN participant p1 ON (match.numero = p1.nummatch AND
p1.position = 1)
LEFT OUTER JOIN participant p2 ON (match.numero = p2.nummatch AND
p2.position = 2)
LEFT OUTER JOIN point po1 ON (match.numero = po1.nummatch AND
po1.manche = 1)
LEFT OUTER JOIN point po2 ON (match.numero = po2.nummatch AND
po2.manche = 2)
LEFT OUTER JOIN point po3 ON (match.numero = po3.nummatch AND
po3.manche = 3)
WHERE match.type = 'D' AND p1.numjoueur = 1

Regards,
Bill K.