July 24, 2009 at 10:20 am
I have coded two sql select stmts that I though would be equivalent but have learned through observation that they are not, the issue now is that I cannot figure out why they would perform differently.
I normally use the % operator with the LIKE keyword but I was trying to simplify a where clause that had many values in the code list and since both of these queries ran without error I thought it might be ok. But after examining the results I have determined that they return different results and are not equivalent.
Query A: Returns 2,488,200
select count(*)
from [Table]
where [column_name] not in('%10BUC%','%UNCLGAD%')
Query B: returns 2,273,182
select count(*)
from [Table]
where
[column_name] not like '%10BUC%' and
[column_name] not like '%UNCLGAD%'
After performing additional queries I have figured out that Query A returns more results because it includes rows with values of 10BUC1Y10,10BUC1Y1L,10BUC1Y2L, etc.
Query B correctly filters out these result rows but for some reason they are being included in the Query A results.
Does anybody have any insight on why this would happen ?
July 24, 2009 at 10:26 am
William Plourde (7/24/2009)
I have coded two sql select stmts that I though would be equivalent but have learned through observation that they are not, the issue now is that I cannot figure out why they would perform differently.I normally use the % operator with the LIKE keyword but I was trying to simplify a where clause that had many values in the code list and since both of these queries ran without error I thought it might be ok. But after examining the results I have determined that they return different results and are not equivalent.
Query A: Returns 2,488,200
select count(*)
from [Table]
where [column_name] not in('%10BUC%','%UNCLGAD%')
Query B: returns 2,273,182
select count(*)
from [Table]
where
[column_name] not like '%10BUC%' and
[column_name] not like '%UNCLGAD%'
After performing additional queries I have figured out that Query A returns more results because it includes rows with values of 10BUC1Y10,10BUC1Y1L,10BUC1Y2L, etc.
Query B correctly filters out these result rows but for some reason they are being included in the Query A results.
Does anybody have any insight on why this would happen ?
In Query B change the AND to an OR.
July 24, 2009 at 10:27 am
Query 1 is using an equality comparison which does not allow for the wildcard characater %. Query 2 is eliminating those rows because of the LIKE comparison operator.
Just so you know, using the wildcard % as the first character in a string will result in a table scan and can get to be a very poor performer.
July 24, 2009 at 10:42 am
SSCrazy, Thanks for the reply. I think you answered my question. I understand about equality not working with wildcards, but since it compiled and ran I thought it might work.
And I also know about the table scans but this is part of a system that has a user application with a query builder and is necessary for business purposes..but I definitely try to steer them to equality comparisons whenever possible.
July 24, 2009 at 10:48 am
For edification:
where
[column_name] not like '%10BUC%' and
[column_name] not like '%UNCLGAD%'
is equivalent to
where
not ([column_name] like '%10BUC%' or [column_name] like '%UNCLGAD%')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply