LinuxQuestions.org Question: How to define an SQL HAVING clause in relational algebra?
 Linux - Software This forum is for Software issues. Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

 11-04-2004, 07:32 AM #1 jdruin Member   Registered: Jul 2003 Location: Louisville aka Derby City Distribution: WinXP SP2 and SP3, W2K Server, Ubuntu Posts: 313 Rep: Question: How to define an SQL HAVING clause in relational algebra? I noticed an SQL query I ran produced different results depending on whether I used a WHERE clause or a HAVING clause. I believe the WHERE clause is defined in relational algebra as a predicate that must return true for the record (tuple) to be returned to the user. I am not sure but I have always thought of the predicate as being in the form of a symbolic logic syllogism. Based on what the HAVING clause does (filter), it seems that it would be expressed in relational algebra as a predicate as well. However, when a WHERE clause is used rather than a HAVING clause, the resulting row set (tuples) can be quite different. Do you know how to express HAVING in relational algebra in such a way that the relational algebra description will be unambiguous from the WHERE clause definition? I would like to know why WHERE and HAVING behave differently. Example: Problem statement: Select only negative values from field some_number, sum those negative values, then group the sums by period. Returns the "expected" results SELECT period, SUM(some_number) FROM some_table WHERE some_number < 0 GROUP BY period Using HAVING causes the SQL interpreter engine to select and SUM the records before considering the HAVING clause, resulting in positive numbers being added together with the negative numbers in the result set. The result is then returned as long and the total sum of number is negative. SELECT period, SUM(some_number) FROM some_table GROUP BY period HAVING some_number < 0
11-08-2004, 07:50 AM   #2
rjlee
Senior Member

Registered: Jul 2004
Distribution: Ubuntu 7.04
Posts: 1,994

Rep:
Not in relational algebra (I probably could sit down and work it out but I don't have time at the moment). See http://www.postgresql.org/docs/7.4/s...ql-select.html for a formal definition of HAVING, WHERE and other SQL clauses.

It basically comes down to when you apply the filter, before aggregation (WHERE) or after (HAVING). The relevant paragraph is:
Quote:
 HAVING eliminates group rows that do not satisfy the condition. HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP BY, while HAVING filters group rows created by GROUP BY. Each column referenced in condition must unambiguously reference a grouping column, unless the reference appears within an aggregate function.

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Bluesuperman Programming 4 02-01-2005 09:49 AM Slovak Linux - Software 0 11-30-2004 05:46 PM h/w Programming 7 12-03-2003 05:14 PM gdbugger General 5 10-23-2003 06:54 PM markus1982 Programming 1 09-26-2003 12:27 PM

LinuxQuestions.org

All times are GMT -5. The time now is 11:07 PM.

 Contact Us - Advertising Info - Rules - Privacy - LQ Merchandise - Donations - Contributing Member - LQ Sitemap -