Joins In Steps

[p2t2p]

I think about my self as quite SQL-savvy person, I used to optimize quite complex queries and is able to read plans for Oracle, Postgres and MySQL.

And yet, I've got not idea why would anybody need right join.

Have you guys ever had a case when you'd need a right join? I've been to the field for 15 years and yet to see people using right join in the wild.

Like the last example in this link - why would you do that? Most probably your business logic focuses on dogs, something like "find dogs with no owner" or something, In this case it is much more readable and straight forward to go with left join or even with sub-select where you'd have something like 'select * from dogs where owner_id not in (select id from owners)'.

Have you used right joins and if you have can you explain the use case?

[saulrh]

I think that I've almost used a right join before, but not for anything like a legitimate use case. I was attempting to out-think an in-house DB's optimizer that was doing a hash join in the wrong direction. There may have been some rough spots in some of that DB's specialized SQL extensions that played into the problem. I think I ended up using a full outer join plastered with optimizer hints. Like you said, if you're using a right join, probably not unlikely that you're doing something wrong.

[notafraudster]

I use right_join never in SQL, but often in offline data analysis (e.g. in R). Modern R has an operator called the "pipe" (%>%) which allows left to right evaluation of functions, and so it's fairly common to write code that basically reads like, say, chained method invocations in JavaScript (i.e. object.method1().method2().method3()). The operator works so that the invoking object is automatically passed as the first argument of the function, so func(x, y) is the same as x %>% func(y). You might see where this is going.

Think of left join as a join from x to y, and a right join as a join from y to x, where x is the data we keep all of and y is the data we keep only when there's a match.

Then, in R, I often use right joins when my "y" data requires preprocessing, resulting in lines of code that are like: (y %>% preprocess1() %>% preprocess2() %>% right_join(x)). I could of course write this as "y_preprocessed = y %>% preprocess1() %>% ...; x %>% left_join(y_preprocessed)" but I think the former is actually a little syntactically clearer.

[tempguy9999]

A right join is a left join flipped so you never formally need one, like you said.

I believe I used one once, exactly once ever, about a year ago. That was because I was fighting an almighty 5 page CTE charlie fox of a query and had to turn an inner join into an outer join to get data otherwise excluded. It just so happened the data missing was on the right so I made it a right join. Had it not been so awful I'd have flipped it into a left join, my usual style, but I couldn't bear rewriting it.

(background: it was complex but could not be broken down or it lost efficiency. Part of the length was long explicit lists of fields 'select a, b, c, d, ...' which were unavoidable, but it was just plain complex too. The inner join suddenly became an outer join because we found data missing - the DB was a commercial one written by some clueless people with little consistency and absolutely no documentation. I hope I never see that again).

[gav]

I struggle to think of time I used a right join in the 20-odd years I've used SQL.

The example shows a case where you'd want foreign keys to enforce referential integrity, though FKs aren't fashionable these days. You shouldn't be able to have an entry for owner_id 8 in the Dogs table without a corresponding Owner.

[paulddraper]

A right join is simply a commuted left join, so barring some bizzare DMBS optimizer oddity, there's no nothing you couldn't trivially replace with left join or vice versa.

I just checked some code, saw 357 left joins and 1 right join.

    SELECT p.password, u.id
    FROM password p
      RIGHT JOIN "user" u ON p.user_id = u.id

[replyguy912]

Agree on struggling for real scenarios where you'd use a right-join, but your example with

select * from dogs where owner_id not in (select id from owners)

You wouldn't actually recommend something like this would you? The query plans for sub-selects vs. left-joins are very different, with lots of implementations having limits on the "in" clause.

Kind of funny that the way you'd feel if you saw a right join is similar to how I feel about sub-selects!

[onlyrealcuzzo]

I'm by no means a SQL expert. But I just used a right join a few days ago (for the first time ever).

I thought it made sense to start the SELECT from one table. From there, I could left join to another table, and from that table I could only right join to get what I needed.

I'd love to know, is there another way around that? I know I could have started from a different table, but I think the query makes more sense starting from where I did.

[rtpg]

I really feel like we need a lower level expression language than SQL, one based on actual query plans rather than a declarative thing.

Having that, and helping people learn that, would make it much harder to not “get” how joins and the like end up working

[replyguy912]

We have SQL above that level and relational algebra below it; the challenge with your proposal is it's super-dependent on non-trivial implementation. Upper-level database courses deal with this, but in the same way compilier or operating systems do: you get to build functioning implementations but not realistic, performant ones. IMO opinion this is outside the scope of comp sci grad students or advanced undergrads not in a DB specialization.

[tempguy9999]

Perhaps, as a learning aid, and definitely to help fix erroneous query plans, but you'd lose the ability for the DB to adapt to new situations such as added data, data distributions changing, the addition/removal of indexes etc.

Also it's downright hard to make a good query plan for a nontrivial query, and it gets exponentially harder.

Honestly the optimiser is in a better position to do the work.

On re-reading I think you're talking only about pedagogy in which case disregard the list-of-cons in my post. And I think it would be a damn good idea! I really like it.

[contradictioned]

Would you also force users to decide on the implementation of the join operator? Because that is one of the benefits of a declarative query, as well as placement of other operators and join ordering if multiple relations are involved.

[obituary_latte]

That’s a typo in the last paragraph, no?

>The left right is the table named in the outer join part of the query.

[zindlerb]

Thanks for pointing that out. It has been updated.

[revskill]

Does SQL Join allow equality with operators like ilike, like,... or custom equality function ?

select * from a inner join b on mycustomfunction(a, b);

[hobs]

Generally any equality is fine, but you'd usually do something like on mycustomfunction(a) = mycustomfuntion(b)

And if you do this, it will be incredibly slow because you cant use any indexes in this case.