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

Answers


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


Need Your Help

how to disable a submit button if text_field is empty in ruby on rails?

javascript jquery ruby-on-rails

I am new to ROR. I just want to know is it possible to disable a submit tag button if the text_field is empty.??

Remove HTML tags + content

html ruby nokogiri

OK, as simple as it sounds I still can't figure out how to do it (and imagine I've even decided to change language for that).

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.