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);
You can also use a subquery (that only returns one row) on the left side:
Select 1 From dual Where (Select 1,2) In (Select 1,2);
You can negate In with Not:
Select 1 From dual Where 1 Not In (2,3,4);
So you know In, but did you know Any? This allows you to compare against a subquery:
Select 1 From dual Where 1 = Any (Select 1);
You’ve already seen this trick, though, since In does the exact same thing. However, with Any, you can use any comparison operator:
= > < >= <= <> !=
Select 1 From dual Where 1 < Any (Select 1 Union Select 2); Any requires that the left side is a single value (a column or a constant) while the other side must be a subquery. This will not work: [sql] Select 1 From dual Where 1 < Any (1, 2); [/sql] You can also use the word Some in place of Any: [sql] Select 1 From dual Where 1 = Some (Select 1); [/sql] This allows you to pick which word fits the context better. Part of code is communicating to other developers, which includes your future self.
The All keyword is similar to Any, but it makes sure that every value in the subquery matches the one on the left:
Select 1 From dual Where 1 = All (Select 1 Union Select 1);
I pointed out that = Any is the same as In, but <> Any is not the same as Not In. The equivalent of Not In is <> All
Select 1 From dual Where 1 <> All (Select 2 Union Select 3); Select 1 From dual Where 1 Not In (Select 2 Union Select 3);
Although, you could just put a Not before the expression to negate it. This expression is equivalent to the two right above:
Select 1 From dual Where Not 1 = Any (Select 2 Union Select 3);
The null-safe equals. This one’s pretty neat: it can save you a bunch of typing. If you need to compare two nullable columns and you’d like the comparison to return true if they’re both null, you have to write an Or expression that tests for that circumstance.
Select 1 From (Select Null As Col1 Union Select 1) S1 Join (Select Null As Col1 Union Select 1) S2 On S1.Col1 = S2.Col1 Or (S1.Col1 Is Null And S2.Col1 Is Null);
Well, with the null-safe comparison, I can just write:
Select 1 From (Select Null As Col1 Union Select 1) S1 Join (Select Null As Col1 Union Select 1) S2 On S1.Col1 <=> S2.Col1;
If both columns are null, it will return true.
Underscore in Like
You’ve probably written queries like this using the like operator:
Select 1 From dual Where 'Will' Like '%ill';
The percent sign (%) is a wildcard, so this matches anything that ends in “ill”. You can also use the underscore (_) to match only one character, so this returns a row:
Select 1 From dual Where 'Bill' Like '_ill';
While this does not:
Select 1 From dual Where 'Thrill' Like '_ill';