Oracle outer join(+) syntax

Question

I'm relatively new to SQL and a complete beginner with Oracle, and having difficulty understanding the (+) syntax. Consider the following query:

SELECT *
FROM   CustomerShip,
    (SELECT DISTINCT b.ShipSeq AS shipSeq
     FROM   Orders a,
            CustomerShip b
     WHERE  a.OrderId IN (SELECT OrderId
                          FROM   Orders
                          WHERE  CustomerId = @CustomerId
                          AND    OrderType <> 'A')
     AND    b.CustomerId = @CustomerId
     AND    b.ShipSeq = a.CustShip
     AND    OrderStatus <> 'C'
     GROUP BY b.ShipSeq) i
WHERE  CustomerId = @CustomerId
AND    (Address NOT LIKE '%RETAIL%STORE%')
AND    ShipSeq = i.ShipSeq(+)
ORDER BY ShipTo DESC, OrderDate DESC;

So I gather the (+) in Oracle is an outer join, but I'm confused what is is joining on? The result of the inner query i. I need to rewrite this query for MSSQL and not sure what that would look like?

"I'm relatively new to SQL" . . . Well, then, don't learn obsolete, out-dated syntax. Even Oracle recommends using explicit outer joins. — Gordon Linoff, Jun 13 '17 at 11:04
I'm not. I'm reworking a query that was written a while ago. — PixelPaul, Jun 13 '17 at 11:11

score 1 · Accepted Answer · answered Jun 13 '17 at 11:01

1

The columns that are joined on are just before the plus sign.

 ... FROM CustomerShip s LEFT OUTER JOIN (....) i ON s.ShipSeq = i.ShipSeq

answered Jun 13 '17 at 11:01

SAS

3,943
2
27
48

Thank you, that was what I was looking for. – PixelPaul Jun 13 '17 at 11:22

Oracle outer join(+) syntax

1 Answers1