Is HAVING clause redundant? Why?

In ANSI SQL for any given SELECT statement having HAVING clause another SELECT statement exists that (1) does not contain HAVING clause and (2) is semantically equivalent to the given one.
True or false? Why?

In ANSI SQL for any given SELECT statement having WHERE clause another SELECT statement exists that (1) does not contain WHERE clause and (2) is semantically equivalent to the given one.
True or false? Why?

Thanks. I am with SQL for 10+ years.
LVL 1
midfdeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
sounds like homework, what have you tried?
midfdeAuthor Commented:
I tried many things ranging from COBOL to LINQ.
I am expecting answers, though. Not counter-questions please.
peter57rCommented:
We can't do homework here; it's against EE rules.

And I think the key issue is the 'ANSI SQL' qualification.  Very few people on this site will be bothered about the theoretical capabilities of ANSI SQL , they are only interested in practical matters relatiing to the SQL dialect being used for their development.

If I can solve a problem in my version of SQL then I have no interest in whether or not my solution conforms to ANSI standards.
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

midfdeAuthor Commented:
The question is simply applicable to any SQL (ANSI compliant) dialect like JET, TRANSACT SQL, Oracle etc. Therefore "they" should be interested in ANSI SQL anyway. Some of the dialects allow an option that requires ANSI compliance.
Which of the rules does my question violate? Did I mention anything about homework?

>>If I can... I have no interest...
Well, then this question is not for you. Just ignore it. Perhaps other experts may have read something like "Advanced ANSI SQL" books.
peter57rCommented:
We all have (read the books).

Perhaps you'd like to put the question into context then.  I can see no practical reason for such a question.
midfdeAuthor Commented:
Ha! I am too old for any homework...
The brevity of the question is just an attempt to avoid additional questions. It's me who is the author of the questions. Are they too simple? Or what? If it is the case please just give me a pertinent reference. The question BTW is of practical meaning for my work. Can a SQL generator ignore existence of HAVING clauses?
midfdeAuthor Commented:
>>I can see no...
Try again!
"Think globally, act locally". Right?
sdstuberCommented:
>>> Can a SQL generator ignore existence of HAVING clauses?


yes it "can",  "should it"?  no, and not just for convenience of those reading the sql, but for performance reasons
midfdeAuthor Commented:
Thanks, sdstuber, I'll take it as "True" for the first of my questions.
What about the second one?
Could you please elaborate on performance issue? Links? References?  Benchmarks?
sdstuberCommented:
all you need is one case to make it false, so start simple


select * from sometable where somecolumn =1


is there anyway to acheive the same filtering functionality without using a where clause?  

sdstuberCommented:
>>> Could you please elaborate on performance issue? Links? References?  Benchmarks?

not really, but it's fairly easy to intuit

any filtering condition can be replaced by another filtering condition that is a subquery, in some cases it might be a trivial and nonsensical but still functionally valid


having x > 1
   replaced by
where exists (select null from somedummytable where x>1)


but, others may be more complex.

having count(somecolumn) = max(somecolumn)
   replaced by
where (select count(somecolumn) from sometable)  = (select max(somecolumn) from sometable)

it should be obvious that additional queries to the same table and associated io and other overhead will be more expensive than simply using the count/max results of the initial data pass.
There may be exceptions, but they are "exceptions"


midfdeAuthor Commented:
>>is there anyway to acheive the same filtering functionality without using a where clause?  
Yes, there is

begin tran
select 0 as somecolumn into #sometable
insert into #sometable select 1
--
select * from #sometable
group by somecolumn
having somecolumn = 1
--
rollback

Results:
(1 row(s) affected)

(1 row(s) affected)

somecolumn
-----------
1

(1 row(s) affected)
sdstuberCommented:
not good enough in generic case, yes, for some specific data and tables it might be possible, but not in general


sometable has 2 rows,  both with somecolumn=1


select * from sometable
group by somecolumn
having somecolumn = 1


First off, if sometable has more than one column, this will be a syntax error.
Second,  if sometable does have only the one column, this will return one row,  not two.  So it doesn't really mimic the functionality
midfdeAuthor Commented:
>>easy to intuit
Sorry, my intuition keeps silence since it does not have an idea about SQL optimizer.

midfdeAuthor Commented:
begin tran
select 0 as somecolumn, -99 as anothercolumn into #sometable
insert into #sometable select 1, -98
insert into #sometable select 1, -97
select * from #sometable
group by somecolumn, anothercolumn
having somecolumn = 1
rollback


(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
somecolumn  anothercolumn
----------- -------------
1           -98
1           -97

(2 row(s) affected)
sdstuberCommented:

Yes, it's possible to create scenario's where it is possible.  But seriously,  who cares?

Your question, is for "any" sql,  so you tell me.  If by "any" you mean, there exists at least one sql where you can reverse having/where then yes, but that's trivially true, you already knew it, so why ask?
If by "any" you mean "all", then no, I've already demonstrated one counter case which is sufficient.


as for the other comment....

I didn't mention any optimizer.

It was simply a matter of io.  If you query data twice, when you could query it once, then it's less efficient.

I suppose, in an abstract sense you could say that a really smart optimizer, with arbitrarily large resources could decipher the results of implied secondary queries from the results of initial queries.
However, that's such a large assumption as to be relatively useless.

If you care about efficiency then you must be working within the context of real systems or at least theoretical systems with realistic limitations.

What is the nature of your question?  What is it you're really trying to determine?


midfdeAuthor Commented:
I am trying to reduce query generator complexity(highest priority) keeping in mind performance of resulting query (high priority).

>>What is it you're really trying to determine?
That's simple.
==
In ANSI SQL for any given SELECT statement having WHERE clause another SELECT statement exists that (1) does not contain WHERE clause and (2) is semantically equivalent to the given one.
True or false? Why?

==
Not "any SQL" but ... see the question.
"Any" means whatever query, not language, that is syntactically correct, and that are so complicated (with multi-nested correlated subqueries, full joins and other weird things) that they might not come to my mind.
"Why" means "Please provide something more convincing than just 'yes' or 'no'"
sdstuberCommented:
That doesn't answer my questions,  sorry.  I don't know how to answer what you're looking for.



>>> "Any" means whatever query, not language,

where did anyone ever mention languages? that might hint there was confusion on this point?

As for the "why" part,  not funny.  what are you trying to do?
midfdeAuthor Commented:
>>where did anyone ever mention languages?
right here:
>>Your question, is for "any" sql
Wrong!
===
>> I don't know how to answer
That's too bad. Thank you for trying though.
midfdeAuthor Commented:
It ooks like hard way is the one to go.
sdstuberCommented:

>>>where did anyone ever mention languages?
>>>right here:
>>>>>Your question, is for "any" sql
>>>Wrong!

huh?  how did you make the connection between queries vs languages from that phrase?

My question is what you do you mean by "any"  that's your word choice not mine.  But as simple as a 3-letter word is, it's meaning is ambiguous as you have used it.

Grammatically:  "any" could imply 1 query would be sufficient to show it is possible to have a having replaced with a where, and a where replaced with a having.
But, you already knew an example of each where it was possible.  Or, if you didn't, then it was trivial to derive and hence not really a question.  So,  it seems like that's probably not what you were asking for.

So, I have to assume "any" is a mistaken word choice, and you really meant "all"  In which case I've given you the complete answer.
NO - it is not possible to write ALL queries with WHERE as a functionally equivalent HAVING (or other not filtered queries), my one example was sufficient to prove that.
As for HAVING rewritten as an ALL... that's quite simple to illustrate.


select * from ( some query with values, including aggregates or not)
WHERE blah,blah,blah  --- replace this where with whatever the having clause was, substituting nested aliases as appropriate.


However,  what is possible and what is a good idea aren't the same thing.  You asked about efficiency, I already gave one example of when replacing HAVING with a WHERE would be a bad idea.
I'm sorry you didn't like that example, but efficiency only applies to constrained resources and those constraints are what make them possible.

If my assumption is wrong and you didn't mean "any" to mean "at least 1" nor did you mean "all" but instead meant  "some, more than 1",  then yes, it's probably possible, and I'll leave it as an exercise to derive as many examples as you need; but I don't see the point.

This is my quandary.  I think your question, as asked, has been answered,  it's your followup-comments that have confused me.


Since you accepted a post, apparently I was right, so Why the B?   If there was something missed, please explain.  What is the "hard way" ?




midfdeAuthor Commented:
Do you know what "L" stands for in "SQL" abbreviation?
"Any" means ¿
midfdeAuthor Commented:
"Any" meanas what inverted A does in math logic, just a single one of ALL.
midfdeAuthor Commented:
A query is a statement (SELECT) in a certain (formal) language (ANSI SQL).
SELECT DISTINCTROW ...
may be a query in JET SQL, but not in Transact SQL language or in ANSI SQL for that matter.
sdstuberCommented:
>>> Do you know what "L" stands for in "SQL" abbreviation?  

Yes, of course - why do you keep throwing out stuff like this which is, purely argumententive?
I, like everyone else here am a volunteer,  I'm trying to help you because I want to, but I won't continue to want to, if this is the way the conversation will go.

>>> "Any" meanas what inverted A does in math logic, just a single one of ALL.

If you're only interested in the existence of at least one, then you got your answer, 1 - yes and 2 -yes.  
Proof by examples are above.  Since your answers are complete, please explain the B grade.  Nothing is missing.


>>> may be a query in JET SQL, but not in Transact SQL language or in ANSI SQL for that matter.

I have no idea why you're talking about JET SQL and Transact SQL



midfdeAuthor Commented:
>>>>>Your question, is for "any" sql
This phrase does not leave any doubts that you are talking about "any" Structured Query Language
You say "any" of a set of languages, then you are surprised:
>>how did you make the connection between queries vs languages from that phrase?

That's why I am asking if you forgot what SQL originally means, because I was talking about one language, namely ANSI SQL, and any, meaning every of all, query in this language.

Further on, the questions were "True" or "False". Were they not?. "Why" was indeed an euphemism for "prove it". As we all know an example may refute a hypothesis (this is where intuition helps), not to prove it's truthfulness. Similarly, testing may prove bug's existence, and cannot prove that a program is bug-free.  I realize formal proof does not pertain here, so I ask for "something more convincing" than a couple of examples, leave alone just "True".
I thank you anyway for fruitful discussion, and I am sorry if you are disappointed with my evaluation, which in my view is pretty just.
Can you tell me whether I can change it and how?
Best regards.

sdstuberCommented:
ah,  I understand now.  This is funny...

You thought I was talking about languages, when I was talking about queries.  I was NEVER (despite your assumption that it leaves no doubt)  discussing languages,  but queries only.

My use of "sql" above is in the common shorthand for "sql statement", not for the language itself.

I think the Why/Prove it part was well understood.
And, I think the examples discussed above, work both ways.
There are examles that prove there exists at least one sql statement/query such that the assertions are true.

And to prove there exists at least one sql statement such that the second assertion is false.

As for the first assertion,  replacing HAVING with WHERE  i think the pseudo-code showing the inline view demonstrates a generic means of replacing HAVING with a WHERE clause.  I do not have a formal proof that the inline view technique will always work so as to prove always true; but I can't think of anything that would make it not work.

If you want to change the grade, I can reopen the question for you since I am a zone advisor,
or you can click the Request Attention link and a moderator will assist you.

However, it's perfectly acceptable to leave it a B as long as you can explain what is missing.
midfdeAuthor Commented:
>>exists at least one sql statement such that the second assertion is false.
Which one? Could you elaborate? I am under opposite impression.

>>...explain what is missing.
"True" or "False" wording prior to last posting, and "Why" are missing. All the rest is just fine.
...and "...I can't think of anything" is not good enough for me at this point.
sdstuberCommented:
>>> Which one? Could you elaborate? I am under opposite impression.

sometable has 2 rows,  both with somecolumn=1, there are no other columns, or the other columns have identical values.

select * from sometable where somecolumn =1

any attempt to using having requires group by and group by will act as a distinct thus creating different results,  you attempted to disrpove this one earlier with a counter example but your counter example added extra criteria thus creating a different scenario and didn't really apply.



>>> and "...I can't think of anything" is not good enough for me at this point.

but you said you weren't looking for a formal proof.  
midfdeAuthor Commented:
>>...but your counter example added...
So what? What in the original questions makes you think that "added" or not is relevant in this consideration?

>> ...you weren't looking for a formal proof.
There is a lot in between "I can't think" and formal prove, such as "It was pointed out / proved in ... by,.. see..."
sdstuberCommented:
>>> So what? What in the original questions makes you think that "added" or not is relevant in this consideration?

because "at least one" is sufficient. I gave an example,  you gave a "different" example in an attempt to counter it.  But, because it was different it fails to do so.

I've already agreed it's possible to create at least one example where it's true.  You explicitly created one example to reinforce it, fine, but since that wasn't a contested point, who cares?

If you wish to counter my example, that's fine, but if you alter it so as create additional possible e scenarios, your addtional "trues" do not prove the absence of my one "false".  Since my assertion was there is at least one false and I've shown it, it doesn't matter how many additional trues you can demonstrate.  They are irrelevant.


>>>There is a lot in between "I can't think" and formal prove,

yes, but I thought I gave that in http:#37235525

if you want something more formal than the inline view extraction and explanation, I don't think I can provide that.  For one, it would take more time than I'm inclined to provide here, and, more importantly, ANSI SQL is a fuzzy definition, "formal" proof doesn't really apply.  Not only are there multiple standards across time creating ambiguity, within each publication  some details are left to the specific platform implementations.  So, unless you are willing to step away from an abstract ANSI SQL and discuss specifics of what is and is not supported in each platform it's not possible to say for sure exactly what the results of predicate expansion will be.

having [some condition] on a query

"should be" equivalent to

where [some condition]  applied to an outer query on an inline view of the original query without the having clause.

I started writing up a quasi-formal proof, but once I got 8 paragraphs in and hit the boundary of platform implementations I deleted it as pointless.

Maybe that, failure, is itself the answer you're looking for.
Without exhaustive testing I can't prove it, but I think it's hard to argue that it would be impossible to find an ansi-compliant implementation that fails on at least one query.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
midfdeAuthor Commented:
>>... it would take more time than I'm inclined to provide here.
How right you are!

Could you please reopen the question?
midfdeAuthor Commented:
True and true. Right :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.