I love it when software gives you elegant ways of solving your problem. Programming language designers make me feel like they care when they take the time to include succinct, powerful expressions. I’ve recently discovered some in new things in MySQL, as well as a few rediscoveries. This is the first five, and I’ll cover the next five in another article.
You’ve probably used the standard In operator before:
Select 'Oh yeah!' From dual Where 1 In (1,2,3);
As a side note, the dual table is just a dummy table that always returns one row. It’s useful for demonstrating language features or running experiments.
You can also use a subquery with In:
Select 1 From dual Where 1 In (Select 1);
The thing I discovered was that it’s not just scalar values: it’s actually comparing rows, so you can see if a row is present:
Select 1 From dual Where (1,2) In (Select 1,2); …[Read more]