Jason Dodson wrote:

> Hey all,
>
> I was presented with a strange... situation if you will. I will try to
> explain what is going on as much as I can, without going too deep (look
> for *** to identify differences):
>
> Query1:
>
> Select NAbills.Acct_Ref, CustAcct.Acct_ID, NABills.BillTotal,
> NABills.CustBillNo
> From NABills Inner join CustAcct on NABills.Acct_Ref = CustAcct.Acct_ID
> Where CustAcct.CustID='VE' and NABills.CustBillNo Is Null
> Order by NABills.VendorInfo;
>
> Result:
>
> ACCT_REF ACCT_ID BILLTOTAL CUSTBILLNO
> ======== ======= ========= ==========
> 18271 18271 330 <null>
> 13778 13778 46 <null>
> 100396 100396 35 <null>
> 13842 13842 101 <null>
> 18272 18272 175 <null>
> 14383 14383 35 <null>
> 16135 16135 35 <null>
> 14996 14996 42 <null>
> 20254 20254 35 <null>
> 16338 16338 50 <null>
> 20721 20721 35 <null>
> 100398 100398 280 <null>
> 18272 18272 69 <null>
> 16138 16138 234 <null>
> 4741 4741 13 <null>
> 13752 13752 13 <null>
> 16107 16107 187 <null>
> 18270 18270 41 <null>
> 13753 13753 1143 <null>
> 100396 100396 650 <null>
>
> Plan:
>
> PLAN SORT (JOIN (CUSTACCT INDEX (CUSTACCT_CUSTID),NABILLS INDEX
> (NACUSTBILLNOX,FK_NABILLS_ACCTREF)))
>
> ______________________________________________________________________________________________________________
>
> Query2:
>
> Select NAbills.Acct_Ref, CustAcct.Acct_ID, NABills.BillTotal,
> NABills.CustBillNo
> From NABills ***Left*** join CustAcct on NABills.Acct_Ref =
> CustAcct.Acct_ID
> Where CustAcct.CustID='VE' and NABills.CustBillNo Is Null
> Order by NABills.VendorInfo;
>
> Result:
>
> ACCT_REF ACCT_ID BILLTOTAL CUSTBILLNO
> ======== ======= ========= ==========
> 18271 18271 330 <null>
> 13778 13778 46 <null>
> 100396 100396 35 <null>
> 13842 13842 101 <null>
> 18272 18272 175 <null>
> 14383 14383 35 <null>
> 16135 16135 35 <null>
> 14996 14996 42 <null>
> 20254 20254 35 <null>
> 16338 16338 50 <null>
> 20721 20721 35 <null>
> 100398 100398 280 <null> *********
> 18272 18272 69 <null>
> 16138 16138 234 <null>
> 4741 4741 13 <null>
> 13752 13752 13 <null>
> 16107 16107 187 <null>
> 18270 18270 41 <null>
> 13753 13753 1143 <null>
> 100396 100396 650 <null>
>
> Plan:
>
> PLAN SORT (JOIN (NABILLS INDEX (NACUSTBILLNOX),CUSTACCT INDEX
> (RDB$PRIMARY7)))
>
> _______________________________________________________________________________
>
> Now notice, I simply changed the first query from a inner join to a left
> join. The recordsets should be identical, but strangely enough, a NEW
> record appears with a left join. The peculiar thing is, looking at the
> Acct_Ref/Acct_ID fields, this record SHOULD have shown up in the inner
> join as well. What could possibly be going on here?
>

The marked record in the second result set is shown in the result set of
the first query as well. So, where is the difference?



--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2 (NEW!)
Upscene Productions
http://www.upscene.com