Understanding ANY And ALL In SQL

Doug Kline explains the ANY and ALL operators in SQL:

-- note that this creates a single column of values
-- which could be used in something like IN
-- for example
SELECT 1
WHERE 12 IN ( SELECT tempField FROM (VALUES(11),(12),(7)) tempTable(tempField))
-- I could rephrase this as:
SELECT 1
WHERE 12 = ANY ( SELECT tempField FROM (VALUES(11),(12),(7)) tempTable(tempField))

I rarely see these operators in the wild and might have used them in production code a couple of times if that.

Related Posts

Default Schemas in SQL Server

Max Vernon takes us through the order in which SQL Server searches for tables given a single-part name: Default schemas in SQL Server can be a blessing, since they reduce the need to specify the schema when creating DDL statements in T-SQL. However, relying on the default schema when creating DML statements can be problematic. […]

Read More

Making Dynamic SQL Safe

Erik Darling explains patiently that if you use sp_executesql wrong, you don’t get the benefits of using it right: The gripes I hear about fully fixing dynamic SQL are: – The syntax is hard to remember (setting up and calling parameters)– It might lead to parameter sniffing issues I can sympathize with both. Trading one […]

Read More

Categories

October 2018
MTWTFSS
« Sep Nov »
1234567
891011121314
15161718192021
22232425262728
293031