Some, Any and All oh my!

Tales of the exotic and strange

When 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.

Example:

SELECT firstName, lastName 

FROM employee AS e 

WHERE 

  e.secId >= ANY 

    (

      SELECT minSecId 

      FROM securedAssets AS sa 

      WHERE 

        sa.region = 'Inventory Mgmt'

    )

;

All

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. 

Example:

SELECT widgetName

FROM widget AS w 

WHERE 

  3 >= ALL 

    (

      SELECT availableWidgetParts 

      FROM widgetParts AS wp 

      WHERE 

        wp.widgetId = w.widgetId

    )

;

Conclusion

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.