SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Hugo Kornelis / HAVING EXISTS / Latest Posts

RE: HAVING EXISTS

Jamie Longstreet-481950 — Sat, 25 Sep 2010 06:19:29 GMT

All I can say is WOW. Neat one!

RE: HAVING EXISTS

Hugo Kornelis — Wed, 15 Sep 2010 15:52:59 GMT

[quote][b]rahul2671985 (9/15/2010)[/b][hr]Hi,Thanks for replying me. I got stuck after "Having Exists".. the serch condition should take the groups which has max(a.thevalue) greater thaen 3. that I cannot figure out hw it works?can u plz explain me in brief?Rahul[/quote]I'm not well known for my ability to explain "in brief";-) So I hope you don't mind a long explanation.You first have to understand the basics of EXISTS. This condition is mostly used in a WHERE clause. For instance this query (which looks a bit like the one in the question, but is actually different):[code="sql"]SELECT a.GroupName, a.TheValueFROM QotD AS aWHERE EXISTS (SELECT * FROM QotD AS b WHERE b.TheValue > a.TheValue);[/code]Logically speaking (the actual implementation can be different, but that is beyond our scope for now), SQL Server will process rows from table QotD (aliased as a) one by one. For each row, it will evaluate the subquery, thereby substituting the value of TheValue in the current row for a.TheValue. So for the "first" row (Group 1 / 1), the condition in the subquery will read "WHERE b.TheValue > 1", whereas for the "last" rows (Group 7 / 3), it will read "WHERE b.TheValue > 3".After evaluating the subquery, SQL Server checks to see if any rows were returned. If that is the case, the EXISTS clause is considered to be true (there exists at least one row in the result of the subquery), the row passes the check for the conditions in the WHERE clause, so it will be included in the results of the query. If the result of the subquery is empty, the EXISTS condition evalutes to false (no row exists in the subquery) and the "current" row is discarded from the result set of the outer query.You'll also have to understand the basics of HAVING. The HAVING clause is very similar to the WHERE clause. The only difference is that it does not operate on individual rows, but on groups of rows as defined by the GROUP BY clause. And that it includes or excludes those groups in their entirety based on the result of the condition evaluation.In the case of the query in the original question, groups are made on GroupName - rows with the same GroupName are considered to be in the same group. Because of this grouping, it is still possible to refer to a.GroupName in the HAVING clause (as there can by definition be only one GroupName per group), but you can no longer access individual values of a.TheValue - there might be groups that happen to have only a single value in this column, but there is no guarantee and therefor the syntax rules forbid you to reference a.TheValue (as this would introduce the risk of an ambiguous reference). You can, however, include non grouped columns in an aggregate function. For each group, regardless of the number of rows it contains, MAX(a.TheValue), COUNT(DISTINCT a.TheValue), or AVG(a.TheValue) are an unambiguous reference that can evaluate to only one single value.So in short, "HAVING a.TheValue = 2" is illegal (unless you add a.TheValue to the GROUP BY clause, but that would change the semantics); but "HAVING MAX(a.TheValue) = 2" is fine.Once you know all these basics, understanding HAVING EXISTS is just a matter of combining the two. The HAVING implies that a condition has to be evaluated for each group (not for individual rows). The EXISTS then says that for each group, the subquery has to be evaluated. But references to the outer table have to be replaced by the values of the current group first. And bacause this is in a HAVING, a reference can only be to a column included in the GROUP BY, or to another column in an aggregate function.In the question, MAX(a.TheValue) is the only reference. For the "first" group (Group 1), MAX(a.TheValue) is 3, so the subquery evaluated reads[code="sql"]EXISTS (SELECT * FROM QotD AS b WHERE b.TheValue > 3)[/code](NOTE: The "& gt;" in the code above should be a > symbol, but for some reason the site keeps changing it to the wacky & gt code)If this subquery returns rows, the entire group is included in the result set of the outer query; if the subquery returns an empty set, the entire group is excluded from the result set.And that's all. Basically, two concepts that are rather familier to most SQL Server developers, used in a very unfamiliar combination.Does this help you figure out what happens?

RE: HAVING EXISTS

Sean Lange — Wed, 15 Sep 2010 11:50:57 GMT

Go back and look at the whole thread. It has been explained in great detail with multiple scripts to run and test. Start on about 2 of the comments and read through the end of page 3. :-)

RE: HAVING EXISTS

Rahul26 — Wed, 15 Sep 2010 11:41:49 GMT

Hi,Thanks for replying me. I got stuck after "Having Exists".. the serch condition should take the groups which has max(a.thevalue) greater thaen 3. that I cannot figure out hw it works?can u plz explain me in brief?Rahul

RE: HAVING EXISTS

Hugo Kornelis — Wed, 15 Sep 2010 00:42:18 GMT

[quote][b]rahul2671985 (9/14/2010)[/b][hr]I didnt get the exists condition here.. hw it works? can anyone explain me plz?Thanks,rahul[/quote]I second Nakul's suggestion to first read the entire discussion. If, after that, you still struggle to understand the condition, then please post back and indicate exactly where you are stuck. I'll be happy to explain further.

RE: HAVING EXISTS

Nakul Vachhrajani — Tue, 14 Sep 2010 22:29:37 GMT

[quote][b]rahul2671985 (9/14/2010)[/b][hr]I didnt get the exists condition here.. hw it works? can anyone explain me plz?Thanks,rahul[/quote]Rahul, please follow the entire discussion from page# 1, and I am sure you will get it. Essentially, both the WHERE and HAVING conditions are ultimately evaluated as boolean conditions - if the ultimate result is true, the particular record stays in the result set; else it is discarded. What this question also illustrates very well is the logical query processing order, which is also clarified throughout the discussion.

RE: HAVING EXISTS

Rahul26 — Tue, 14 Sep 2010 16:40:38 GMT

I didnt get the exists condition here.. hw it works? can anyone explain me plz?Thanks,rahul

RE: HAVING EXISTS

L' Eomot Inversé — Sat, 11 Sep 2010 13:53:33 GMT

Really good fun question. Thanks Hugo.I looked at it for a bit and concluded that if it didn't generate an error message then it should return the three groups with no 3 in them. Would it generate an error? Well, why should it? Having needs a logical condition expression, so an "exists" expression should be OK as long as it obeys the rules for all logical condition expressions in a Having clause (ie don't refer to an non-aggregated column attribute of a row contributing to the group other that one of the grouping attributes). But it took me a while to convince myself that banning "Having Exists" would be silly, in fact I wasn't 100% certain so I was relieved when the website told me I had gotten it correct.

RE: HAVING EXISTS

webrunner — Thu, 09 Sep 2010 15:23:06 GMT

[quote][b]dbowlin (9/8/2010)[/b][hr]QoTD has really been kicking my butt the last couple of weeks. I had to reread the explanation a couple of times to understand it. Good question, thanks.[/quote]Ditto. I'm getting some of them right, but Hugo really has my number. He posts some great questions, and invariably I get them wrong, but in this case I am going to have to study not only his original explanation but also the example he posted in the discussion (above). I don't understand it yet and I really want to.Thanks, Hugo!- webrunner

RE: HAVING EXISTS

wbeaton-664441 — Thu, 09 Sep 2010 08:51:26 GMT

Great question!

RE: HAVING EXISTS

Kangana Beri — Wed, 08 Sep 2010 18:02:12 GMT

Great question! Thanks.

RE: HAVING EXISTS

jlennartz — Wed, 08 Sep 2010 15:07:35 GMT

That made my head swim. I was really confused after the explaination but as usual the discussion cleared it up. Thanks everyone.

RE: HAVING EXISTS

Dennissinned — Wed, 08 Sep 2010 12:42:20 GMT

WOW Hugo. Thanks for the question. Really had me thinking.

RE: HAVING EXISTS

SQLRNNR — Wed, 08 Sep 2010 10:25:33 GMT

Excellent Question

RE: HAVING EXISTS

sknox — Wed, 08 Sep 2010 10:18:56 GMT

[quote][b]Tony Bater (9/8/2010)[/b][hr]In a recent blog, Kalen Delaney said she is frequently asked why is there a need to understand the internals of SQL Server. I think this question illustrates why. The key to getting the right answer is an understanding of the sequence of events during the query processing - the HAVING clause is processed after the GROUP BY and acts on the result set from the earlier phases.I learned this from Itzik Ben-Gan's book 'Inside Microsoft SQL Server 2008: T-SQL Querying'. Chapter 1, Logical Query Processing, has a great section on the sequence of events that occurs when the query processor gets to work. I really recommend this book to anyone who is struggling to understand the explanation for this QotD.[/quote]Strictly speaking, this is not about the internals of SQL Server, but about the logical stucture of T-SQL. First and foremost, you need to understand the logical schema, because that's the only way you can write code that fits the schema, as well as tell if a given coding problem is your error or Microsoft's :-P The primary reason to understand the internals of SQL Server comes after that -- so you can know how to write queries that are not only logically correct, but that will perform well within the constraints of SQL Server and your environment.In other words, you learn the logical process to write code that works, and then learn the actual internal process so you can write code that works well.(Disclaimer: I got the question wrong, because I have much to learn about both subjects :hehe: )

RE: HAVING EXISTS

TSycamore — Wed, 08 Sep 2010 09:20:46 GMT

Good question, but there's an error in the explanation of the answer. At the end, it should say "with a maximum for TheValue of less than 3", not "less than 2".

RE: HAVING EXISTS

Hugo Kornelis — Wed, 08 Sep 2010 08:48:08 GMT

[quote][b]Bradley Deem (9/8/2010)[/b][hr]In regards to your practice application example, assuming I understand the requirement, I believe there is a mistake in the having clause. See the comment in the first select statement.[/quote]You are absolutely right. Sorry for that mistake, and thanks for posting the corrected script.

RE: HAVING EXISTS

Bradley Deem — Wed, 08 Sep 2010 08:46:16 GMT

Thanks for the question Hugo and the practical application example. Your explanation of the QOTD from two days ago provided a great refresher and helped me get it right.In regards to your practice application example, assuming I understand the requirement, I believe there is a mistake in the having clause. See the comment in the first select statement.Here is a full working example with set up and tear down.[code="sql"]CREATE TABLE Orders(OrderNo INT,OrderDate DATETIME,CustomerNo INT)CREATE TABLE OrderItems(OrderNo INT,Amount INT, ItemCode VARCHAR(10)) INSERT OrdersVALUES(100,'9-8-10',1),(101,'9-8-10',2),(102,'9-8-10',3)INSERT OrderItemsVALUES(100,1,'Bike'),(100,2,'Tire'),(101,2,'Spokes'),(102,3,'Pedal')DECLARE @CustomerNo AS INT = 1SELECT o.OrderNo, o.OrderDate, SUM(oi.Amount) AS TotalOrderAmountFROM Orders AS oINNER JOIN OrderItems AS oi ON oi.OrderNo = o.OrderNoWHERE o.CustomerNo = @CustomerNoGROUP BY o.OrderNo, o.OrderDateHAVING NOT EXISTS (SELECT * FROM Orders AS o2 INNER JOIN OrderItems AS oi2 ON oi2.OrderNo = o2.OrderNo WHERE o2.OrderNo <> o.OrderNo AND o2.OrderDate = o.OrderDate GROUP BY o2.OrderNo HAVING COUNT(oi2.ItemCode) = COUNT(oi.ItemCode)); -- Changed the <> to = SELECT o.OrderNo, o.OrderDate, SUM(oi.Amount) AS TotalOrderAmountFROM Orders AS oINNER JOIN OrderItems AS oi ON oi.OrderNo = o.OrderNoWHERE o.CustomerNo = @CustomerNoGROUP BY o.OrderNo, o.OrderDate HAVING COUNT(oi.ItemCode) <> ALL (SELECT COUNT(oi2.ItemCode) FROM Orders AS o2 INNER JOIN OrderItems AS oi2 ON oi2.OrderNo = o2.OrderNo WHERE o2.OrderNo <> o.OrderNo AND o2.OrderDate = o.OrderDate GROUP BY o2.OrderNo); DROP TABLE OrdersDROP TABLE OrderItems[/code]

RE: HAVING EXISTS

Hugo Kornelis — Wed, 08 Sep 2010 08:45:35 GMT

A while back, I promised to post the QotD code in a copy/paste ready format in the discussion when I have the code as a graphic in the question. Yet, I always forget to do as I promised. My apologies to all those who already spent time typing it in for yourself.And to those who want to try it out, and experiment to see how small changes affect the result, here is some SQL you can copy and paste:[code="sql"]CREATE TABLE QotD (GroupName varchar(20) NOT NULL, TheValue int NOT NULL, PRIMARY KEY (GroupName, TheValue) );goINSERT INTO QotD (GroupName, TheValue)SELECT 'Group 1', 1 UNION ALLSELECT 'Group 1', 2 UNION ALLSELECT 'Group 1', 3 UNION ALLSELECT 'Group 2', 1 UNION ALLSELECT 'Group 2', 2 UNION ALLSELECT 'Group 3', 1 UNION ALLSELECT 'Group 3', 3 UNION ALLSELECT 'Group 4', 2 UNION ALLSELECT 'Group 4', 3 UNION ALLSELECT 'Group 5', 1 UNION ALLSELECT 'Group 6', 2 UNION ALLSELECT 'Group 7', 3;goSELECT a.GroupName --, MAX(a.TheValue)FROM QotD AS aGROUP BY a.GroupNameHAVING EXISTS (SELECT * FROM QotD AS b WHERE b.TheValue > MAX(a.TheValue));goDROP TABLE QotD;go[/code]EDIT: I added an extra column, commented out, to the SELECT. This might help you see what happens.

RE: HAVING EXISTS

Robert Frasca — Wed, 08 Sep 2010 08:37:44 GMT

Group 1 - max(a) is 3 ---- nothing in b is greater than aGroup 2 - max(a) is 2 ---- 3 (in b which is the whole table) is greater than 2 so this is trueGroup 3 - max(a) is 3 ---- nothing in b is greater than aGroup 4 - max(a) is 3 ---- nothing in b is greater than aGroup 5 - max(a) is 1 ---- 2 & 3 (in b which is the whole table) are greater than 1 so this is trueGroup 6 - max(a) is 2 ---- 3 (in b which is the whole table) is greater than 2 so this is trueGroup 7 - max(a) is 3 ---- nothing in b is greater than aOkay, I've got it now. I think I was getting my a and b contexts confused. Thanks for the help folks. It probably helped that I increased my blood caffeine level.GREAT question. The amount of smoke coming out of my ears has doubled.

RE: HAVING EXISTS

Tony Bater — Wed, 08 Sep 2010 08:36:07 GMT

In a recent blog, Kalen Delaney said she is frequently asked why is there a need to understand the internals of SQL Server. I think this question illustrates why. The key to getting the right answer is an understanding of the sequence of events during the query processing - the HAVING clause is processed after the GROUP BY and acts on the result set from the earlier phases.I learned this from Itzik Ben-Gan's book 'Inside Microsoft SQL Server 2008: T-SQL Querying'. Chapter 1, Logical Query Processing, has a great section on the sequence of events that occurs when the query processor gets to work. I really recommend this book to anyone who is struggling to understand the explanation for this QotD.

RE: HAVING EXISTS

Nakul Vachhrajani — Wed, 08 Sep 2010 07:51:05 GMT

[quote][b]Robert Frasca (9/8/2010)[/b][hr]..., there can never be anything GREATER THAN a?...[/quote]Robert, I believe the key is to treat the groups individually. Therefore, when Group 1 from "a" is being evaluated (because of the group by), it is being evaluated against the whole QotD table "b". Here, we are looking for the GroupNames from the [b]grouped[/b] set "a" whose MAX(a.TheValue) values are less than the "b.TheValue"Since the MAX(a.TheValue) for Group 1 is 3, which is same as the max value for "b" it does not appear in the result set.The MAX(a.TheValue) for Group 2 is 2, which is less than "b", and therefore it appears in the result set.Hope that clears things out a bit. You can break the query into two, with the first part being:[code="sql"]SELECT a.GroupName, MAX(a.TheValue)FROM QotD AS aGROUP BY a.GroupName, a.TheValue[/code]Now compare this with "b" - you should start getting a feel of the internal workings.

RE: HAVING EXISTS

Sean Lange — Wed, 08 Sep 2010 07:47:28 GMT

[quote][b]Robert Frasca (9/8/2010)[/b][hr]All right, I confess. I don't get it. the subquery says where b GREATER THAN a not LESS THAN. As near as I can tell, there can never be anything GREATER THAN a? I could understad >= perhaps but not this.I'm sure there's a moronically simple explanation but I didn't understand the explanation provided. No reflection on Hugo, I think my pea brain simply requires further clarity.[/quote]this is what i struggled with for a bit myself. The having will return the row when there is another group in the table with TheValue > the current group. Keep in mind that in the subquery, max(a.TheValue) is for the current group only. I had to create a table with the data in it before I totally got it myself. :w00t:

RE: HAVING EXISTS

Robert Frasca — Wed, 08 Sep 2010 07:40:48 GMT

All right, I confess. I don't get it. the subquery says where b GREATER THAN a not LESS THAN. As near as I can tell, there can never be anything GREATER THAN a? I could understad >= perhaps but not this.I'm sure there's a moronically simple explanation but I didn't understand the explanation provided. No reflection on Hugo, I think my pea brain simply requires further clarity.

RE: HAVING EXISTS

Sean Lange — Wed, 08 Sep 2010 07:32:47 GMT

Agreed excellent question. Took me a bit to understand the subtlety of it. Then just as I understood it, I discovered what was causing me to seriously scratch my head.[quote]So the result of this query will be a list of all groups with a maximum for TheValue of less than 2.[/quote]The list of all groups with a maximum for the group with a max less than 3 not 2.

RE: HAVING EXISTS

Daniel Bowlin — Wed, 08 Sep 2010 06:58:42 GMT

QoTD has really been kicking my butt the last couple of weeks. I had to reread the explanation a couple of times to understand it. Good question, thanks.

RE: HAVING EXISTS

Hugo Kornelis — Wed, 08 Sep 2010 05:59:08 GMT

Thanks, all, for the kind words![quote][b]jcrawf02 (9/8/2010)[/b][hr]Hugo, do you have an application of this?[/quote]Yes, I have. Although that particular situation called for a HAVING NOT EXISTS.I can't share the actual details, so I'll have to pretend it was about an order processing system. The requirement was to find those orders from a particular customer where no other order on the same date (but by any customer) had the same number of order items. (I know this requirement makes no sense at all; just trust me that it does make sense in the actual context, which I can't disclose).This is how I did it:[code="sql"]SELECT o.OrderNo, o.OrderDate, SUM(oi.Amount) AS TotalOrderAmountFROM Orders AS oINNER JOIN OrderItems AS oi ON oi.OrderNo = o.OrderNoWHERE o.CustomerNo = @CustomerNoGROUP BY o.OrderNo, o.OrderDateHAVING NOT EXISTS (SELECT * FROM Orders AS o2 INNER JOIN OrderItems AS oi2 ON oi2.OrderNo = o2.OrderNo WHERE o2.OrderNo <> o.OrderNo AND o2.OrderDate = o.OrderDate GROUP BY o2.OrderNo HAVING COUNT(oi2.ItemCode) <> COUNT(oi.ItemCode));[/code]I could also have solved this in a different way, by changing the HAVING clause of the outermost to[code="sql"]HAVING COUNT(oi.ItemCode) <> ALL (SELECT COUNT(oi2.ItemCode) FROM Orders AS o2 INNER JOIN OrderItems AS oi2 ON oi2.OrderNo = o2.OrderNo WHERE o2.OrderNo <> o.OrderNo AND o2.OrderDate = o.OrderDate GROUP BY o2.OrderNo);[/code]But frankly, I believe the ALL operator to be even more obscure than HAVING EXISTS. :-D(EDIT: Proved my own point by using ANY instead of ALL when I posted this - now corrected)

RE: HAVING EXISTS

ronmoses — Wed, 08 Sep 2010 05:46:31 GMT

I had to stare at it a long while, but I finally came up with the right answer without running any queries or researching... woo-hoo! :w00t: Nice question!ron

RE: HAVING EXISTS

jcrawf02 — Wed, 08 Sep 2010 05:33:54 GMT

Definitely a tricky one. Hugo, do you have an application of this?

RE: HAVING EXISTS

Andeavour — Wed, 08 Sep 2010 04:35:26 GMT

Great question... I was another victim, but I learned something new here today, which hopefully I'll remember :-)

RE: HAVING EXISTS

Nakul Vachhrajani — Wed, 08 Sep 2010 03:40:50 GMT

Hello!I believe the most important keys to solving this one (as you rightly mentioned) are to understand that:1. Both the WHERE & HAVING are "binary" conditions, i.e. they would either evaluate to TRUE (meaning the row/group remains) or FALSE (meaning the row/group is filtered out)2. The logical processing order evaluates this particular condition by group (i.e. uses the MAX(a.TheValue) for that group) a. This is because HAVING is evaluated later on (after the GROUP BY)I knew for sure about# 1, but #2 was a little too much to resist an experiment due to the use of the aggregation. I thus ended up running the query and confirmed my belief.

RE: HAVING EXISTS

hrvoje.piasevoli — Wed, 08 Sep 2010 01:45:01 GMT

Great question! Thank You Sir!Realy flexible, handy and readable method.

RE: HAVING EXISTS

Sree Arjun Div — Wed, 08 Sep 2010 00:41:13 GMT

Thanks For QuestionI learned Having Exists ...!

RE: HAVING EXISTS

Koen Verbeeck — Wed, 08 Sep 2010 00:35:21 GMT

Great question. I got it wrong, obviously, but I learned something.

RE: HAVING EXISTS

Stewart "Arturius" Campbell — Wed, 08 Sep 2010 00:22:37 GMT

Thanks for the question.Learned something

RE: HAVING EXISTS

UMG Developer — Tue, 07 Sep 2010 22:46:08 GMT

Great question Hugo, thanks!I wasn't sure if that was allowed or not, so I had to do some research and learned something new. Note that I can imagine where I need to apply it, but you never know. ;)

HAVING EXISTS

Hugo Kornelis — Tue, 07 Sep 2010 22:18:18 GMT

Comments posted to this topic are about the item [B]HAVING EXISTS[/B]