Views is a fantastic module that allows you to build complex database queries through configuration. However, I've found that a weird variation of the 80/20 rule applies to views: 80% of the time, the configuration will get you 100% of what you want, but 20% of the time, it will only get you 80% of what you want. That's where the views hooks are handy, especially hook_views_query_alter. I've used this many times - adding a where clause, modifying a field, etc.
But, yesterday I wanted to do something beyond even what could be accomplished with a where query - or so I thought. I wanted a where condition without a corresponding field. There are, of course, many ways to fake this. But then I decided to just look through exactly what options I had available once I had that magical $query to play with. So I checked out the object definition in plugins/views_query_plugin_default.inc.
Turns out there are options I never knew about before, including my new personal favorite, $query->add_where_expression. That's right, I can insert arbitrary (which is to say carefully considered but completely configuration independent) sql code into the query. Sweet. Thanks views.