Support LQ: Use code LQ3 and save \$3 on Domain Registration
 Home Forums HCL Reviews Tutorials Articles Register Search Today's Posts Mark Forums Read
 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.

Notices

 11-04-2004, 08: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, 08:50 AM   #2
rjlee
Senior Member

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

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.