Categories
Script SQL The Arcane

ALL, ANY, and SOME: 3 SQL operators you do NOT want to use…

http://bradsruminations.blogspot.com/2009/08/all-any-and-some-three-stooges.html

-- 4 questions with BoxOf3Coins
with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ANY of the coins in BoxOf3Coins?',case when 25 > any (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ALL (EACH) of the coins in BoxOf3Coins?',case when 25 > all (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ANY of the coins in BoxOf3Coins?',case when 25 = any (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ALL (EACH) of the coins in BoxOf3Coins?',case when 25 = all (select CoinValue from BoxOf3Coins) then 'Yes' else 'No' end;

-- Now answer the same question with the EmptyBox
with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ANY of the coins in EmptyBox?',case when 25 > any (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth more than ALL (EACH) of the coins in EmptyBox?',case when 25 > all (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ANY of the coins in EmptyBox?',case when 25 = any (select CoinValue from EmptyBox) then 'Yes' else 'No' end;

with BoxOf3Coins(CoinValue) as (select  5 union all select 10 union all select 25)
	,EmptyBox as (select CoinValue from BoxOf3Coins where 1 = 2)
select 'Is twenty-five cents worth the same as ALL (EACH) of the coins in EmptyBox?',case when 25 = all (select CoinValue from EmptyBox) then 'Yes' else 'No' end;