SQL: The difference between WHERE and HAVING
sql-bits.com
sql-bits.com
I swear to god whenever I search for something in the python standard library I have to scroll past a bunch of sites like this before i find the actual docs.
> It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses. The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions. (Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at the WHERE stage.)
Thank you for linking this, it's very concise and actually helpful.
https://wikidiff.com/spam/spam
https://www.codegrepper.com/code-examples/python/frameworks/...
EDIT2: replaced statement of fact about WHERE/HAVING with this message.
It may be time to admit that general purpose search engines are fundamentally broken and that the only useful way forward is subject-specific search that allows users to be in control of their results.
I've been saying that since I first heard someone pitching SEO while eating lunch. Once you start gaming an index for visibility, Tragedy of the Commons goes into full swing, and the Yellow Pages ironically become a truer representation of actual local state. To this day, the memory of that...individual just going on and on casually talking pitching about what is no less than intentional pollution of the search structure of the collective knowledge of human still causes a hot nugget of incandescent rage and disgust deep within my soul. Before now, the thought of forgiving them has never entered my mind before now, and in this minute is quickly dismissed. I didn't know the person, they were themselves probably just trying to get by best they knew how. But I just can't seem to let it go. Poisoning the well like that... It either requires the most appalling sense of shallow callousness, profound ignorance, or a complete disregard for the integrity of our species' collective informational output. It's the burning of the Library of Alexandria by way of Salesman vying for attention. As time goes on the disgust only becomes becomes more pointed as the practice is normalized and refined to the point the way information is organized and made accessible on the Internet makes a bigger statement on what society really values than the indexed content itself does.
I'd normally say live and let live, but I'm getting increasingly bothered by the suffering and extra cognitive load foisted on the world by ads, marketing, etc..., and the lack of societal wherewithal to push back and say enough is enough.
Anyway, this rant brought to you by Libraries! Support your local branch today! Strike a blow for open access!
The bloggers themselves initiate the issue, and I don't see how subject-specific engines could completely solve that. Why would they be able to distinguish this article from a well written entry on a SQL topic that is not just a restatement of fairly basic knowledge? Docs don't cover everything, there's space for articles like this that follow the same basic structure but with more useful information, a and once people can infer a little bit about the search engine's algorithm from how it shows results then we're back to the SEO arms race.
But I wouldn't go as far as to say that the person who writes the website is not the problem. The content in these sites is usually borderline spam.
The thing is, documentation is (usually) written very dense, with a lot of assumptions about knowledge. The PostgreSQL documentation on WHERE vs HAVING is decent (https://www.postgresql.org/docs/8.1/tutorial-agg.html), but the MySQL documentation (https://dev.mysql.com/doc/refman/8.0/en/select.html) is ... a lot to read and not very explanatory for someone who only rarely deals in SQL.
Documentation writing is an art and most projects don't have the resources to have actually working documentation, much less understandable documentation - which is part of the reason why w3schools, stackoverflow, quora and random SEO "blogs" get so much traffic.
I think your problem is with search engines and not the writers of these articles, who probably don't consider you to be the target audience.
(This is not a comparison between the languages, just an oddity about the docs)
I created special "search engines" on my browser, so I start typing ":p" and whatever I write next it automatically searches using the site:python.org filter.
The standard library docs have their own search. Plus there is always help()
Preferably, SQL would look more like:
FROM Foo f
WHERE f.value < 10
JOIN Bar b on b.id = f.bar_id
WHERE b.other_value > 20
SELECT f.group, f.value
GROUP BY f.group the_group AGGREGATING SUM(f.value) the_sum
WHERE the_sum > 100
ORDER BY the_group
It also cross-compiles to SQL and can thus be used to build queries, although the result is not necessarily the most efficient. Someday I hope someone with expertise in compilation and query optimization will take a look at dplyr and figure out how to get it to generate optimally efficient SQL.
If we did that, and also ported dplyr to some other languages, programmers might never have to hand-write SQL again. (LINQ might be a good way too, but I know much less about it)
[1] https://scholar.google.com/citations?user=YA43PbsAAAAJ&hl=en...
Dplyr was already great in its first version, but Hadley and company have worked incredibly hard to make it much, much better. It's hard to think of another system that has improved so much from such an already high starting point.
It was a fine language for its time. So was Pascal, and FORTRAN. We don't use those anymore generally for a variety of good reasons.
SQL ANSI nulls alone are a good-enough reason to toss it. Any language where "x = x" commonly returns an object that is treated as falsey is broken.
edit: I'm getting "You're posting too fast" so I can't continue this conversation, but no being able to express "unknown" is not worth throwing out the reflexive property of equality. That is a bad trade-off and I will die on this hill.
Nah. One of the most common uses of null is to communicate missing values in the sense of "this entity has this attribute but we don't know what its value is". This is a fundamental feature needed in data management.
So say you've got data on two balls A and B. You don't know what the color of either ball is. Is color(A) = color(B)? What's the best answer to this question?
Obviously it's "we don't know", and the value we use to communicate "we don't know" is null.
SQL nulls aren't broken. They're just a third option outside of True/False that needs its own handling, because reality is complicated.
PS: Contrary to popular opinion in certain coding circles, floating point arithmetic isn't broken either. It's a beautiful, delicate tool that works the way it does because it must in order to support its use cases (e.g. numerical linear algebra and representing continuous physical quantities). If it doesn't work exactly the way you want, you may need a couple guardrails or a different system like fixed point arithmetic.
The correct way to represent this, which has been widely used for several decades now, is sum types. If your value might be unknown, you need to explicitly represent this in the type of your value (e.g. Optional<Foo>) instead of relying on a hack that allows subverting the type system.
Optional<Foo> lends itself well to functional programming and prevents things like null pointer errors, but that's still at 3GL level, the EXPLAIN level. SQL doesn't throw null pointer exceptions. Ever. The query syntax is correct or it isn't. The same concerns in a programmatic language simply do not apply, which is why Optional<Foo> is a poor fit in this context.
Null in SQL is not the null in JavaScript or C++. Same name, but not the same animal, and I think this is what trips folks up. In your mind, replace SQL's null with a new keyword, UNKNOWN, and it makes more logical sense.
SELECT ... FROM ... WHERE x = UNKNOWN;
Even if x were UNKNOWN in a row, is it the same unknown? For example, if you see two girls, but you don't know their birthdays. Their birthdays are unknown.
WHERE Keasha.birthday = Cynthia.birthday
This partial statement would always be false no matter what until you know both of their actual ages. Just because something is unknown doesn't mean it's the same unknown.
Set theory, not a programmatic series of steps as found in most languages. And that's what SQL is: a DSL for set theory.
I believe it's advantageous to avoid nullable columns in relational schema design, but that's a separate issue from the concept of NULL in SQL as distinct from programmatic languages.
Agreed, but they should be used where appropriate. My favorite example of a nullable column is an end date. Until an end date occurs, it's unknown - thus null. Representing the unknown end date with anything other than null is semantically incorrect, and in many cases can lead to errors.
I don't know, and would love to know, why databases have been so slow to adopt this.
That's what makes SQL null-values distinct - the fact that expressions that return booleans can return NULLs.
And even if it were a good idea, leaving the user with no built-in operators, not even a simple COALESCE(bool) to properly manage the null complexity? That's unacceptable.
Given a SQL boolean expression `f(b)` that can return true, false, or null, how do I test if `f(b)` has returned NULL? How do I handle that NULL case?
ANSI SQL gives users a strange new paradigm for boolean algebra and then says "GLHF" and gives you zero tools to handle the situation, not even linguistic features that would let you roll your own boolean-based functions, since you can't write SVFs that take boolean parameters.
I mean this trashboat of a language doesn't even let you bind boolean to a variable! You have to deal with the PITA of converting from Booleans to bits because those are totally different things.
Many languages have a null/undefined of some sort, but they are often simply coerced to false. But, for a language whose sole purpose is to manipulate data the null value nearly always has meaning.
> Given a SQL boolean expression `f(b)` that can return true, false, or null, how do I test if `f(b)` has returned NULL? How do I handle that NULL case?
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Section 8.6, the null predicate.
`where f(b) is null`
> ANSI SQL gives users a strange new paradigm for boolean algebra and then says "GLHF" and gives you zero tools to handle the situation.
It's also not a strange new paradigm, and is in fact built on foundational topics. Relational algebra and set theory are not new concepts.
AFAIK MySQL, Oracle, and SQL Server don't have the Boolean datatype as a variable/column type - only Bit, which lacks boolean semnatics, and so Booleans are not considered data values, and therefore can't be used with IS NULL or COALESCE.
Notice that databases, with their 3-valued logic, are written in a systems language like C/C++, a general-purpose programming language with boolean logic.
Some systems are best thought of using non-boolean logic. Quantum systems are another example. You can simulate just about anything with a Turing-complete language, but it doesn't mean that standard computation with boolean logic is the best way to think about every domain.
Surely if it were beneficial you'd see more people doing it, but I have literally never encountered software outside of SQL that does it. I could probably implement it in C++ or C# in an afternoon using enums and operator overloading, at least for the boolean-boolean operators. A little longer to make comparison operators for the other basic types that would yield nulls when compared to null. But nobody does this. Why?
Do you see SQL as some weird little edge case of data management? 99.9% of all serious data management happens in a database server with a SQL interface. Maybe other languages don't implement 3-valued logic because they don't need it, because they don't need to do data management, because they'd be bad at data management.
It's not even true that languages outside SQL don't have 3-valued logic. They may not have it built into the base language, but analytics packages like Python/pandas and R certainly have some version of a null or NA, and nobody would question the value of it for a second. And databases are typically implemented using C/C++, so there's another example of someone using a general-purpose programming language to implement 3-valued logic (as part of the SQL implementation that is written in C/C++).
It's not like SQL is some weird separate language on a different planet as your favorite general purpose programming languages. It's implemented with those languages. So there's your 3-valued implementation right there. When the feature is valuable, it's implemented, and not before.
I rarely use nulls like that. And then only for things outside of SQL databases like lazy loading or caching. And even then I still prefer null == null to be true.
If nulls are for what you have written then why we are calling them "null" - no value? Why not calling them "unknown". Even then what is benefit in introducing non boolean logic to a relational query language? I have never seen anyone using any of three-valued logic benefits when writing SQL. Every time I get unknown logical value it means I have made a mistake in my query, which would be better handled by rising an error.
Well, what can I say? Other people need them, and I illustrated why.
> And even then I still prefer null == null to be true.
Some databases have alternate operators, like <=>, that make null == null true. And you can always coalesce null to a sentinel value if your dialect doesn't have this operator. Heck, if you're writing SQL using templates, you could have the template language replace a <=> b with coalesce(a, 99999999) = coalesce(b, 99999999) (or whatever other sentinel value you want to use).
For example, AVG(col) and COUNT(col) treat it as a missing value (NULLs do not alter the average or the count), but 1 + NULL treats it as an unknown value (the result is unknown). The complete list of NULL-related inconsistencies would be long.
It would have been better to use different words for different semantics, or not implementing NULL at all. Also, in some cases it can lead to poor query performance.
I mean, we hear over and over again when learning SQL "NULL bool and FALSE are different things"...
But the SQL engine itself treats NULL and FALSE as equivalent all the time.
DECLARE @x INT = NULL
SELECT 'foo' WHERE @x = 1
COALESCE(x = y, false)
“bit” is not a standard SQL type at all, it's an SQL server proprietary 1-bit numeric type. boolean is an standard SQL type, but SQL Server doesn't support it as a storage type or consistently elsewhere (it does exist, annoyingly, in some contexts, but is limited in where it or expressions producing it can appear, which—not any limitation of standard SQL—is the source of your problem.)
> and you can't just say COALESCE(x = y, false)
In something that supports standard SQL in the relevant parts, like Postgres, yes, you absolutely can.
In SQL Server, you have to do something clumsy like:
CASE
WHEN x=y THEN 1
ELSE 0
END
There are plenty of valid criticisms of standard SQL around NULLs, but the particular brokenness of SQL Server’s handling of booleans is not one of them.
SQLite has a similar problem, averts the practical because it doesn't use booleans natively internally in which they apply, instead natively using 0 and 1 (with TRUE and FALSE as aliases) and then converting to boolean in the narrow set of boolean contexts. This means that can use COALESCE as described (and, generally, SQLite will behave as you would expect with real Boolean support, even though it doesn't actually have it.)
DB2, from what I can find, supports BOOLEAN from v11.1.
In any case, the problem here is not a standard SQL problem, even if it is a problem of several popular SQL implementations.
SQL boolean operators can only result true or false, there isn't a third option.
NULL is a data value for all data types, which happens to return 'true' to the boolean operation 'is null', and 'false' to any other boolean operation. NULL = NULL or NULL = 1 don't return a third-option NULL, there is no such thing, they return a plain old 'false'.
(NULL = NULL returns false because otherwise it would make outer joins terrible)
As you pointed out, SQL does not implicitly convert boolean values (bits) to or from the result value of boolean operators. Bits are values and can be NULL. Boolean results are not values and can only be true or false.
Now, is this optimal? Hell no. (Sum types would probably be the way to go if SQL were designed 50 years later.) But it's consistent.
What...? Go type this into your favorite DB and get back to me:
SELECT TRUE AND NULL
What is the difference? Try this:
SELECT CASE WHEN (TRUE AND NULL) THEN 'A' ELSE 'B' END
Now "TRUE AND NULL" is a boolean condition, and it will return false, and the boolean conditional will evaluate to 'B'. If SQL had three-way logic, some expressions inside the parentheses would make the whole conditional return NULL, but it will always return either 'A' or 'B'.
I just wish they'd ignored them in one more spot: equality checking.
Every time someone tries to replace it, it eventually comes back again. Just look at the recent NoSQL and Big Data craze, and then how we're today with a miriad of SQL engines.
However, I would add that SQL is a very strong local optimum: a "good" language that keeps the good parts would be insufficiently different from actual SQL to convince anybody to switch.
Also people forget that querying is only half of the equation, there's DDL too.
The only reason I can think of where you often can’t strictly treat DQL and DDL as totally different languages is the just from the total lack of interest in consistency in SQL language design generally — eg update and delete steal parts of DML syntax haphazardly and differently; the syntax for update/delete data referring to another table
UPDATE table WHERE … JOIN …
DELETE FROM table USING …
Outer joins inherently require a concept of absence of data, so you'd need a built-in Optional type which would be nullability in all but name. And you'd want lifting of operators on the inner type to the optional type for ergonomic reasons; doing a pattern match for every selection of bar.* where bar is left-joined would be extremely painful. I think you'd end up where you started on the null front.
Sum types generally, for columns etc., maybe (pun not intended), but I'm skeptical about complex types in a relational language - structure ought to be represented as relations where possible so that everything else composes.
Seems like an attempt to throw the baby out. 3-valued logic anyone?
Most dynamically typed general-purpose languages use a large-number-of-values logic, they just use a different formulation than SQLs 3VL. Particularly, nulls tend to be equal to each other, and only propagate from basic boolean ops where false would, not whenever they are an input. (Though IEEE 754 NaNs, which most use and are another flavor of null-like value, are not equal to each other, and, in the case of quiet NaNs, propagate a lot like SQL nulls through arithmetic operations [though not consistently through other language features].)
As found in a few general purpose languages.
Hell, JavaScript has true, false, null, and undefined. 4-value logic!
The real world upon which we model our code has a "true", "false", and "can't tell yet; not enough info". As inconvenient as it feels psychologically, you can't deny the fundamental reality of the third, "can't tell yet", state.
In JavaScript, null == null yields true. "Test" == null yields false. In SQL, those expressions yield null. SQL is afaik unique in its belief that algebra involving nulls should yield nulls instead of boolean values. That's what's meant by "three value logic", and that's what I find problematic. It violates several mathematical rules, like the reflexive property of equality.
I understand your position, but your idealism would be strictly worse in real life. Can you imagine if an optional foreign key relation immediately made all joins fail because one was unset? Or if a join between two optional values made all NULL values join together?
The SQL NULL behavior also allows UNIQUE nullable columns to have more than one instance that is NULL, so you can have uniqueness over a column that may also be unset.
If you're a purist or an idealist, SQL NULL might be a mess, but pragmatically, it's the only behavior that is sane for SQL as-is.
Really? The basic operations of boolean algebra or AND, OR, and NOT.
In JavaScript, what is:
1. null && true
2. false || null
3. !null
Well, one of them produces a boolean result, but... Actually, JS has basically an infinite valued logic, because of its truthiness/falsiness dichotomy and the rules around how the “boolean” operators preserve truthy or falsy inputs; only ! of the core boolean operators always returns a boolean which is why !! is equivalent to Boolean().
Like, this is a language where "X OR NOT X" is not true if X = null
JS, for all its wonky truthiness rules, still satisfies the basic premise that "X || !X" yields something truthy.
Most other languages treat NULL as a distinct-but-normal value. IMO this isn't better or worse than SQL, or rather, the SQL approach is better for querying in SQL, whereas the approach common in general purpose programming languages is better more generally, but forces more special case handling.
You have vastly underestimated the number of values in JS’s logic.
So, any language using IEEE 754 floats is broken?
Err, that will include any language that implements IEEE floats.
From(foo) |>
Where(Get.value .< 10) |>
Join(From(bar) |> As(:bar), on = Get.bar.id .== Get.bar_id) |>
Where(Get.bar.other_value .> 20) |>
Select(Get.group, Get.value) |>
Group(Get.group) |>
Where(Agg.sum(Get.value) .> 100) |>
Order(Get.group)
JOIN Bar b
The need to spell them out again and again is one of my personal SQL pet peeves.
GROUP BY is totally the opposite of what you expect. It NOT "group by"!,
This is group by:
1:
A
B
2:
A
C
I learned this (and most of my understanding of SQL) from https://blog.jooq.org/10-easy-steps-to-a-complete-understand... (point 2.), which was recommended by Hadley Wickham (of R tidyverse/dplyr fame).
SQL is not a great language. Serviceable, but not great.
test=# create table event (who text not null,
test(# what text not null,
test(# at timestamptz not null default now());
CREATE TABLE
test=# insert into event (who, what)
test-# values ('Bob', 'sent a message'),
test-# ('Alice', 'received a message'),
test-# ('Alice', 'sent a message receipt'),
test-# ('Bob', 'received the message receipt'),
test-# ('Bob', 'waited patiently for a response'),
test-# ('Bob', 'worried whether his message had been well-received'),
test-# ('Bob', 'paced'),
test-# ('Alice', 'forgot to reply for two weeks'),
test-# ('Bob', 'began to panic'),
test-# ('Bob', 'fled the country for Moldova in a fit of panic'),
test-# ('Alice', 'finally remembered to reply'),
test-# ('Bob', 'never got it');
INSERT 0 12
test=# select who as "The poor sod",
test-# count(what) as "Events"
test-# from event
test-# group by "The poor sod"
test-# order by "Events" desc;
The poor sod | Events
--------------+--------
Bob | 8
Alice | 4
(2 rows)Snowflake supports it - https://docs.snowflake.com/en/sql-reference/constructs/havin...
Redshift doesn’t: https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clau...
Databricks doesn’t: https://docs.databricks.com/sql/language-manual/sql-ref-synt...
All of them do also support teradata’s QUALIFY clause which is like another layer over WHERE and HAVING that filters windowed data.
In theory¹ a DB that supports skip-scanning could use feedback from some HAVING clauses to short-circuit such a scan significantly, for instance if HAVING COUNT(*)<4 is present and a fourth row for that grouping comes up it can discard the row and skip to the next value. This would likely only be useful for indexes containing large numbers of the same few values though.
[1] I'm not aware of any engine that does this. It might be that the time cost of assessing if the optimisation is worthwhile² far outways the sum benefit over time of the saving when it is actually found to be worthwhile.
[2] Similarly, writing+testing+maintaining the code to do so might not be good use of dev time compared to other improvements
I will post the link later, if necessary in a comment.
SELECT x ... HAVING y => SELECT * FROM (x ...) t WHERE yOne inner σ (WHERE) and one outer σ (HAVING).
If subqueries are allowed, then you don't need that.
This phrase returns departments where the number of female employees is <10 right, rather than listing female employees from departments that have <10 employees in total?
SELECT department_id, count(*) AS employees_no FROM employee WHERE gender = 'F' GROUP BY department_id HAVING count(*) < 10;
FROM Employee
WHERE gender = 'F'
GROUP BY department_id
HAVING count(*) < 10
SELECT department_id, count(*) as employees_no
Except some databases allow for that syntax. The one we use, SQLAnywhere, does.
select category, sum(amount) as total
from mytable
group by category
HAVING total > 100
select category, total
from (
select category, sum(amount) as total
from mytable
group by category
) as mysubquery
WHERE total > 100I usually find CTEs to be a relatively tolerable middle ground of readability and performance (although I don't run from HAVING, either):
with category_totals as (select category,
sum(amount) as total
from mytable
group by category)
select category,
total
from category_totals
where total > 100;
Subqueries are indeed a performance red flag when the query is returning more than a few rows AND the subquery references fields outside of the subquery. Or put more simply, a subquery that will need to be executed an unreasonable number of times per run.
But so long as you aren't doing anything like that, and your database engine has a competent query planner, there should be zero difference in performance. For this reason I don't consider subqueries to be a code smell. For me a query is "smelly" when it does things which aren't obvious to a human parsing the query with their brain.
I've managed software teams and data engineering teams, and both teams get tripped up with even moderate SQL queries. To simplify, we encouraged teams to use a clearer subset of SQL. Most HAVING can be replaced with a WHERE inside a more readable and explicit subquery. Similarly, we got rid of most RIGHT JOINS.
[0] https://jvns.ca/blog/2019/10/03/sql-queries-don-t-start-with...
But seriously, this concept doesn't seem to require as much attention as this post gives it.
/thread
With no other language I have faced so many gotchas with implicit conversions and unexpected behaviors.
Cornell has a good explanation: https://www.cs.cornell.edu/projects/btr/bioinformaticsschool...
If you want to take a peek at an older revision: http://web.cecs.pdx.edu/~len/sql1999.pdf (I'm guessing this will be another case of be careful what you wish for).