Right join! Huh! What is it good for?
-
@boomzilla
I haven't explicitly tested, it's a thing that's in the coding standards here. I've done a little searching here and there on theStack OverflowsInternets, and "slightly" is the only real thing I recall finding - I don't recall seeing any sort of percentages or relative times.So, it might just be cargo cultage
-
@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.Interesting: I've always read it's the other way around.
-
@darkmatter said in Right join! Huh! What is it good for?:
acquiescing to the coalescing
I like the phrase. We can be friends again.
-
I'm assuming that one for one, ISNULL might win but if you have multiple nested ISNULLs, COALESCE probably wins.
-
@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 Right join tableb on ... Right join tablec on ... join tabled on ... Left join tablee on ... Left join tablef on ...
because hate.
Holy crap TIL you can start joining things before you specify the base table!
Warning: head explosion eminent!
-
@tsaukpaetra said in Right join! Huh! What is it good for?:
Warning: head explosion eminent!
Better put your helmet on !
-
@karla said in Right join! Huh! What is it good for?:
@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().
My Little Pony: Equestria Girls - Time to Come Together [1080p] – 02:07
— RainbowDash32
-
@tsaukpaetra said in Right join! Huh! What is it good for?:
Holy crap TIL you can start joining things before you specify the base table!
Warning: head explosion eminent!
-
@blakeyrat said in Right join! Huh! What is it good for?:
@tsaukpaetra said in Right join! Huh! What is it good for?:
Holy crap TIL you can start joining things before you specify the base table!
Warning: head explosion eminent!Aha! See @darkmatter, I was right! Also, I had forgotten about that thread.
-
@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 do all the time. Whenever I want to compare tables in two instances (say Dev and UAT) and get all records that are different or exist in only one or the other, I use full joins.
SELECT * FROM ( SELECT key, value FROM dbDev.table) dev FULL JOIN ( SELECT key, value FROM dbUAT.table) uat ON dev.key = uat.key WHERE dev.key IS NULL OR uat.key IS NULL OR dev.value <> uat.value
Edit: 'd by boomzilla
-
BTW, I never say
OUTER
for any of my joins. My list of join operations includeINNER JOIN
,LEFT JOIN
,RIGHT JOIN
,FULL JOIN
, and,
(comma join).
-
@djls45 said in Right join! Huh! What is it good for?:
BTW, I never say
OUTER
for any of my joins. My list of join operations includeINNER JOIN
,LEFT JOIN
,RIGHT JOIN
,FULL JOIN
, and,
(comma join).Same. It's a redundant keyword, and let's be honest, it's the
LEFT
/RIGHT
/FULL
that's the important bit.
-
@boomzilla me too....
I'm sad they only built up the pyramid on one side and didn't LEFT JOIN their way back down the other side
-
TL;DR: As an implementation of Codd's rules, SQL ranks just below a game of 52 card pickup with a deck of Hollerith cards. Obvious statement is obvious, but doesn't change that fact that most of us are stuck with it unless we are willing to throw relational properties away outright (or deal with what is likely to be an incomplete implementation of mini-Kanban in whatever language we're using), nor does it mean that just because something is a bad idea doesn't mean it isn't useful in practice (right up until it becomes a show-stopping problem that takes a year of refactoring to fix).
And people wonder why I get depressed from dealing with this shit...
-
@djls45 said in Right join! Huh! What is it good for?:
comma join
If you ever use that your SQL is bad and you should feel bad
-
@jaloopa said in Right join! Huh! What is it good for?:
@djls45 said in Right join! Huh! What is it good for?:
comma join
If you ever use that your SQL is bad and you should feel bad
SELECT ones + 10*tens + 100*hundreds AS nbr FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) o (ones), (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t (tens), (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) h (hundreds) WHERE ones + 10*tens + 100*hundreds BETWEEN 42 AND 288 ORDER BY hundreds, tens, ones
-
@djls45 that's what
CROSS JOIN
is for
-
@jaloopa But that requires at least 11 more characters per join than a comma join does, and it means exactly the same thing.
-
-
@djls45 said in Right join! Huh! What is it good for?:
@jaloopa said in Right join! Huh! What is it good for?:
@djls45 said in Right join! Huh! What is it good for?:
comma join
If you ever use that your SQL is bad and you should feel bad
SELECT ones + 10*tens + 100*hundreds AS nbr FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) o (ones), (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t (tens), (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) h (hundreds) WHERE ones + 10*tens + 100*hundreds BETWEEN 42 AND 288 ORDER BY hundreds, tens, ones
I thought to myself, "No fricking way..."
Then I did it...
Status: Pondering if this is a suitable replacement for that numbers table...
-
@tsaukpaetra said in Right join! Huh! What is it good for?:
@djls45 said in Right join! Huh! What is it good for?:
@jaloopa said in Right join! Huh! What is it good for?:
@djls45 said in Right join! Huh! What is it good for?:
comma join
If you ever use that your SQL is bad and you should feel bad
SELECT ones + 10*tens + 100*hundreds AS nbr FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) o (ones), (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t (tens), (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) h (hundreds) WHERE ones + 10*tens + 100*hundreds BETWEEN 42 AND 288 ORDER BY hundreds, tens, ones
I thought to myself, "No fricking way..."
Then I did it...
Status: Pondering if this is a suitable replacement for that numbers table...
Is
generate_series(42, 288)
not good enough?
-
@ben_lubar No such critter in T-SQL.
-
@unperverted-vixen said in Right join! Huh! What is it good for?:
@ben_lubar No such critter in T-SQL.
Write a function that generates SQL that does that, then!
-
@ben_lubar said in Right join! Huh! What is it good for?:
@unperverted-vixen said in Right join! Huh! What is it good for?:
@ben_lubar No such critter in T-SQL.
Write a function that generates SQL that does that, then!
Be my guest!
-
@boomzilla said in Right join! Huh! What is it good for?:
What's the pyramid thing?
Maybe
SELECT * FROM ( SELECT id, Foo FROM ( SELECT * FROM BAR ...
-
@scholrlea said in Right join! Huh! What is it good for?:
what is likely to be an incomplete implementation of mini-
KanbanKanren in whatever language we're using)FTF my own dumbass self.
Also, using a logic-constraint toolset like for mini-Kanren to implement a jackassed version of relational constraints, while entirely possible, is usually Doing It Wrong unless you have no choice - or have a way to fully encapsulate it such that very little of the underlying mechanism leaks. Mind you, that was part of my point, but I doubt I got it across because I was being too snide and slipshod to even notice my mistake earlier.
-
@jaloopa said in Right join! Huh! What is it good for?:
@djls45 said in Right join! Huh! What is it good for?:
comma join
If you ever use that your SQL is bad and you should feel bad
There have been times I've needed values in a single query from two tables that did not have a relationship in the schema. I can't recall the specifics of how I was able to link the information I needed together in a useful manner, but it happened. Once.
-
@abarker That's known as a Cross Join, I think. I've used it once or twice, but it's not a common thing at all.
-
@masonwheeler You may be correct. I've used so infrequently, I've never really looked into it that much.
On a related note, I found this SO answer that has a pretty good visual for the different types of joins (except for cross).
-
You complicators and your academic solutions. You Think joins are so great, but did you ever think about ACTUAL developers who will have to maintain your code? You've just left a mess that no one will be able to read.
Instead of being clever and mixing all the tables up together, ONE table at a time.
Select everything from one table. Clean, simple.
Then use a cursor... a loop. Select from the second table using only one variable at a time from first table. Easy to read. Easy for other developers to understand
-
@lorne-kates
@Polygeekery, do you rent out your arsonist services? I have a potential target that needs enlightening...
-
@izzion said in Right join! Huh! What is it good for?:
@lorne-kates
@Polygeekery, do you rent out your arsonist services? I have a potential target that needs enlightening...I'm no database guru, but even I know when nuking it from orbit is the only way to be sure. This is one of those times.
-
@izzion said in Right join! Huh! What is it good for?:
@lorne-kates
@Polygeekery, do you rent out your arsonist services? I have a potential target that needs enlightening...It is more a labor of love than a service. As such I always feel bad when I charge. Also, I do have @Lorne-Kates home address so I could make it happen.
-
@lorne-kates
On a more serious note, I wish there was some sort of "for dummies" setting in SSMS / SSDT that would just reach out of the computer monitor and slap .NET developers with "you don't want to do that, you dummy" whenever they use looping logic instead of set based logic for their queries.SQL is designed for and requires set-based operations (even MySQL). If you want to iterate through your data, pull it all into a caching layer in the web tier and loop it there -- looping it in the SQL server is just going to make Baby Jesus (and your SQL server) cry
-
@lorne-kates 10/10 All-pro trolling
-
@tsaukpaetra said in Right join! Huh! What is it good for?:
@djls45 said in Right join! Huh! What is it good for?:
@jaloopa said in Right join! Huh! What is it good for?:
@djls45 said in Right join! Huh! What is it good for?:
comma join
If you ever use that your SQL is bad and you should feel bad
SELECT ones + 10*tens + 100*hundreds AS nbr FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) o (ones), (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t (tens), (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) h (hundreds) WHERE ones + 10*tens + 100*hundreds BETWEEN 42 AND 288 ORDER BY hundreds, tens, ones
I thought to myself, "No fricking way..."
Then I did it...
Status: Pondering if this is a suitable replacement for that numbers table...
Status: was looking for this the other day but search wasn't helpful as I didn't recall any specific terms. Thanks for the 5th upvote, @Captain!
-
@tsaukpaetra
SELECT 42+LEVEL FROM DUAL CONNECT BY LEVEL < 246
-
@pleegwat said in Right join! Huh! What is it good for?:
@tsaukpaetra
SELECT 42+LEVEL FROM DUAL CONNECT BY LEVEL < 246
-
@pleegwat said in Right join! Huh! What is it good for?:
@tsaukpaetra
SELECT 42+LEVEL FROM DUAL CONNECT BY LEVEL < 246
That requires getting Oracle on you...
-
Select row_number() over (order by object_Id) From sys.objects a cross join sys.objects b
-
@jaloopa The Evil Ideas Thread is .
-
This topic reminds me of this one time when I needed
FULL OUTER JOIN
s in Access and ended up doing this instead:FROM ( ( ( SELECT dtDate FROM ( SELECT TableA.dtDate FROM TableA UNION SELECT TableB.dtDate FROM TableB UNION SELECT TableC.dtDate FROM TableC ) GROUP BY dtDate ) AS Dates LEFT JOIN TableA ON Dates.dtDate = TableA.dtDate ) LEFT JOIN TableB ON Dates.dtDate = TableB.dtDate ) LEFT JOIN TableC ON TableB.dtDate = TableC.dtDate
-
Technically, every inner or outer join can be expressed in terms of an inner join or a full outer join:
full join from inner join(Edit: It's not easy to duplicate rows for null-to-null matches.)--full join from inner join SELECT * FROM ( SELECT MAX(TableA.Key) KeyA, TableB.Key KeyB FROM TableA INNER JOIN TableB ON TableA.Key = TableB.Key OR TableA.Key IS NULL GROUP BY TableB.Key UNION ALL SELECT TableA.Key KeyA, MAX(TableB.Key) KeyB FROM TableA INNER JOIN TableB ON TableB.Key IS NULL GROUP BY TableA.Key ) Full_Join_Table
left join from inner join (Edit: fixed)
--left join from inner join SELECT TableA.Key KeyA, MAX(TableB.Key) KeyB FROM TableA INNER JOIN TableB ON TableA.Key = TableB.Key OR TableB.Key IS NULL GROUP BY TableA.Key
right join from inner join (Edit: fixed)
--right join from inner join SELECT MAX(TableA.Key) KeyA, TableB.Key KeyB FROM TableA INNER JOIN TableB ON TableA.Key = TableB.Key OR TableA.Key IS NULL GROUP BY TableB.Key
inner join from full join
--inner join from full join SELECT * FROM TableA FULL JOIN TableB ON TableA.Key = TableB.Key AND TableA.Key IS NOT NULL AND TableB.Key IS NOT NULL
left join from full join
--left join from full join SELECT * FROM TableA FULL JOIN TableB ON TableA.Key = TableB.Key AND TableA.Key IS NOT NULL
right join from full join
--right join from full join SELECT * FROM TableA FULL JOIN TableB ON TableA.Key = TableB.Key AND TableB.Key IS NOT NULL
(Of course, you could always use a cross join or comma-join and do the same thing in thewhere
clause...)Oh, and cross join from inner join!
--cross join from inner join SELECT * FROM TableA INNER JOIN TableB ON TableA.Key = TableB.Key OR TableA.Key <> TableB.Key OR TableA.Key IS NULL --Edit: added these null checks OR TableB.Key IS NULL
-
@djls45 said in Right join! Huh! What is it good for?:
--full join from inner join
SELECT *
FROM TableA
INNER JOIN TableB ON TableA.Key = TableB.Key
OR TableA.Key IS NULL
OR TableB.Key IS NULLExcept that doesn't work, at least in SQL Server
create table #temp (id int) create table #temp2 (id int) insert into #temp values(1), (2), (3) insert into #temp2 values (1), (4), (5) --full join from inner join SELECT * FROM #temp INNER JOIN #temp2 ON #temp.id = #temp2.id OR #temp.id IS NULL OR #temp2.id IS NULL
id id ----------- ----------- 1 1 (1 row(s) affected)
-
@polygeekery said in Right join! Huh! What is it good for?:
@izzion said in Right join! Huh! What is it good for?:
@lorne-kates
@Polygeekery, do you rent out your arsonist services? I have a potential target that needs enlightening...It is more a labor of love than a service. As such I always feel bad when I charge. Also, I do have @Lorne-Kates home address so I could make it happen.
Fair warning-- there's enough chemicals in the house (for hobby purposes, of course) that you'll have to set that fire and run very fast and very far to avoid the fireball.
-
@lorne-kates Arduino to the rescue! Why use an old school analog fuse when you can overcomplicate it with a microcontroller, have a bug in your code and have it go off in 1 second instead of 1,000 seconds?
-
@djls45 said in Right join! Huh! What is it good for?:
full join from inner join
@jaloopa said in Right join! Huh! What is it good for?:
doesn't work, at least in SQL Server
Nor in MS Access, either.
-
@jaloopa said in Right join! Huh! What is it good for?:
@djls45 said in Right join! Huh! What is it good for?:
--full join from inner join
Except that doesn't work, at least in SQL Server
@anotherusername said in Right join! Huh! What is it good for?:
Nor in MS Access, either.
Aw, nuts! I was so sure that I thought I didn't need to check. Oh, well. At least the others do, though (at least in SQL Server). :D
-
@djls45 cross join from inner join won't work if there are nulls
-
@jaloopa said in Right join! Huh! What is it good for?:
@djls45 cross join from inner join won't work if there are nulls
What do you mean? I have the null checks right up there!
()