Inner Join returns Emty Resultset when One table is empty

I have a problem with some SQL.
I have 2 tables. Person and Customer.
Both Tables have a CUSTOMER_ ID field.

I want to select every CUSTOMER_NAME from the Customer Table whose ID is NOT in the Person Table.
So i think I should be doing a NOT-Equal Join.

This Code snippet works fine. But when the Person Table is Empty (Hence Nothing is returned from the Inner Select), Nothing gets returned for my entire select.
If at lease one record is present in Person, the SQL works fine.

What am I missing?
select CUSTOMER_NAME 
from CUSTOMER inner join PERSON on CUSTOMER.CUSTOMER_ID NOT IN (select CUSTOMER_ID from PERSON) 
where (CUSTOMER.DATE_OF_BIRTH <= TO_DATE('28/11/2008', 'dd/MM/yyyy'))

Open in new window

ShamDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UnifiedISCommented:
Make your inner join and outer join
inner join requires a match
0
i2mentalCommented:
select CUSTOMER_NAME
from CUSTOMER
left  join PERSON on CUSTOMER.CUSTOMER_ID = PERSON.CUSTOMER_ID
where person.customer_id is null
and (CUSTOMER.DATE_OF_BIRTH <= TO_DATE('28/11/2008', 'dd/MM/yyyy'))
0
dro_lawCommented:
You don't need a join at all if I'm understanding you. All you care about is whether a customerid record doesn't exist in the person table correct? if that's the case, then a simple NOT IN statement should work just fine.

select CUSTOMER_NAME
from
CUSTOMER  
where
CUSTOMER.CUSTOMER_ID NOT IN (select CUSTOMER_ID from PERSON)
and
(CUSTOMER.DATE_OF_BIRTH <= TO_DATE('28/11/2008', 'dd/MM/yyyy'))


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
See attached.
query.txt
0
ShamDAuthor Commented:
Thank you for your solution, Can you tell me if using a Select within a Select would be bad or slow for performance ? I am using this SQL in a very large DB to retrieve a sub-section of data
0
dro_lawCommented:
Well, it will be slower perfomrance that a simple join, but if you have the customer_id field properly indexed, it shouldn't be too bad (unless you have tens of millions of records.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.