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.

One thought on “Did You Know #30 – Order in a Predicate”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s