2

Basically i have a set of fields that user can search by. so i write a query of the form:

SELECT *
  FROM my_addr_vw
 WHERE     fname LIKE :l_fname
  AND mname LIKE :l_mname
  AND lname LIKE :l_lname;

When user does not pass any value to any of the criteria, i assume a '%'.

What that does is, if a record had NULL or no value in say, mname - then the record drops off.... What is a good solution to this problem of NULL matches nothing!!

pirho
  • 11,565
  • 12
  • 43
  • 70
user2275460
  • 339
  • 3
  • 14
  • null equals nothing...null doesn't even equal null. Use where field is null or like statement – Twelfth Aug 21 '14 at 20:03
  • `''` does match `'%'`. `NULL` can’t be matched against because it’s not a string. If appropriate, store an empty string instead of `NULL`. Otherwise, if appropriate, coerce `NULL` to an empty string in your query. Otherwise, if you want an empty query to match everything, maybe build your query dynamically and omit empty criteria. – Ry- Aug 21 '14 at 20:04
  • 2
    @minitech No, Oracle treats `''` as `NULL`. Yes, it's not correct. It's still what it does. http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null http://sqlfiddle.com/#!4/d41d8/34257 – Bacon Bits Aug 21 '14 at 20:22
  • @BaconBits: Oh. Thank you, I didn’t know that. Also, that’s sad. – Ry- Aug 21 '14 at 20:25

3 Answers3

6

Almost any comparison to NULL produces an unknown (what I think of as a NULL result for the comparison). This includes like -- for both the pattern and the string being compared.

The where clause allows rows through only when the comparison is TRUE. So, both FALSE and unknown are filtered out.

That explains the NULL piece. The '' is a bit more abstruse because here Oracle is different from other databases and the standard. Oracle treats NULL and the empty string as the same. So, '' is really just a synonym for NULL (using default Oracle settings). So, you cannot match anything to the empty string, just like you cannot match anything to NULL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • +1 Nit-pick: technically a comparison results in one of `true`, `false`, or `unknown`. Operations like addition can result in `null`, for example `42 + null`, or `'Hi ' + null`. But the result of `1 = null` or `true and unknown` is `unknown` – Andomar Aug 21 '14 at 20:23
1

What is a good solution to this problem of NULL matches nothing!!

The solution is to use IS NULL or IS NOT NULL.

SELECT *
FROM my_addr_vw
WHERE   (fname LIKE :l_fname OR fname IS NULL)
    AND (mname LIKE :l_mname OR mname IS NULL)
    AND (lname LIKE :l_lname OR lname IS NULL);
Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
0

You can return a record with a null value with the is null syntax:

 where (:l_mname = '%' and mname is null) or mname like :l_mname
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Not true. This will pull up false matches where criteria had a not null value but record had a NULL - would still match it. I had a form of (:l_mname is null or mname like :l_mname) but that is ending up inefficient. – user2275460 Aug 21 '14 at 20:17
  • Edited the answer to only return `null` rows when your parameter is `%` – Andomar Aug 21 '14 at 20:20