psj01 psj01 - 6 months ago 15
SQL Question

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

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.

Answer

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.