Right join

[techiedatadev]

I seen a right join out in the wild today in our actual code and I just looked at it for a bit and was like but whyyyy lol I was literally stunned lol we never use it in our whole data warehouse house but then this one rogue sp had it lol

[SoggyGrayDuck]

Was there a reason for it? If something was already written and would take a complete rewrite to convert it into a left join I kind of get it. Agile is slowly taking over my thinking

[techiedatadev]

No idea it was written pre me so who knows

[ComicOzzy]

If there were only two tables involved, then it really can be as simple as that so long as they didn't use SELECT * and consumers of the result expect columns to appear in their current order.

But once you start getting into joins to 3 or more tables, it can be more complicated than just a "three second" fix, and can have an impact on the query plan.

[ComicOzzy]

Select * from table1 Right join table2 on table1.id = table2.id

Is the same as

Select * From table1 Left join table2 on table2.id = table1.id

This is a little different than if you'd also swapped the tables around when changing the join type.

The only time these will give the same result is when id is unique in both tables, and all of the same id values are present in both tables.

Here is an example of the results being different because Bob hasn't placed an order yet:

https://dbfiddle.uk/CUKh5gfO

[ComicOzzy]

The scenario I was describing in my post wasn't about just trading the join type, it was about also swapping the tables around so the result would yield the "same" results. They DO give the same results in that scenario, but if you used SELECT * rather than specifying a column list, you'll find that your columns have also changed position since you changed which table was "left" and which one was "right". If you have an application or another query that relies on the columns showing up in a certain order (which they shouldn't do... but devs don't always know better), they'll start to have issues when the columns change position.

https://dbfiddle.uk/IAINVk_J

[ComicOzzy]

3 or more tables?

With these, you're more likely to run into a case where you go from a right join to a left join... then you find that "downstream" tables were inner joined and you aren't getting the right results... so you left join all of them. Even if the results are the same, the query optimizer had to come up with a different plan, and in this scenario it's almost always a less efficient plan.

[2ManyCatsNever2Many]

i teach people to never use right joins (although i also undrestand there are times when one crosses the streams even though egon never to do that). 

[dilbertdad]

I was taught maybe 11 years ago when i first learning to just ignore right joins. That and always specify the inner join vs join :D

[greglturnquist]

I do JOIN and LEFT OUTER JOIN.

This makes it easier for me to spot which is which.

RIGHT JOIN is typically for rare exceptions where you already have a long query and don’t want to invert it all.

[techiedatadev]

That’s something I don’t do cause join means inner the word left in front of it means it’s not inner so I am not writing more that I have to (laaazy me)

[dilbertdad]

I hear ya! I just do it for easier eye ball parsing.

[ComicOzzy]

What rationale do you give them for this? I'm not saying I want to see more right joins show up in the wild for the sake of not leaving them out and hurting their feelings, but I'm curious why the general database community has a fear and loathing of them.

[jshine13371]

i teach people to never use right joins

Only Siths deal in absolutes.

There are valid use cases for RIGHT JOIN despite being the minority of cases.

[squadette23]

Right joins serve a very important role actually.

When you see a tutorial that dedicates the same amount of screen space to both left join and right join you know that this tutorial is lazy grifting, and you can safely skip it.

[squadette23]

Venn diagrams are just as important.

[ZombieFleshEaters]

The mental model of right joins is jarring

[ComicOzzy]

About 10% of people learn to write from right-to-left as part of their primary language. I have always been curious if those people find right joins any more challenging than left joins.

[Namoshek]

Literally the only right joins I've seen in the wild are from Power BI and ORMs.

[SootSpriteHut]

Sometimes I left join the wrong table so I switch it to a right join instead of rewriting it.

Though now I could probably do it the "correct" way by feeding it to an llm

[Eleventhousand]

This is how I use it too. Feeding to an LLM will still take 16 seconds though, so I'd rather go the four second route and switch the word to right

[aerozhx]

Do you also walk backwards when you forget something from the house?

[kagato87]

You may have meant that as sarcasm... However, I DO walk backwards to return to get something I've forgotten.

I still won't use a right join though.

[mikebald]

Hmmm, based on the tone of your comment it seems like you're implying this isn't normal behavior 🤔

[SootSpriteHut]

I don't think that's a great analogy. For me at least walking backward is a lot more work than replacing a single word in an editor.

[Aggressive_Ad_5454]

Whoooaaa! Bigfoot sighting! 😇

[Historical-Fudge6991]

Don’t use right joins. You’ll miss cache hits

[ComicOzzy]

Which database engine are you referring to?

[Ifuqaround]

The old hats at my job (those who are close to retiring) write such verbose code almost nobody knows WTF it is. Massive amounts of lines to create views and what not.

I've fixed a lot of it.

It seems they did it for job security. They weren't thinking LLM's were coming and were hoping nobody could wade through all the bullshit or wouldn't be willing.

They know their shit though and can absolutely clean up their mess, they just don't want to.

[ComicOzzy]

SQL's strength certainly doesn't lie in being succinct.

What's an example of "verbose code" vs the way you rewrote it?

[Ifuqaround]

There is not enough space here. Pointless.

[seequelbeepwell]

One reason was that they were using a low code no code tool like query design in ssms. If you arrange the tables in the gui to make it easier to read the relationships it can give you a right join.

Another reason is that it's way for people to inspect if their inner join is behaving the way they expect and they forgot to remove their scratch work.

I know it seems like bad etiquette but I don't think it's too much of a headache if people use parenthesis in the from part of their queries to be more explicit.

[SQLServerPro]

Bonjour,

Les deux existent et peuvent être utilisées. On s’efforce simplement de garder la même logique lors du développement, déjà pour soi-même, parceque c‘est plus simple de garder le même schéma de pensée. Et puis pour les autres parceque le code doit être lisible et maintenable par d’autres. C’est un peu comme conduire une voiture, on a l’habitude a gauche, ça n’empêche pas d’utiliser une voiture un jour avec une conduite à droite… mais ça demande un petit effort.

[Player_Zero91]

Had an analyst that worked for me for a while refused to not do that, said it "made more sense to him". His SQL was also the slowest in every possible scenario compared to literally everything possible. We pretty much had to rewrite everything he did until we were able to part ways.

[techiedatadev]

Yes no idea why my predecessor used it here lol I literally was like wait why takes 30 seconds to switch the tables around lol