O'Reilly logo
  • Dustin Waybright thinks this is interesting:

SELECT productid, productname, unitprice FROM Production.Products WHERE supplierid IN (SELECT supplierid FROM Production.Suppliers WHERE country = N'Japan');

From

Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Note

Using a subquery for the IN predicate to generate a single column list, like generating a list of record codes where a record name is like X.