select id, count(*) - 1 as nduplicates
from parts where color = 'red'
group by id
having count(*) > 1;
WHERE cannot filter on the count(*) because WHERE is applied during the query before the count is known.I agree SQL is kind of warty, and it would be great if it were easier to compose from programs, but HAVING is not redundant to WHERE.
select * from (select id, count(*) - 1 as nduplicates
from parts where color = 'red'
group by id) _inner
where nduplicates > 0;
Now obviously that doesn't read as easily, but WHERE can filter out the count(*) perfectly.Two things I'd fix:
* make it explicit when a join is expected to have 1:1 semantics vs 1:n, because it's very easy to end up with duplicated rows otherwise
* increase composability of the syntax; why do we need HAVING, why isn't WHERE enough? We have relational algebra, projections, filters, joins, folds, union, intersect etc. But the syntax of SQL is so idosyncratic, and at times antagonistic to the stream and set-like intuitions behind relational algebra.
To do it right, you'd need to mark up the relations with expected cardinality (effectively unique constraints), so that the DB would be able to verify ahead of time whether a join is going to be 1:1 or 1:n. That would be a better solution than a join working fine up until the cardinality expectation is violated and it suddenly stops working.
If we had to stick with SQL syntax, it might be something like:
select a.*, b.*
from a
join one b on b.id = a.b_id
(Unique constraint from primary key on b.id. It may still filter down the set of rows in a, but it won't duplicate.)Or:
select a.*, b.*
from a
join many b on b.some_key = a.some_key
(No unique constraint on b.some_key)I think the interesting cardinality distinctions are 1:1, 1:n and 1:0.
(The above syntax is ambiguous with aliases, so it wouldn't fly as is. But it gives a flavour.)
What I was saying was that often people have T1::T2 (1-1) and then throw in a later join T2::T3 (1-many) without considering the implications for the T1 (maybe they were doing a count(T1.id)). At least that seems to be an aspect that confuses developers newer to sql (that the many join is duplicating other parts of the data).
Think something like measurements samples that come in an envelope with metadata, or account updates that come in a statement, that kind of thing.
We played with wrapping our dB layer with graphql recently and I would say that the experience was pretty good. In part it’s because you’re working a layer up where you can have a more pluggable model. In the first run you can model your local dB structure, but it’s easy to glue more stuff into it from other sources and generally evolve the structure.