Tales of the exotic and strangeWhen discussing some of the strange parts of a "new to me" system, a co-worker said "That's nothing, our queries have rules where the data has to meet Some, Any or All criteria!"
Now this sounded completely absurd to me so I just let it slide by, but it got me thinking. There are a number of unusual ANSI operators that appear to be logically redundant, maybe this is real.
Sure enough, it is real.
What is surprising, is that isn't an Operator but instead is a Modfier to existing operator.
Some / Any
Some and Any are synonyms. If the subset (query, or static list) has any records that meet the criteria records are returned.
Isn't that an "IN"?
Nope. "IN" is an operator, and would be the same as the combination "= ANY" but because this is a modifier you can pull some neat tricks like ">= ANY". This is handy for checking things like security levels or general product availability.
SELECT firstName, lastName
FROM employee AS e
e.secId >= ANY
FROM securedAssets AS sa
sa.region = 'Inventory Mgmt'
All is another one with some interesting rules. Everything that is returned needs to meet the criteria. This is pretty terrific for things like being able to test if there is enough inventory to build an assembled item. You know if you are building 3 widgets, you need to have at least 3 of each part.
FROM widget AS w
3 >= ALL
FROM widgetParts AS wp
wp.widgetId = w.widgetId
Now this is a nifty feature, and there are plenty of other ways to do this for the majority of cases. I'll probably lump it in INTERSECT and EXCEPT as part of my seldom used, exotic query techniques.