I don't know why I'd never used the IN clause in this manner before, but it just came to mind...

I was writing an ad-hoc query against the sitemap table.  I needed to find any nodes where either the node label or node heading was "Course Section Mark Entry".  This felt clever:

   1: SELECT        *
   2: FROM        NavigationSitemap
   3: WHERE  'Course Section Mark Entry' IN (Heading, Label)

I've used similar approaches before, but never exactly like this.