June 17, 2013 at 8:39 pm
Comments posted to this topic are about the item ALL clause in SQL Server
June 17, 2013 at 10:54 pm
Nice question to Start a Day 🙂 🙂
June 18, 2013 at 12:25 am
Good One i really liked the way it is framed.
June 18, 2013 at 12:26 am
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. ... "
June 18, 2013 at 1:47 am
The difference between 'IN' and 'ALL' is that , 'ALL' give true for when all the data satisfies the met condition, where as 'IN' give out the same even one data met the condition.
And regarding "NOT IN" clause. be careful while using it. in the given Qotd since EmpId<5 is the set condition it gives out the resultset.
Consider this scenario :
INSERT INTO Emptable
VALUES
(NULL,'Name11','Addr1')
SELECT *
FROM Emptable
WHERE (Departmentid NOT IN (SELECT Departmentid
FROM Emptable AS Emptable_1
WHERE (Name ='Name1')
)
)
It returns no result set; same resulutset with using ALL. Instead the query should be re-written as
SELECT *
FROM Emptable E
WHERE ( NOT EXISTS (SELECT Departmentid
FROM Emptable AS Emptable_1
WHERE (Emptable_1.Name ='Name1')
AND E.Empid = Emptable_1.Empid
)
)
So, It's good to make sure before applying ALL or NOT IN in a query.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
June 18, 2013 at 2:11 am
I got this wrong, but having read the explanation I'm still none the wiser...
June 18, 2013 at 2:34 am
Toreador (6/18/2013)
I got this wrong, but having read the explanation I'm still none the wiser...
To understand the mechanics, start with the subquery:
SELECT Departmentid
FROM Emptable AS Emptable_1
WHERE (Empid < 5)
This returns four rows (because Empid is an IDENTITY with the default starting value of 1). In these rows, the Departmentid (which is what is SELECTed) is 2, 5, 6, and 3.
Now the WHERE clause of the outer query reads (pseudo-code, as this is not valid SQL anymore):
WHERE (Departmentid <> ALL (2, 5, 6, 3))
The "ALL" qualifier means that the predicate has to be true for all rows in the subquery, so all of these four have to be true:
Departmentid <> 2
Departmentid <> 5
Departmentid <> 6
Departmentid <> 3
Or, in plain English, the Departmentid value can not be equal to any of the values 2, 5, 6, and 3.
In the table, there are two rows, with Departmentid equal to 4 and 7, that satisfy this condition. Those two rows will be returned.
Exercise for the reader: replace ALL with ANY, try to predict the results, then run the code and check if you were right.
June 18, 2013 at 2:37 am
Thanks Hugo.
So "<> ALL" is equivalent to "NOT IN" ?
June 18, 2013 at 2:52 am
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.
June 18, 2013 at 3:00 am
Hi,
Nice query and explanation, but still need clarification when to use <> ALL and NOT IN. Since in this result are same. if possible please illustrate.
June 18, 2013 at 3:06 am
Hugo Kornelis (6/18/2013)
Toreador (6/18/2013)
I got this wrong, but having read the explanation I'm still none the wiser...To understand the mechanics, start with the subquery:
SELECT DepartmentidFROM Emptable AS Emptable_1
WHERE (Empid < 5)
This returns four rows (because Empid is an IDENTITY with the default starting value of 1). In these rows, the Departmentid (which is what is SELECTed) is 2, 5, 6, and 3.
Now the WHERE clause of the outer query reads (pseudo-code, as this is not valid SQL anymore):
WHERE (Departmentid <> ALL (2, 5, 6, 3))The "ALL" qualifier means that the predicate has to be true for all rows in the subquery, so all of these four have to be true:
Departmentid <> 2Departmentid <> 5
Departmentid <> 6
Departmentid <> 3
Or, in plain English, the Departmentid value can not be equal to any of the values 2, 5, 6, and 3.
In the table, there are two rows, with Departmentid equal to 4 and 7, that satisfy this condition. Those two rows will be returned.
Exercise for the reader: replace ALL with ANY, try to predict the results, then run the code and check if you were right.
Thank you Hugo, as always a fantastic explanation.
email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk
June 18, 2013 at 3:20 am
Toreador (6/18/2013)
So "<> ALL" is equivalent to "NOT IN" ?
I checked the ANSI standard. It defines x NOT IN (y) as equivalent to NOT (x IN (y)), and x IN (y) as equivalent to x = ANY (y), so by extension, x NOT IN (y) is equivalent to NOT (x = ANY (y)).
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).
Or, the short version of the answer: "Yes". 🙂
sunita_pradhan2000 (6/18/2013)
Nice query and explanation, but still need clarification when to use <> ALL and NOT IN. Since in this result are same. if possible please illustrate.
Never use ANY, ALL, or SOME. It is always possible to rewrite queries that use them to use EXISTS / NOT EXISTS. Knowing these constructs is important for understanding other people's code, but I would never use them (except, maybe, to create a future Question of the Day).
Use IN and NOT IN only when comparing with a list of constant values. Never with a subquery. This has different reasons. One of them is the (to many people surprising, though it's actually consistent) handling of NULL values in the subquery. Another is that IN and NOT IN work only with a single column and an equality/unequality comparison; EXIST and NOT EXISTS allow multi-column comparisons and less than / more than comparisons as well - so better to be consistent and just use EXISTS in all cases.
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).
June 18, 2013 at 3:53 am
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.
Tom
June 18, 2013 at 5:10 am
Using EXCEPT is another alternative to using <> ALL in this context.
June 18, 2013 at 6:31 am
Got it wrong because I confused the first column of the sample data set to be the empid , instead of the department ID.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply