Tuesday, 20 October 2009

You Forgot to Say the Magic Word…

In Microsoft SQL Server, this query won’t work:

SELECT * FROM ( SELECT * FROM Customer UNION SELECT * FROM Supplier) ORDER BY CompanyName

But – if you ask nicely, it does exactly what you’d expect:

SELECT * FROM ( SELECT * FROM Customer UNION SELECT * FROM Supplier) PLEASE ORDER BY CompanyName

You won’t believe the look on your colleague’s faces when you solve their problem using simple good manners.

(Of course, it actually works because PLEASE in that context just acts as a table-name alias for result of the UNION sub-select, and sub-selects in SQL Server need to have a name. But don't let that stop you using it for fun and profit.)

2 comments:

marcel said...

had a good lol about this, thanks

NickG said...

Reminds me of an old MS DOS command (can't remember which one) but under some circumstances you had to put something like /WORK as a command argument to get it to work. This seemed to unlock an undocumented feature which made it work a different way.