Oracle Left outer join

Question

SELECT
    a,
    last_note_user,
    c,
    d,
    iso_src
FROM
    X
    CROSS JOIN Y
    CROSS JOIN Z
    LEFT OUTER JOIN W
        ON W.last_note_user = Z.userid
           AND W.user_ten = Y.iso_src

The above ANSI code fetch me 107 records,When I giving the same query without ANSI code it is fetching 875 records.The non ANSI query is below:

SELECT
    a,
    last_note_user,
    c,
    d,
    iso_src
FROM
    X,
    Y,
    Z,
    W
WHERE
    W.last_note_user = Z.userid(+)
    AND W.user_ten = Y.iso_src(+)

why there is difference in the two query with ANSI and without ANSI standards?? By answering the above query please help me out!!!

Answer 1

OUTER JOINS with old ANSI-syntax are ambiguous, so who knows what the query optimizer understands with this.

If the first SQL is producing the right rows, forget about the ANSI version and move on.

Answer 2

Your old-style query has the (+) symbols on the wrong side of the predicate. It should be:

SELECT
    a,
    last_note_user,
    c,
    d,
    iso_src
FROM
    X,
    Y,
    Z,
    W
WHERE
    W.last_note_user (+) = Z.userid
    AND W.user_ten (+) = Y.iso_src

But I wouldn't use the old-style syntax any more really.