Is HAVING clause redundant? Why?

sounds like homework, what have you tried?

I tried many things ranging from COBOL to LINQ.
I am expecting answers, though. Not counter-questions please.

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.

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.

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.

https://www.experts-exchange.com/help.jsp#hs=23&hi=21

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?

>>I can see no...
Try again!
"Think globally, act locally". Right?

>>> 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

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?

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?  

>>> 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"

>>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)

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

>>easy to intuit
Sorry, my intuition keeps silence since it does not have an idea about SQL optimizer.

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)

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?

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'"

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?

>>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.

It ooks like hard way is the one to go.

>>>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" ?

Do you know what "L" stands for in "SQL" abbreviation?
"Any" means ¿

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

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.

>>> 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

>>>>>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.

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.

>>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.

>>> 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.  

>>...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..."

>>... it would take more time than I'm inclined to provide here.
How right you are!

Could you please reopen the question?

True and true. Right :-)