Did You Know #30 – Order in a Predicate

I’ve known this for a while but never wrote about it. In KScope19 I attended a session by Alex Nuijten and during the session he asked a question about LIKE predicate that reminded me this, so here it is.

Equality

It’s quite obvious that if you have a column (let’s say ID) and you want to query only the rows with the value 5 you can write it like this:

SQL> select id from tab where id=5;
        ID
----------
         5

But you can also write:

select * from tab where 5=id;

IN

You can play with the order with the IN predicate as well. When you write “where id in (5,6)” it means id=5 or id=6. Now, how about this?

SQL> select id1,id2 from tab where 5 in (id1,id2);
       ID1        ID2
---------- ----------
         5          9
         2          5

This is completely valid and it means id1=5 or id=5.

BETWEEN

The same goes for BETWEEN:

SQL> select id1,id2 from tab where 5 between id1 and id2;
       ID1        ID2
---------- ----------
         1          7
         5          9
         2          5

This can be translated to: “where id1<=5 and 5<=id2”.

LIKE

Probably the most freaky thing is with LIKE. What will happen if we change the order? The interesting thing is when out data has special character in it:

SQL> select name from tab where 'BLAKE' like name;
NAME
----------------------------------
BLAKE
BL%KE
BL_KE

Note how Oracle considers the data in our column as wildcards so we get the names with “%” and “_” in them.