wvbdmp 9 hours ago

>subquery

>less verbose

Well…

In any case, it depends. OP nicely guarded himself by writing “overusing”, so at that point his pro-tip is just a tautology and we are in agreement: not every use of DISTINCT is an immediate smell.

Reply View 4 replies
  • Sesse__ 9 hours ago

    What do you mean? Here are your real alternatives for doing a semijoin (assuming ANSI SQL, no vendor extensions):

      SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE t2.x = t1.x );
  SELECT * FROM t1 WHERE x IN ( SELECT x FROM t2 );
  SELECT * FROM t1 JOIN ( SELECT DISTINCT x FROM t2 ) s1 USING (x);
    Now tell me which one of these is the less verbose semijoin?

    You could argue that you could fake a semijoin using

      SELECT DISTINCT * FROM t1 JOIN t2 USING (x);
    or

      SELECT * FROM t1 JOIN t2 USING (x) GROUP BY t1.*;
    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.)
    Reply View 3 replies
    • wvbdmp 8 hours ago

      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.

      Reply View 2 replies
      • Sesse__ 8 hours ago

        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.

        Reply View 1 reply
        • Little_Kitty 5 hours ago

          It's great being able to use an any join (and the counterpart anti join) in Clickhouse to deal with these operations.

          Reply View 0 replies