MS SQL Server - Sql 2000 Query Question

Author

Sql 2000 Query Question

Izzy

2006-02-10, 11:23 am

I'm wondering why these two queries yeild different results.

This query returns no records, there is one record in the Company table

SELECT C.CMP_ID, IH.IVH_INVOICENUMBER
FROM COMPANY C
LEFT OUTER JOIN INVOICEHEADER IH ON C.CMP_ID = IH.IVH_BILLTO
WHERE (IH.IVH_DELIVERYDATE BETWEEN '2005-01-01' AND '2006-02-10')
AND (C.CMP_ID = 'BASMOUPA')

This query will return one record.

SELECT C.CMP_ID, IH.IVH_INVOICENUMBER
FROM COMPANY C
LEFT OUTER JOIN (SELECT IVH_INV
OICENUMBER, IVH_BILLTO
FROM INVOICEHEADER
WHERE IVH_DELIVERYDA
TE BETWEEN '2005-01-01' AND '2006-02-10') IH ON
C.CMP_ID = IH.IVH_BILLTO
WHERE (C.CMP_ID = 'BASMOUPA')

My question is, in the first query I specify a LEFT OUTER JOIN on
COMPANY to INVOICEHEADER, by doing this I'm saying give me all records
from COMPANY and only show records from INVOICEHEADER where the joined
fields are equal. There is 1 record in the COMPANY table for that
company. Why is it being filtered out?

It's only when create a sub query for INVOICEHEADER and put the
IVH_DELIVERYDATE filter in that query does it return results. I think
the query should return the same result in both situations.

Is this a bug? Or am I not understanding the way queries work?

Thanks,
Izzy

Razvan Socol

2006-02-10, 1:23 pm

Hello, Izzy

It's not a bug, it's the expected behaviour.

In the first query, the condition involving IH.IVH_DELIVERYDATE in the
WHERE clause is evaluated after the LEFT JOIN and filters only the rows
that are not null and meet the criteria (effectively making the join an
INNER JOIN).

You can also use this query (which is equivalent to your second query):
SELECT C.CMP_ID, IH.IVH_INVOICENUMBER
FROM COMPANY C
LEFT OUTER JOIN INVOICEHEADER IH
ON C.CMP_ID = IH.IVH_BILLTO
AND IVH_DELIVERYDATE BETWEEN '2005-01-01' AND '2006-02-10'
WHERE C.CMP_ID = 'BASMOUPA'

Razvan

Izzy

2006-02-10, 1:23 pm

I see. Thanks for sharing the knowledge.

Sponsored Links