CODEWITHSUNDEEP
sql-servert-sqlleft-joininner-join
ttaaoossuu
ttaaoossuu

Reputation: 7884

How to INNER JOIN on non-empty values BUT AT THE SAME TIME LEFT JOIN on empty values?

It seems that I need a MAGIC JOIN that should work as INNER JOIN on non-empty values but at the same time work as LEFT JOIN on empty values. By "empty" I mean a non-null zero-length char value, like this: ''.

I have table Customers:

CustomerCode    Customer_AddressCode
CC01            AC01
CC02            somebullcrap
CC03
CC04            AC02
CC05

and Addresses:

AddressCode    AddressValue
AC01           City 01
AC02           City 02
AC03           City 03
AC04           City 04
AC05           City 05

The desired output is like this:

CustomerCode  AddressValue
CC01          City 01
CC03          NULL
CC04          City 04
CC05          NULL

so it would omit address with code somebullcrap that does not exist in Addresses table.

The best I could think of is this:

SELECT c.CustomerCode, a.AddressValue
FROM Customers c
LEFT JOIN Addresses a ON a.AddressCode = c.Customer_AddressCode
WHERE c.Customer_AddressCode = ''
OR c.Customer_AddressCode IN
(
    SELECT AddressCode
    FROM Address
)

Is there any more elegant solution to this?

And yes, I do know about nullable foreign keys. I'm just asking about solution without altering tables / making temp tables / modifying existing table data.

Upvotes: 2

Views: 6680

Answers (4)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

EricZ's is probably the most elegant approach that will return the correct results, but just as an exercise in alternatives:

SELECT  c.CustomerCode
      , a.AddressValue FROM    Customers c
        JOIN ( SELECT   AddressCode
                      , AddressValue
               FROM     Addresses
               UNION ALL
               SELECT   ''
                      , NULL
             ) a ON a.AddressCode = c.Customer_AddressCode

Upvotes: 1

xum59
xum59

Reputation: 851

If your problem is to handle the empty strings ( '' ), you can use a simple

CASE Customer_AddressCode='' THEN NULL ELSE Customer_AddressCode END

to cast your empty strings to a real NULL value.
Then, a simple LEFT JOIN like this :

SELECT c.CustomerCode, a.AddressValue
FROM 
(SELECT 
    CustomerCode, 
    CASE Customer_AddressCode='' THEN NULL ELSE Customer_AddressCode END AdCode
FROM Customers) c
LEFT JOIN Addresses a ON (a.AddressCode = c.AdCode OR c.AdCode is null)

The c.AdCode is null condition will allow empty values in the result while filtering the "somebullcrap" you don't seem to want.

Upvotes: 1

Paddy
Paddy

Reputation: 33857

What you want is just a left join, given that there is no address with an empty string as a code in your addresses table. I think that this would give you the desired results:

SELECT c.CustomerCode, a.AddressValue
FROM Customers c
LEFT JOIN Addresses a ON a.AddressCode = c.Customer_AddressCode

Why is there no CHECK on the foreign key between customers and addresses?

Upvotes: 0

EricZ
EricZ

Reputation: 6205

Why not

SELECT c.CustomerCode, a.AddressValue
FROM Customers c
LEFT JOIN Addresses a 
   ON a.AddressCode = c.Customer_AddressCode
WHERE c.Customer_AddressCode = ''
   OR a.AddressValue IS NOT NULL

Upvotes: 4

Related Questions