3 ms·
If you don't like EXISTS, the other standard way to get a semijoin is using IN with a subquery. SELECT * FROM a WHERE x IN (SELECT y FROM b);
by Sesse__ 3y ago
If you don't like EXISTS, the other standard way to get a semijoin is using IN with a subquery. SELECT * FROM a WHERE x IN (SELECT y FROM b);
- meinaccount00 3y agoI've been working with Spark lately and just beeing able to write "semi join" (or "anti join") is quite nice and readable
- Sesse__ 3y agoExplicit semi- and antijoin operators would be a nice addition to the language, but do note that they present some additional challenges for the optimizer. In particular, when the join condition becomes explicit, it can take on more forms than before, which can limit the reorderings allowed or allow new ones. E.g. you now need to deal with this structure: (a SEMIJOIN b ON a.x=b.y) JOIN c ON b.z=c.z This was an impossible structure before, since IN and EXISTS both hide b's columns (all semi- and antijoins effectively come last), and your optimizer will now need to know whether e.g. this associative rewrite is allowed or not: a SEMIJOIN (b JOIN c ON b.z=c.z) ON a.x=b.y Also, you'll need to deal with LATERAL semijoins, which you didn't before… None of this is impossible, but there's more to it than just a small syntactic change.
- magicalhippo 3y agoJust to be sure to check the resulting plans. The DB we use usually has sub-optimal plans when using IN compared to EXISTS, often quite drastically so.
- Sesse__ 3y agoYou always need to check the resulting plans if you care about execution performance, that's just how life is. I'm offhand a bit surprised IN does worse than EXISTS; I can understand NOT IN being slow, because it has very surprising NULL handling that is hard to optimize for.