ALL clause in SQL Server
-
I got it wrong because I haven't had my coffee yet. I somehow didn't see the '7' in the list of department ID values. :doze:
-
zerko (6/18/2013)
When I need to write this sort of query, I use "NOT IN" , instead of "<> ALL" - Seems to do the same thing, and easier to understand what the query is trying to achieve.SELECT *FROM Emptable
WHERE (Departmentid NOT IN (SELECT Departmentid
FROM Emptable AS Emptable_1
WHERE (Empid < 5)
)
)
As the example on the linked reference page says "The following example returns a list.of employees that are not in the specified table. ... "
My thoughts too once I looked up the ALL operator.
-
Hugo Kornelis (6/18/2013)
To my surprise, ANY and ALL are not defined as each others boolean negation. But their two definitions do appear to be completely opposites. So unless I am overlooking some weird edge cases, I am going to say that NOT (x = ANY (y)) should be equivalent to x = ALL (y).No, ANY and ALL are not each others' boolean negation -- there is a very specific edge case where their results have to be the same: where the subquery returns a single row. In that case, ANY means exactly the same as ALL.
Try the following to see it in action:
select '= ANY True' where 1 = ANY (select 1)
union all
select '= ALL True' where 1 = ALL (select 1)
union all
select '= ANY False' where 1 = ANY (select 0)
union all
select '= ALL False' where 1 = ALL (select 0)
union all
select '<> ANY True' where 1 <> ANY (select 0)
union all
select '<> ALL True' where 1 <> ALL (select 0)
union all
select '<> ANY False' where 1 <> ANY (select 1)
union all
select '<> ALL False' where 1 <> ALL (select 1)
Unless there's a bug in your SQL server, that query should only return the True rows.
-
sknox (6/18/2013)
Hugo Kornelis (6/18/2013)
To my surprise, ANY and ALL are not defined as each others boolean negation. But their two definitions do appear to be completely opposites. So unless I am overlooking some weird edge cases, I am going to say that NOT (x = ANY (y)) should be equivalent to x = ALL (y).No, ANY and ALL are not each others' boolean negation -- there is a very specific edge case where their results have to be the same: where the subquery returns a single row. In that case, ANY means exactly the same as ALL.
Okay, that edge case makes logical sense to me. But you've got me thinking, and that's a dangerous thing. What happens when the subquery returns no rows?
Logically = ANY should always be FALSE in this case, because there's nothing to equal. Same with = ALL, right? Well...
Try this code:
select '= ANY True' where 1 = ANY (select 1 where 1=0)
union all
select '= ALL True' where 1 = ALL (select 1 where 1=0)
union all
select '= ANY False' where 1 = ANY (select 0 where 1=0)
union all
select '= ALL False' where 1 = ALL (select 0 where 1=0)
union all
select '<> ANY True' where 1 <> ANY (select 0 where 1=0)
union all
select '<> ALL True' where 1 <> ALL (select 0 where 1=0)
union all
select '<> ANY False' where 1 <> ANY (select 1 where 1=0)
union all
select '<> ALL False' where 1 <> ALL (select 1 where 1=0)
Can anyone explain the results?
-
Thanks for this interesting question.
-
sknox (6/18/2013)
sknox (6/18/2013)
Hugo Kornelis (6/18/2013)
To my surprise, ANY and ALL are not defined as each others boolean negation. But their two definitions do appear to be completely opposites. So unless I am overlooking some weird edge cases, I am going to say that NOT (x = ANY (y)) should be equivalent to x = ALL (y).No, ANY and ALL are not each others' boolean negation -- there is a very specific edge case where their results have to be the same: where the subquery returns a single row. In that case, ANY means exactly the same as ALL.
Okay, that edge case makes logical sense to me. But you've got me thinking, and that's a dangerous thing. What happens when the subquery returns no rows?
Thanks for the explanation of where they are not each others' counterpart. I guessed I didn't express myself well enough, for looking at it now I must say that what I wrote makes little sense anyway.
What I meant to write is that, as far as I see Column = ANY (...) is equivalent to NOT (Column <> ALL (...)) - and similarly with other operators, e.g. Column < ALL (...) is equivalent to NOT (Column >= ANY (...)), etc.
On your last question - I noticed (earlier today, when I looked it up in the standards document) that this is explicitly defined in the standard. ALL with an empty set always returns true, ANY with an empty set always returns false. Sounds logical to me.
I have not checked your code, so I don't know if the observed behaviour in SQL Server matches what the standard dictates.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
sknox (6/18/2013)
[Logically = ANY should always be FALSE in this case, because there's nothing to equal. Same with = ALL, right? Well......
...
...
Can anyone explain the results?
It's quite straightforward. ALL works when the required predicate fails for none of the values in the generated set. ANY works when it succeeds for at least one value in the generated set. Or (no more accurately, but more historically true) ANY can succeed as soon as a value delivers TRUE, while ALL can fail as soon as a value delivers FALSE. That certainly generates the right results for your query set. There is traditionally a "good reason" for defining it that way: rather than generate all the values test them as they are generated using a test which can often (about half the time) be decided on a first hit basis - that must perform better than generating and testing all the values every time. T-Sql isn't the first language to have conjunctions of comparisons of one value with values in a set, and this way of saving a little compute power was used at least as long ago as the 1960s (don't ask me what language - all I remember is that it was one of dozens of languages that I looked at between Oct 1968 and Mar 1969).
Tom
-
Nice and easy wins the race 🙂
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Koen Verbeeck (6/18/2013)
Got it wrong because I confused the first column of the sample data set to be the empid , instead of the department ID.Yes me too i got it wrong for the same reson and then when looking at it again I calculated it.
So really what I learnt from this exercise is to use Exists/ NOT EXISTS in sub queries rather than IN/ NOT IN
Shaheen Karatela
MCTS Business Intelligence Development & Maintenance
-
Should have read the Hugo's explanation first....
-
L' Eomot Inversé (6/18/2013)
Hugo Kornelis (6/18/2013)
hillsl (6/18/2013)
I got it wrong because the question in the emailed newsletter is what I was answering, and in the newsletter the sub-select only has one column, and so is valid SQL.I don't understand this comment. The question in the emailed newsletter is identical to the question on the site. The only place where an extra column is added to the subquery is in the explanation (which is not really an explanation of the answer at all, just some additional information).
The explanation on the website is unbelievably sparse and confusing. Your explanation in an earlier message is the only useful one. The quality of the explanation is a pity, because the question itself is actually a good one. It has a deplorable feature as its subject matter, but MS has stuck that deplorable feature into T-SQL and some people will end up using it, which means all the rest of us have to learn the rotten thing and remember not to use it, so it's probably useful to have a question about it.
btw, by lazy reading (assuming the column name in the where clause of the subquery would be the same as the column name in the select list of that subquery, and not noticing that it was actually different) I got the wrong number. I wonder whether it is just a coincidence that one of the incorrect options is that number, or was that done carefully to remind lazy people like me to be lazy only when it is constructive and useful - if it was done for that purpose that was a good intelligent bit of thinking by the question's author.
+1
-
I got it right however I can't see why I would ever use this over an exists/not exists syntax. This is another bit of syntax that I have never seen used anywhere and I don't really see the purpose of it being supported. Not when there are multiple other ways to do the same thing with syntax that is much more commonly used.
Hopefully none of my colleagues are reading today's QOTD and thinking, I'll use that from now on! 😀
-
I really was about to race to answer 5. But then gave it a though as usual and got it clear. It was confusing for me between EmpId and DepartmentId. Leter got it correct. 🙂
-
I do not understand the explanation as the sub query only returns the departmentid, not the empid.
-
samtrenchard (7/1/2013)
I do not understand the explanation as the sub query only returns the departmentid, not the empid.The explanation doesn't actually explain the question and answer. It only tells you what would happen if you made a certain change to the query, which is irrelevant to the actual question.
For an explanation of the correct answer, please read the posts already made in this topic.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply