title: SQL LIKE Performance with only the wildcard (%) as a value
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?
  </code></pre>
  
  <p>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.</p>
  
  <p>The alternate approach to this would be to use 3 separate queries based on the user inputting the wildcard.</p>
  
  <p>A is wildcard query:</p>
  
  <pre><code>SELECT * FROM TableName WHERE b LIKE ?
  </code></pre>
  
  <p>B is wildcard query:</p>
  
  <pre><code>SELECT * FROM TableName WHERE a LIKE ?
  </code></pre>
  
  <p>A and B are wildcards:</p>
  
  <pre><code>SELECT * FROM TableName
  </code></pre>
  
  <p>No wildcards:</p>
  
  <pre><code>SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?
  </code></pre>
  
  <p>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.</p>