1
    SELECT a.org, 
           a.id, 
           a.Name, 
           b.ordNum 
      FROM customers A, 
           orders B 
     WHERE a.org  = 'JJJ' 
       AND a.org  = b.org (+)
       AND b.addr_type (+) = 'ST' -- <<<<<<<<<<<<<<<<< why do i need to add (+) here
       AND a.cust_id = b.cust_id (+)
  ORDER BY 2

I have a table with a list of customers (A) and a table called orders (B) that have orders the customers may have placed . The query i have above is supposed to give me the names of all customers and the order number IF there is an order linked to that customer.

My question is.. why do i need to add the (+) after b.addr_type to get all the customers even if they have not placed an order.

Dmitry Bychenko
  • 180,369
  • 20
  • 160
  • 215
psj01
  • 3,075
  • 6
  • 32
  • 63
  • 4
    Tip of today: Switch to modern, explicit join syntax! – jarlh Jun 02 '16 at 13:50
  • Possible duplicate of [LEFT JOIN and LEFT OUTER JOIN in SQL Server](http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server) – SomeJavaGuy Jun 02 '16 at 13:50

4 Answers4

4

That is the old-style JOIN syntax, wherein (+) denotes an OUTER JOIN. This means that every row in the left table will be returned whether it has a match on the right table or not. To get only the customers with order, use an INNER JOIN. Additionally, you should use explicit JOIN and not the old-style syntax:

SELECT
    c.ORG, c.ID, c.NAME, o.ordNum 
FROM customers c -- Use meaningful aliases to improve readability
LEFT JOIN orders o
    ON c.org = o.org
    AND c.cust_id = o.cust_id
    AND o.addr_type = 'ST'
WHERE
    c.org = 'JJJ'
ORDER BY c.ID
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • 3
    When he says "old-style", it's *very* old. The "new-style" syntax is from 1992, that's older then some of my colleagues. No-one should ever be using implicit joins any more. They're harder to read, maintain, etc, and in some cases they actually don't work correctly and have been deprecated. – MatBailie Jun 02 '16 at 13:57
3

The (+) is an "outer join" in old style syntax. This means EVERY row on the left side of the join is returned with a "null" in the right hand side table's colums if no match is made.

An INNER join (regular equals in old style SQL) would not return a record if there was no match on the right side.

Modern syntax is

SELECT A.ORG, A.ID, A.NAME, b.ordNum FROM 
customers A
LEFT OUTER JOIN customers b on a.id = b.id 
              AND a.cust_id = b.cust_id 
              AND b.addr_type = 'ST'
WHERE a.org = 'JJJ'
ORDER BY 2

The "OUTER" part is optional, and indeed implicit if you're using the word "LEFT". Your other options are RIGHT and FULL for outer joins.

Why use this new syntax? Because it's ANSI SQL compliant, the (+) is deprecated and won't port over to some modern RDBMS implementations. Plus, as per the comment on this post, it's as ugly as sin and hard to maintain.

Jeff Watkins
  • 6,343
  • 16
  • 19
  • 1
    It's not Oracle specific, other RDBMS do support it. That said, there are cases where it doesn't work correctly, and so it has been deprecated, and it's a nightmare to write, read, maintain, debug, etc. – MatBailie Jun 02 '16 at 13:59
2

In order to avoid such questions, switch to LEFT JOIN syntax which is more readable

    SELECT a.org, 
           a.id, 
           a.Name, 
           b.ordNum 
      FROM customers a LEFT JOIN 
           orders b ON (a.org = b.org) 
                   AND (b.addr_type = 'ST') 
                   AND (a.cust_id = b.cust_id)
     WHERE a.org = 'JJJ' 
  ORDER BY a.id -- better put it direct, not field's index
Dmitry Bychenko
  • 180,369
  • 20
  • 160
  • 215
2

The (+) syntax tells Oracle to execute a left join instead of an inner join.

The result is a list of records with all valorized columns from customers and some empty columns from orders table.

If the columns from orders table are NULL, the where condition b.addr_type = 'ST' will be always FALSE for these records, so you will not obtain the desired result.

Instead if you write b.addr_type(+) = 'ST' you'll get all columns matching the condition plus the columns with NULL value because of the left join, that is what you want to get.

Carlo
  • 1,539
  • 1
  • 11
  • 25