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

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: