Right join! Huh! What is it good for?


  • Impossible Mission - B

    @masonwheeler said in The bad jokes topic 🐴🍹👨:

    A SQL query walks into a bar, goes up to two tables, and asks, "mind if I join you?"

    I shared this joke with my brother, and he remarked, totally deadpan, "So did he join them on the left or the right?"

    I asked him, "have you ever seen anyone ever use a right join?" And he said he has, and that he's used them from time to time, though he couldn't think of any examples. Which I find strange, because I don't recall ever having seen a use case for it.

    Can anyone think of a time when they've been in a situation where a right join was the right tool for the job?



  • @masonwheeler "left" and "right" are moronic. There's only "inner" and "outer". Fortunately, MS SQL Server doesn't require the moronic direction.



  • @masonwheeler said in Right join! Huh! What is it good for?:

    Can anyone think of a time when they've been in a situation where a right join was the right tool for the job?

    I was doing some ad hoc querying just the other day! I was changing my existing query to look for something else and I was too lazy to reorganize the tables so I did use a right join.

    @blakeyrat said in Right join! Huh! What is it good for?:

    @masonwheeler "left" and "right" are moronic. There's only "inner" and "outer". Fortunately, MS SQL Server doesn't require the moronic direction.

    I don't think I've ever typed either inner or outer. The lack of left / right indicates it's inner and vice versa. I pretty much always use a left join because it's nice to easily know what's going on when you always do certain things in the same way. Unless, as I said above, I'm being lazy and just poking around at data.


  • SockDev

    @masonwheeler I habitually always use a left join, and cannot ever remember writing a right join. It's not they execute any different: all it changes is the order you list the tables in.



  • @boomzilla Imagine if addition in C# was like:

    var = c LEFT + b;

    That's retarded; addition's commutative. But guess what? SO ARE SQL OUTER JOINS!



  • @blakeyrat said in Right join! Huh! What is it good for?:

    That's retarded; addition's commutative. But guess what? SO ARE SQL OUTER JOINS!

    But the point of a left vs right outer join is that you're specifying which table's value doesn't have to exist.



  • @masonwheeler said in Right join! Huh! What is it good for?:

    Can anyone think of a time when they've been in a situation where a right join was the right tool for the job?

    Yes, but only because the database engine sucked. The only difference between left and right joins is the order of the arguments.



  • @boomzilla said in Right join! Huh! What is it good for?:

    I was doing some ad hoc querying just the other day! I was changing my existing query to look for something else and I was too lazy to reorganize the tables so I did use a right join.

    Oh, and I partly did it because it amused me expressly because I never use them.



  • I just did a search on our code base. In thousands of stored procedures there are less than 20 that use it.

    They all fall into the following scenarios

    • Query was built using the query builder
    • Query was initially an inner join needed to be changed to an outer join and that was the order of the tables (and the program--looks like me in most instances--and didn't bother switching the order of the table**)

    ** Bad programme, bad ❗


  • SockDev

    @blakeyrat said in Right join! Huh! What is it good for?:

    "left" and "right" are moroni

    maybe. they are required for doing things like reporting on third party data where the third party app helpfully decides not to write records in an ancillary table unless there is non-(null|zero|blank) data in at least one field. if you don't use a left join when querying sata off that table you'll lose records....

    okay, that's countering moronic with "moronic" but hey, it works and i'd like to see you come up with a better way to make a query like the one i just described.



  • @boomzilla said in Right join! Huh! What is it good for?:

    But the point of a left vs right outer join is that you're specifying which table's value doesn't have to exist.

    The result's the same, so why does that matter?

    If the order of the tables in your query matter, that means you're not using table aliases, aka. you're doing it wrong already.



  • @karla said in Right join! Huh! What is it good for?:

    In thousands of stored procedures there are less than 20 that use it.

    LOL....just grepped my code and reports and found no right joins.

    reports: 895 left joins
    main app: 662 left joins



  • @accalia said in Right join! Huh! What is it good for?:

    maybe. they are required for doing things like reporting on third party data where the third party app helpfully decides not to write records in an ancillary table unless there is non-(null|zero|blank) data in at least one field. if you don't use a left join when querying sata off that table you'll lose records....

    That doesn't require "LEFT OUTER", just "OUTER".

    My point isn't that outer joins are never useful; that would be a retarded thing to say. My point is that putting a direction on your outer joins is stupid. Which is true and factual.


  • SockDev

    @blakeyrat said in Right join! Huh! What is it good for?:

    That doesn't require "LEFT OUTER", just "OUTER".

    but i don't want records on the right side that don't match the left.

    blah blah blah FROM table_a A LEFT JOIN table_b B ON condition

    is better than

    blah blah blah FROM table_a A OUTER JOIN table_b B ON condition AND A.id IS NOT NULL



  • @blakeyrat said in Right join! Huh! What is it good for?:

    @boomzilla said in Right join! Huh! What is it good for?:

    But the point of a left vs right outer join is that you're specifying which table's value doesn't have to exist.

    The result's the same, so why does that matter?

    If the order of the tables in your query matter, that means you're not using table aliases, aka. you're doing it wrong already.

    I don't think you're understanding the distinction. The table alias thing especially doesn't make sense here.

    So...you have one table, let's call it TABLE_A. And you have that in your query now. Next, you're going to include TABLE_B in your query.

    If you want records from TABLE_A but don't care if there are any in TABLE_B, then you might end up with (I'll even use aliases!):

    select *
    from TABLE_A
    left join TABLE_B b on [some join condition]
    

    However, if you wanted to make sure you got a record from TABLE_B but were OK with TABLE_A's row not existing, then you'd do this:

    select *
    from TABLE_A
    right join TABLE_B b on [some join condition]
    

    Obviously, you could reverse the order and go back to using a left join, which is what an outer join defaults to. But you couldn't replace left and right in those queries with outer and get the desired results without doing other stuff, too.

    0_1503426592543_712f4b5a-f170-44d5-b4ff-f39421762d08-image.png



  • @boomzilla said in Right join! Huh! What is it good for?:

    Obviously, you could reverse the order and go back to using a left join, which is what an outer join defaults to

    Huh, thought it was full outer by default. I always just write LEFT OUTER JOIN out of habit anyway.


  • :belt_onion:

    @masonwheeler I've used right join before... But it's always because a query already existed and needed to have something changed and I didn't want to reorganize it to fit left joins.

    Also, some languages go right to left, maybe they are also more comfortable right joining 😉



  • @maciejasjmj said in Right join! Huh! What is it good for?:

    Huh, thought it was full outer by default

    Oh, yeah, I think you're probably correct. Ugh...so using blakey's method means more work and more chances for mistakes. It is the 1970s of joins.



  • @boomzilla I understand the distinction and I understand that OUTER is interpreted as LEFT OUTER, I'm saying that using "left" and "right" as keywords in a computer language is moronic and stupid.

    The only time you'll see a RIGHT OUTER and it'll make sense is if people are doing joins using that horrible pyramid syntax which sucks and everybody hates instead of using table aliases.



  • @blakeyrat said in Right join! Huh! What is it good for?:

    I'm saying that using "left" and "right" as keywords in a computer language is moronic and stupid.

    Why? Do you mean designing a language with them or using them in code? I've been interpreting your posts as the latter, BTW.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.