Subqueries are very nice indeed, especially being able to use them in the FROM section (e.g. as inline views), which is wonderfully elegant for certain queries. Admittedly, it also makes your SQL query very long and complicated: I ended up refactoring the queries using proper views... and then realised that MySQL doesn't support this either! I understand that most of what you can do with a subquery you can rewrite with a join, and that views are a form of syntactic sugar, but why refuse to use the expressive power if it's there? (cp Paul Graham's "Beating the Averages")
(OK, the reason to not use the full expressive power of Oracle has always been for me the fact that I'm not paying for the license myself... which is why I'm glad to get the chance to play with it at work.) I really have to look some more at Postgres, which seems to have a lot of the features of Oracle at the same lovely price as MySQL. (Annoyingly it looks like doesn't run on Win32 except with Cygwin).