Left Join that always includes null records

Question

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                        "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?

Answer 1

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