Left Join that always includes null records

I'm using Oracle 11gR2 and I am trying to write a query that returns address data from two tables, CUSTOMERS and LOCATIONS. A given customer may (or may not) have different locations, each with their own address.

I would like to return the address for every customer, and all their locations. For example, if the tables contained data like:

CUSTOMERS
CUSTOMER_ID    ADDRESS
    1         "New York"
    2         "California"


LOCATIONS
CUSTOMER_ID   LOCATION_ID    ADDRESS
    1             1        "New Jersey"

Then I want the results to look like:

CUSTOMER_ID    LOCATION_ID    ADDRESS
   1                         "New York"
   1                1       "New Jersey"
   2                      开发者_如何学Go  "California"

My first thought was something like this:

SELECT 
 CUSTOMERS.CUSTOMER_ID,
 LOCATIONS.LOCATION_ID,
 NVL(LOCATIONS.ADDRESS,CUSTOMERS.ADDRESS) ADDRESS
FROM
CUSTOMERS
 LEFT JOIN
LOCATIONS ON (CUSTOMERS.CUSTOMER_ID=LOCATIONS.CUSTOMER_ID)

The problem with that is that when a customer does have locations, it does not return a row with null values for location data, so I don't get a row with the address in the CUSTOMERS table. It gives me something like this:

CUSTOMER_ID    LOCATION_ID    ADDRESS
   1                1       "New Jersey"
   2                        "California"

It's missing the New York address for customer 1. I tried this...

SELECT 
 CUSTOMERS.CUSTOMER_ID,
 LOCATIONS.LOCATION_ID,
 NVL(LOCATIONS.ADDRESS,CUSTOMERS.ADDRESS) ADDRESS
FROM
CUSTOMERS
 LEFT JOIN
LOCATIONS ON (CUSTOMERS.CUSTOMER_ID=LOCATIONS.CUSTOMER_ID OR LOCATIONS.CUSTOMER_ID IS NULL)

But it gave me the same results as the first query. Is there a way to return a null record for the second table even when there is a match on the join condition?

You don't need a join here at all:

SELECT  customer_id, NULL AS location_id, address
FROM    customers
UNION ALL
SELECT  customer_id, location_id, address
FROM    locations

You can try a full outer join. For example:

SELECT    
CUSTOMERS.CUSTOMER_ID,   
LOCATIONS.LOCATION_ID,   
NVL(LOCATIONS.ADDRESS,CUSTOMERS.ADDRESS) ADDRESS             
FROM  CUSTOMERS   
  FULL OUTER JOIN  LOCATIONS ON (CUSTOMERS.CUSTOMER_ID=LOCATIONS.CUSTOMER_ID)

If you want to join the two tables even when there is a non match, you will need to use IS NULL on your joined columns.

For example.

Table 1:
CustomerID
CustomerName

.

Table 2:
CustomerID
CustomerEmail

.

Select,
CustomerID,
CustomerName,
ISNULL (CustomerEmail, NULL) AS CustomerEmail


FROM table1

LEFT JOIN table2
ON table1.CustomerID = table2.CustomerID

This wil bring back results with NULL