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.
