SQL LIKE Performance with only the wildcard (%) as a value
I am wondering what the performance of a query would be like using the LIKE keyword and the wildcard as the value compared to having no where clause at all.
Consider a where clause such as "WHERE a LIKE '%'". This will match all possible values of the column 'a'. How does this compare to not having the where clause at all.
The reason I ask this is that I have an application where there are some fields that the user may specify values to search on. In some cases the user would like all the possible results. I am currently using a single query like this:
SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?
The values of '%' and '%' can be supplied to match all possible values for a and or b. This is convenient since I can use a single named query in my application for this. I wonder what the performance considerations are for this. Does the query optimizer reduce LIKE '%' to simply match all? I realize that because I'm using a named query (prepared statement), that may also affect the answer. I realize the answer is likely database specific. So specifically how would this work in Oracle, MS SQL Server and Derby.
The alternate approach to this would be to use 3 separate queries based on the user inputting the wildcard.
A is wildcard query:
SELECT * FROM TableName WHERE b LIKE ?
B is wildcard query:
SELECT * FROM TableName WHERE a LIKE ?
A and B are wildcards:
SELECT * FROM TableName
No wildcards:
SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?
Obviously having a single query is the simplest and easiest to maintain. I would rather use just the one query if performance will still be good.
Answer
SQL Server will generally see
WHERE City LIKE 'A%'
and treat it as
WHERE City >= 'A' AND City < 'B'
...and happily use an index seek if appropriate. I say 'generally', because I've seen it fail to do this simplification in certain cases.
If someone's trying to do:
WHERE City LIKE '%ville'
...then an index seek will be essentially impossible.
But something as simple as:
WHERE City LIKE '%'
will be considered equivalent to:
WHERE City IS NOT NULL