arun55 (10/8/2009)
vliet (10/8/2009)
Since the 'or' operator has lower precedence as the 'and' operator, when you ommit the parentheses the join condition means something completely different. Without parentheses the predicate behind the 'or' is evaluated separately, which leaves out the 'a.id = b.id' part completely, thus returning all records from 'a' for each and every record where 'b.date2' is empty. This might blow up the size of the result set a lot when a has many rows and b has many rows without a date in this column. Never ommit any parenthenses whenever you use both 'and' and 'or' in a predicate. You can never use too many parenthenses in these cases, computers don't know what you mean, they just do what you tell them to, but that's generaly not what you want.I've seen predicates with both the 'and' and 'or' operator being the cause of 'human' errors in SQL so often, it's almost worth an article.
Thanks for the information. I understood what you have explained.
But in above case, b.date field has no nulls and I am getting same results.
I think it was a precautionary statement. I will check the requirement and try to add parentheses.
If Table B can contain a start but no end date (assumption given inferred use) this is important.
Your 40 minute query is returning information based on two specific possibilities:
1) a.id = b.id and a.date1 between b.date1 and b.date2
2) b.date2 is null
This would return the same data as the following more complex and less efficient query:
select a.*,b.*
from table a
left join table b
on a.id = b.id
and a.date1 between b.date1 and b.date2
union
select a.*,b.*
from table a, table b
where b.date2 is null
Looking at the second select this is a cross join. After creating this cross join it then goes through each record Count(A)*Count(B) and select those where date2 is null (0 per your statements). So this entire cross join would be generated then ignored. You are paying the cost to create this cross join and then ignore it.
Your results are the same given your data set, but this doesn't mean your queries are equal. They could change after your next transaction.
You might consider a third query depending on your expectation.
This requires that a.date1 occur after b.date1 but b.date2 may be null
select a.*,b.*
from table a
left join table b
on a.id = b.id and a.date1 >= b.date1 and a.date1 <= isnull(b.date2,getdate())
This query allows a.date1 to predate b.date1. This may not be desired.
Arun55 modified.
select a.*,b.*
from table a
left join table b
on a.id = b.id
and (a.date1 between b.date1 and b.date2 or b.date2 is null)