Join return empty but subquery doesn't

April 24, 2024

I have 2 tables:

Invoice

CustomerInvoiceID - PK
RecordLocator - Text
createdDate - date

SimplifiedInvoice

CustomerInvoiceID - PK / FK Invoices
CountryCode - text
VatPercentage - number
SubtotalAmount - number
TotalAmount - number

If I run the following query:

select  
    si.CountryCode, 
    si.RecordLocator, 
    si.CustomerInvoiceID, 
    i.CreatedDate,  
    i.VatPercentage, 
    si.SubTotalAmount, 
    si.TotalAmount
from
    SimpliedInvoice si , invoice i 
where 
    si.CustomerInvoiceID = i.CustomerInvoiceID
    and si.CountryCode = 'GR'

My result is an empty dataset.

But if I run this query:

select * 
from SimpliedInvoice
where CustomerInvoiceID in (select CustomerInvoiceID 
                            from Invoice 
                            where CountryCode = 'GR')

I get plenty of data.

I am not able to see why, any idea?

>Solution :

Well, in your first query, you check the SimplifiedInvoice table (alias si) for the CountryCode = ‘GR’ –

where 
    ....
    and si.CountryCode = 'GR'
        *********************

But in your second query, you’re testing the Invoice table in the subquery for that same country code:

where CustomerInvoiceID in (select CustomerInvoiceID 
                            from Invoice 
                                 *******  
                            where CountryCode = 'GR')
                                  ******************

So change your first query to:

select  
    si.CountryCode, 
    si.RecordLocator, 
    si.CustomerInvoiceID, 
    i.CreatedDate,  
    i.VatPercentage, 
    si.SubTotalAmount, 
    si.TotalAmount
from
    SimpliedInvoice si 
inner join
    invoice i on si.CustomerInvoiceID = i.CustomerInvoiceID
where 
    i.CountryCode = 'GR'

and you should get the same set of data returned