Differene between 2 where conditions which looks similar

Question

My mind is blind when I see those 2 where conditions and I can't understand what's the difference. Can someone explain me? First returns 273 records and the second one returns 93. I thought they should return the same records.

1st

    s.value LIKE '%One Travel%'
    OR s.value LIKE '%One RSA%'
    OR s.value LIKE '%One Other%'
    OR s.value LIKE '%GP&U%'
    OR s.value = ' '

2nd

    s.value LIKE (
    '%One Travel%'
    ,'%One RSA%'
    ,'%One Other%'
    ,'%GP&U%'
    , ' ')

Answer 1

LIKE in the second command is not a function, it is actually the same LIKE predicate. But its right-hand argument is specified as a row value.

I guess many DBMS throw an error, but H2 tries to convert all values here to a character string instead, in some rare cases it is useful, but in this case it isn't.

something LIKE (
    '%One Travel%'
    ,'%One RSA%'
    ,'%One Other%'
    ,'%GP&U%'
    , ' ')`

becomes

something LIKE 'ROW (%One Travel%, %One RSA%, %One Other%, %GP&U%,  )'

in recent versions of H2 or

something LIKE '(%One Travel%, %One RSA%, %One Other%, %GP&U%,  )'

in older versions (they don't support row values, but they treat such expressions as non-standard arrays).

Both expressions are meaningless. You need to use the first command with multiple OR conditions. Alternatively you can use a complex regular expression with non-standard REGEXP operator (with similar to LIKE syntax) or with also non-standard REGEXP_LIKE function.

Actually VALUE is a keyword, so both these commands cannot be executed by H2 (at least by its recent versions), but I assume it was just a replacement of a real column name.