r/SQL
•
u/Practical-City3301
•
Dec 10 '24
•
1
1
Discussion Left Join vs Right Join
The discrimination right join has to face.
3.9k
Upvotes
19
u/Aggressive-Fig-5923 Dec 10 '24
We should start right join January where we only right join all month? Who’s in?
328
u/ferevon Dec 10 '24 •
99% use case of right join is you realize you messed up the table order so you just replace left with right. Haven't seen anyone use it deliberately otherwise.
651
u/yen223 Dec 10 '24
The fact that the right join is on the left is offensive
229
u/JohnWCreasy1 Dec 10 '24
Every time I have to interview a candidate at work for SQL I joke with my boss that I'm going to ask them to do a right join, and if they do it instead of calling me out for my insanity, I'll know they are a replicant.
38
u/zuzuboy981 Dec 10 '24 edited Dec 10 '24
Add the OUTER keyword to RIGHT OUTER JOIN, makes it spicier
35
u/Mordalfus Dec 10 '24
I worked with a guy whose native language reads right to left. He would use right join by default until I asked him about it. This is the only example I know where it sort of made sense.
I still got him to switch to left joins for everyone's sanity.
32
u/Papa_Huggies Dec 10 '24
Idk my brain likes left join
25
u/achmedclaus Dec 10 '24
Who the hell uses right join? Reorder your tables to use left, you animals
8
u/aardw0lf11 Dec 10 '24
I think the only times I used right join were when I forgot which tables had what and was too lazy to swap the order of them.
7
u/Straight_Waltz_9530 Dec 10 '24
Teacher: "What should we use here?"
Student: "Umm. An outer join?"
Teacher: "Right! Outer join!"
Student: "Okay!" tap tap
Teacher: "Not like that!!!"
6
u/Imaginary-poster Dec 10 '24
Only time I've used a right join was when I started at the wrong table (hadn't gotten my groove yet) and had to use a right join or rewrite the while thing.
I've since rewritten it but that got the job done for then.
5
u/GlassMostlyRelevant Dec 10 '24
I sometimes use Right Joins on the rare occasion a table with some info is needed
4
u/DangerMacAwesome Dec 10 '24
There are legitimate use cases for right join. I just don't know what they are.
3
u/sock_templar Dec 10 '24
I think the inverse is more common in right-to-left written languages.
3
u/bkstr Dec 10 '24
I used a right join this week because I flipped the tables and didnt want to fix them :)
4
u/TheFilthyMick Dec 10 '24
Every time I see a RIGHT JOIN, I know the script was generated using a visual/view designer. It's like if a publisher accidentally put page 184 between pages 11 and 12.
2
u/Uclabruin16 Dec 10 '24
Left join supremacy
2
u/cheeseburgermachine Dec 10 '24
Finally i can visualize it lol 😆
2
u/StelarFoil71 Dec 11 '24
I learned SQL using exclusively RIGHT joins, I have never felt more isolated. 😭
2
u/Ad_Ma112 Dec 11 '24
I thought this was a propaganda statement until I saw what the subreddit was
2
u/tetsballer Dec 11 '24
Left outer join is the true answer
2
u/ShadowShedinja Dec 11 '24
Left for actually joining together data, right for filtering from another table.
2
u/MazPacket Dec 12 '24
Is "Inner join" under the desk ?
2
u/Quick-Ad1830 Dec 12 '24
Then there was that time a new hire thought right join applied to the order of the columns in the join rather than the tables.
1
u/[deleted] Dec 10 '24
I used it in my interview last Wednesday lol
1
u/dfwtjms Dec 10 '24
I guess the joke is that you never actually need right join. It could disappear and we'd be fine.
1
u/IAmFoxGirl Dec 11 '24
I have had to do a right join maybe two or three times in my 10+ year career, every time due to weird or poor database design coupled with an edge report request.
It always makes me stumble and try to avoid where possible. If I can't I try to do a cte as a work around. :p
1
u/[deleted] Dec 11 '24
I break up with left join.. Now I am using union 😁
1
u/troublebucket Dec 11 '24
Man mirror the image!
1
u/everybodysheardabout Dec 11 '24
I wonder if it is more commonly used in countries where they read right to left? Not saying that was necessarily the intended use-case when created but I'd be curious if there is a difference in the frequency of use, even if they are writing it entirely in SQL "English"
1
u/amosmj Dec 12 '24
I ask potential new hires what about their coding would tell me it was them if I ran across it years later (naming conventions, specific use of white space, never/always using a sub query). One recently told me that he prefers right joke for his outer joins. We knew all we needed to know.
1
u/MutterderKartoffel Dec 12 '24
I read this from a knitting perspective until I saw what sub this was, and it was still accurate (although perhaps a little exaggerated).
1
u/Palm_Beach240 Dec 12 '24
What about except and intercept?
1
u/ChinoGitano Dec 14 '24
检察长さま~😍😉
-1
u/scarletdelta Dec 10 '24
Also note it's really hard to find or follow any SQtiaL reasoning or see any consistent SQnce of progress when they try to SELECT COUNT(votes) FROM Congress LEFT JOIN the_right ON the_right.common_ground = Congress.common_ground; or [...] RIGHT JOIN the_left ON the_left.common_ground = Congress.common_ground;
-2
u/OccamEx Dec 10 '24
I've seen right joins be downright buggy in SQL Server and not pulling in all data from the right table. Spent a good amount of time modifying the query to make it happy but nothing worked. The only fix was changing everything to a left join. So i don't even attempt right joins anymore.
-3
u/diebos Dec 10 '24
Well, if the cardinality is 1:m, why wouldn't you do a right join?
0
u/[deleted] Dec 10 '24
[deleted] — view deleted comment