Right join! Huh! What is it good for?
-
@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.
-
@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
-
@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.
-
@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.
-
@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.
-
@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.
-
@blakeyrat said in Right join! Huh! What is it good for?:
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.
Sorry, I'm not familiar with what you're describing here.
-
I don't think I've got any right or full outer joins in the codebase. And I know a fair part of the left outers have the form
from a left join b on <condition> where a.foo='foo' and b.bar='bar'
.Remind me to verify tomorrow and educate the guilty with the QA lead's inflatable baseball bat.
-
It's so confusing, all that. Can't we have a simpler syntax?
select ... from a,b where a.id=b.id(+)
Ah, much better.
-
@boomzilla said in Right join! Huh! What is it good for?:
Sorry, I'm not familiar with what you're describing here.
tablea Right join tableb on ... Right join tablec on ... join tabled on ... Left join tablee on ... Left join tablef on ...
because hate.
-
@boomzilla said in Right join! Huh! What is it good for?:
Why? Do you mean designing a language with them or using them in code? I've been interpreting your posts as the latter, BTW.
Designing.
Look, never mind, obviously I'm not getting the message across here and I got work to do.
Boomzilla may now call me a stupid moron idiot at his convenience.
-
@maciejasjmj said in Right join! Huh! What is it good for?:
It's so confusing, all that. Can't we have a simpler syntax?
select ... from a,b where a.id=b.id(+)
Ah, much better.
DOWNVOTED FOR RAGE
-
@darkmatter said in Right join! Huh! What is it good for?:
@boomzilla said in Right join! Huh! What is it good for?:
Sorry, I'm not familiar with what you're describing here.
tablea left join tableb on ... left join tablec on ... join tabled on ... right join tablee on ... right join tablef on ...
because hate.
OK, yeah, there's no table aliases there, and that's kind of dumb, but just in an overly verbose way. What's the pyramid thing?
-
@blakeyrat said in Right join! Huh! What is it good for?:
@boomzilla said in Right join! Huh! What is it good for?:
Why? Do you mean designing a language with them or using them in code? I've been interpreting your posts as the latter, BTW.
Designing.
Look, never mind, obviously I'm not getting the message across here and I got work to do.
Boomzilla may now call me a stupid moron idiot at his convenience.
I'll pencil you in for later tonight. I have to go somewhere really soon.
I guess it would be weird if you were coding in a right to left language (didn't someone already allude to that?) but it makes sense for what it is, though I haven't given any serious thought to replacements.
What keywords would have been better?
-
@boomzilla oops, I listed the left and right joins backwards for the pyramid I'll have to fix that. the pyramid is that instead of putting the main table first and left joining it to everything, (or last and right joined), they build up to it and then back down from it, kind of like a pyramid.
Fixed here
tablea Right join tableb on ... Right join tablec on ... join tabled on ... Left join tablee on ... Left join tablef on ...
I think that's what Blakey was talking about anyway
-
@darkmatter Ah, OK. I was thinking of something more physical, like giant nested
if
s or whatever (wow, a blakey metaphor that I couldn't catch when it went over my head!). But yes, I think the "backwardsness" of a right join has been well enough covered in this thread.
-
@boomzilla or maybe he meant a nested query pyramid. But the thing I posted was the only thing I could think of that would be relevant to left vs right joins. Probably my fault for thinking it must be relevant.
-
@blakeyrat
http://sqlfiddle.com/#!6/24f1d/1SQL server has never supported
OUTER JOIN
- it can useLEFT JOIN
instead ofLEFT OUTER JOIN
, but if you just tell it OUTER, it doesn't know whether you wantLEFT
,RIGHT
, orFULL
-
Call me stupid all you want, but I completely fail to see how the fucking fuck are aliases supposed to help here.
-
@izzion said in Right join! Huh! What is it good for?:
N.B.: This link is only valid for the next 15 minutes, at most.
Signed: the Webmaster for Microsoft.com
-
@blakeyrat said in Right join! Huh! What is it good for?:
@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.
That's like saying you don't need both the
<
and>
operators, because you can always rewrite your expression to use only one.
-
@cartman82 said in Right join! Huh! What is it good for?:
@blakeyrat said in Right join! Huh! What is it good for?:
@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.
That's like saying you don't need both the
<
and>
operators, because you can always rewrite your expression to use only one.Ah ha! A new interpretation. It's having both of them that's moronic. This had not occurred to me when reading his posts.
-
Left and right joins are clearly a matter of order, and are basically the same join.
Has anyone ever encountered a genuine reason to use a full outer join?
-
@jaloopa said in Right join! Huh! What is it good for?:
Has anyone ever encountered a genuine reason to use a full outer join?
yes
and it made good use of coalesce() as well.
-
@darkmatter said in Right join! Huh! What is it good for?:
and it made good use of coalesce() as well.
Nothing wrong with
coalesce()
. Used that myself a few times. I've also usedstuff()
to good effect
-
@jaloopa said in Right join! Huh! What is it good for?:
Has anyone ever encountered a genuine reason to use a full outer join?
I've used it when doing something like a diff. Either the same table across different schemas or looking at the same table but two different "objects" contained in the table.
-
@darkmatter said in Right join! Huh! What is it good for?:
@jaloopa said in Right join! Huh! What is it good for?:
Has anyone ever encountered a genuine reason to use a full outer join?
yes
and it made good use of coalesce() as well.
I love me some coalesce().
-
@karla said in Right join! Huh! What is it good for?:
I love me some coalesce().
???
Filed under: Am I doing it right?
-
@masonwheeler said in Right join! Huh! What is it good for?:
@karla said in Right join! Huh! What is it good for?:
I love me some coalesce().
???
Filed under: Am I doing it right?
Maybe?
-
@masonwheeler She likes to "come together"
-
@jaloopa
Though, if you only have "use the value of the column, or this default value if the column is NULL", usingISNULL()
is slightly more performant thanCOALESCE()
in MS-SQL land, presumably because ISNULL is optimized for only NULL-checking the first option before just taking the second option, whereas COALESCE is set up to NULL-check one to many elements until it finds a non-NULL value.
-
@masonwheeler I use it when I have a block of similar queries. For example, one that returns matches, one that returns parents without children, and one that returns orhans. The table orders will run parallel, making it more obvious at a glance that they are related.
-
@izzion how many isnulls would you nest before acquiescing to the coalescing?
-
@darkmatter
I wouldn't nest any - if I have "give me a 0 in place of NULL for this one column", I'd use an ISNULL. If I have "give me the first non-NULL column out of an ordered list of columns, or a default value if they're all NULL", then I'd use COALESCE.
-
@izzion that's exactly how I use it. I've never looked into the performance, as nothing I've worked on has bottlenecked on it, but isnull conceptually is value or hard coded default, while coalesce is the first from this list with a value
-
@izzion said in Right join! Huh! What is it good for?:
@jaloopa
Though, if you only have "use the value of the column, or this default value if the column is NULL", usingISNULL()
is slightly more performant thanCOALESCE()
in MS-SQL land, presumably because ISNULL is optimized for only NULL-checking the first option before just taking the second option, whereas COALESCE is set up to NULL-check one to many elements until it finds a non-NULL value.You've actually tested this? How much is "slightly?"