Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Joins In Steps

zindlerb.com

Post reply on HN
Print view View original

21–28 of 28 posts

Re: Joins In Steps

#21

Post by paulddraper »

p2t2p wrote: post #6

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? Mo…

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

Re: Joins In Steps

#22

Post by onlyrealcuzzo »

p2t2p wrote: post #6

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? Mo…

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.

Re: Joins In Steps

#23

Post by notafraudster »

p2t2p wrote: post #6

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? Mo…

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.

Re: Joins In Steps

#24

Post by lenzm »

onlyrealcuzzo wrote: post #22
p2t2p wrote: post #6

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? Mo…

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 st…

Any right join could be written as a left join. What you describe is the use case for a right join - it makes the query more readable.

Re: Joins In Steps

#25

Post by wodenokoto »

notafraudster wrote: post #23
p2t2p wrote: post #6

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? Mo…

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 firs…

I tend to do like

    x %>% left_join(y %>% preprocess1() %>% preprocess2())
But yes, my understanding is that right and left joins are the exact same function, just with the order of input switched.

Re: Joins In Steps

#26

Post by kec »

jodrellblank wrote: post #19
nothrabannosir wrote: post #12

Earlier quoted context omitted.

Would that come with the benefit of not getting nasty surprises when the cantankerous scheduler suddenly decides to mess with a previously efficient solution? Or even protect me from myself in the future accidentally breaking an efficient query through an ostensibly innocent change? I would appreciate a way to encode execution efficiency parameters in the semantics of the query itself. Specifying which indexes to use…

This talk ("How Modern SQL Databases Come up with Algorithms that You Would Have Never Dreamed Of by Lukas Eder" https://www.youtube.com/watch?v=wTPGW1PNy_Y ) is not particularly gripping, but he does claim that modern database engines will ignore indexes if they estimate it will be faster to read all the data than it is to access the index first then read the relevant data. And that Oracle can have multiple executio…

> Could this be a rare case of the mythical sufficiently smart compiler having more information at runtime than the programmer has at coding time [...]

Mythical? Isn't this the core concept behind optimizing JITs?

Re: Joins In Steps

#28

Post by p2t2p »

replyguy912 wrote: post #18
p2t2p wrote: post #6

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? Mo…

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 simila…

I would say "it depends". I have seen when replacing bunch of joins with sub selects improved performance. I have seen otherwise as well. It all depends on the nature of your data, RDBMS you're using and statistics it has accumulated.

I haven't seen anybody hitting "in" clause limits in query like mine but we did hit such a limit once when we were passing a hardcoded list of IDS from client side. Something like "where blah in (id1, id2, id3.... idn)". I remember that one failing on MS SQL.

Post reply on HN