CODEWITHSUNDEEP
sql
coutier eric
coutier eric

Reputation: 1019

Like '%' in where clause : is it always ignored

To simplify/generalize my SQL, i want to use this code:

SELECT ... FROM ... WHERE ColumnName LIKE '[To_be_replaced]'

If a certain field of my gui is empty, i want to replace "To_be_replaced" by "%":

SELECT ... FROM ... WHERE ColumnName LIKE '%'

but if it contains something, [To_be_replaced]" is replaced with the content, like this:

SELECT ... FROM ... WHERE ColumnName LIKE 'foo'

My question is: do all (or major) SQL Engine optimize this code by simply ignoring where condition when content of like is "%" alone ?

Upvotes: 4

Views: 269

Answers (1)

user5683823
user5683823

Reputation:

Oracle does NOT ignore it. Indeed, Oracle is not SQL Standard compliant; to Oracle, a string of length zero is the same as NULL, and comparisons to NULL are never TRUE.

The Oracle optimizer (and the Oracle "logic", really, regardless of "optimizer") interprets where str_column LIKE '%' the same as where str_column IS NOT NULL. And, to judge by the EXPLAIN PLAN, it doesn't ignore the LIKE condition even then. The EXPLAIN PLAN shows an AND of two filters: is not null AND the like '%' condition, even though the latter should be omitted.

In any case, in Oracle omitting the LIKE condition altogether would produce different results (due to Oracle's non-standard treatment of empty strings).

Added: Duh! In any DBMS a comparison to NULL never evaluates to TRUE, so ...like '%' is equivalent to is not null. Even a horse knows that - even one with no name (see Comment below). I don't know what I was thinking.

Either way, the Oracle EXPLAIN PLAN shows the is not null condition and it STILL shows the like '%' condition as well. It is not optimized away.

Further: I created a table with a column declared specifically not null. The like '%' filter is still present in the EXPLAIN PLAN, even though now the is not null filter is no longer present (for obvious reasons).

Upvotes: 3

Related Questions