• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 811
  • Last Modified:

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

0
ShamD
Asked:
ShamD
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
awking00Commented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now