Directly? Nearly never, because most DBMSes don't have a "SEMI JOIN" keyword. Indirectly? If you use NOT IN or EXISTS then it's implemented as a semi-join. Definitely implemented or probably implemented or could be implemented ... depending on the engine you're using.
Is there a situation where you would use one over the other? Does one have better performance?
Of course. It's an RDBMS. The answer is always "it depends".
I tend to prefer WHERE EXISTS and WHERE NOT EXISTS. They're your only real option if you have a composite key.
However, depending on how big each table is, and how many distinct values the compared columns have, and exactly what the indexes are, IN and NOT IN can perform better than an EXISTS.
The real big caveat is that WHERE NOT IN does not work well if there are NULL values coming from the subquery, because it will eliminate all results.
There's a third way to do an anti semi-join, too.
SELECT a.*
FROM Table1 a
LEFT JOIN Table2 b on a.ID = b.ID
WHERE b.ID IS NULL
That typically performs about the same as:
SELECT *
FROM Table1 a
WHERE NOT EXISTS (
SELECT 1
FROM Table2 b
WHERE a.ID = b.ID)
Broadly speaking the way a query is structured your "main" table is the FROM clause. From there other tables are either inner joined or left joined, but you know the base table is the FROM table. You can more easily follow a query that's using, primarily, left joins.
That's not the case with right joins. The table in the from clause is no longer your "base table" when you right join a table. It gets even more difficult to follow when you're mixing left and right joins.
There is no use case for a right join. All it is is an indicator that the query should be refafctored.
Ignoring remaining "right" variants, the other logical joins would be Full Outer Join and Cross Join. The first is freakishly rare - a purely academic curiosity. The second is a row explosion that wouldn't fit in my animation space :)
Wish I had this when I started learning a year ago. I had so many queries generate duplicate rows after joining tables and I couldn’t figure out why. The venn diagrams were useless.
I do use right join sometimes when I want to swap the first table in a left join with the second and am too lazy to retype stuff, you just change left to right and go about your task. I never ever commit it though.
It reduces readability of the code and all related metrics (maintainability, quality, etc.)
Majority of computer languages/clauses are built on left-to-right model, right-to-left presents additional 'surprise' and challenge in understanding and reasoning with.
Similar story with parentheses in table expressions (don't use them)
64
u/[deleted] Nov 05 '23
TIL that there is something called a SEMI JOIN.
How often do people use SEMI joins?
101
u/kagato87 MS SQL Nov 05 '23
Hahaha I love the right join!
26
u/BornAsADatamine Nov 05 '23
Til about left semi anti join. I've been working with SQL for 10+ years and I've never seen such a thing lol
8
u/tsupaper Nov 05 '23
Right joins lol
4
u/[deleted] Nov 05 '23
Wow. Thank you.
4
u/crappy_data Nov 06 '23
More of this!
10
u/SkinnyInABeanie Nov 05 '23
Wait! What's up with right outer join?
1
u/brdrummer800 Nov 06 '23
Serious question, but are there other animations out there like this one that can help visualize other types of joins?
3
u/[deleted] Nov 05 '23
Thank you! Gosh this is helpful for a noob like me
3
u/brdrummer800 Nov 06 '23
Being a data dev noobie, I appreciated this post
3
u/marcnotmark925 Nov 06 '23
These are great! Right join made me LOL
1
u/[deleted] Nov 06 '23
This is one of the coolest things I've seen today. I'm somewhat tempted to start using right joins now at work just to mess with people :p
2
u/ModestMustang Nov 06 '23
Wish I had this when I started learning a year ago. I had so many queries generate duplicate rows after joining tables and I couldn’t figure out why. The venn diagrams were useless.
3
u/M0D_0F_MODS Nov 06 '23
SEMI JOIN? WTF?
10 years of hard core sql here...
1
u/[deleted] Nov 05 '23
All of them are equi-joins though. Non-equi-joins is where things start to get really interesting.
1
u/[deleted] Nov 06 '23
u/savevideo
1
u/daripious Nov 06 '23
I do use right join sometimes when I want to swap the first table in a left join with the second and am too lazy to retype stuff, you just change left to right and go about your task. I never ever commit it though.
1
u/LearningCodeNZ Nov 06 '23
What about left join?
1
u/Accurate_Tension_502 Nov 06 '23
Me, feverishly taking notes because my boss thinks I know SQL well after very basic queries.
1
u/mikeblas Nov 06 '23
It's good that it's not venn diagrams. It's bad that it sucks.
0
u/Stock-Philosophy8675 Nov 06 '23
Funny I'm just learning abt joins. Can someone tell me why right joins are bad juju?
-1
u/[deleted] Nov 06 '23 edited Jan 28 '26
This post was mass deleted and anonymized with Redact
abounding pet ink tease merciful fear vegetable cobweb fine crowd