No, sorry, you’re certainly correct, I just meant that any subqueries are generally crazy verbose. And then you usually want additional Where clauses or even Joins in there, and it starts to stop looking like a Where clause, so I’m often happy when I can push that logic into From.
Yes, I would certainly prefer if you could write
SELECT * FROM t1 SEMIJOIN t2 USING (x);
although it creates some extra problems for the join optimizer.
Re:SQL Anti-Patterns You Should Avoid
by Little_Kitty on Saturday October 18, 2025 @04:04PM
(#45630005)
It's great being able to use an any join (and the counterpart anti join) in Clickhouse to deal with these operations.
You could argue that you could fake a semijoin using
or but it doesn't give the same result if t1 has duplicate rows, or if there is more than one t2 matching t1. (You can try to fudge it by replacing * with something else, in which case the problem just moves around, since “duplicate rows” will mean something else.)