psj01 psj01 - 2 years ago 82
SQL Question

Joining two tables, customers and orders to get list of all customers and the order number IF they have an order

FROM customers A,
orders B
AND = (+)
AND b.addr_type (+) = 'ST' -- <<<<<<<<<<<<<<<<< why do i need to add (+) here
AND a.cust_id = b.cust_id (+)

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download