An SQL SELECT supplying a set for another SELECT to operate on in place of a table.

select * from (select * from foo where bar) where baz

select * from foo where baz in (select * from bar where quux)

MySQL lacked the functionality for years, until release 4.1. That truly sucked the smelly kumera. And now that it is finally implemented, it still has irritating limitations like prohibiting nested SubSelects.