Hi Conner. I'm retired for over 2 years now and don't play in Oracle like I used to (daily). I felt my SQL skills were pretty strong after using Oracle for almost 30 years, but I have to admit I didn't know about a partitioned outer join. Thanks for posting about this!
@DatabaseDude
Ай бұрын
That is why it was worth a video!
@hoi-polloi1863
Ай бұрын
This is really nice! I suppose you could get a similar result by joining the scores against a full join on the list of unique quarters vs unique players, but this partitioned business seems really clean.
@DatabaseDude
Ай бұрын
Yes, you can achieve it with a full join, but not sure how well that would scale to a real world case (eg 100s of thousands of customers across, say, 12 months)
@krakajak67
Ай бұрын
I learn something about SQL every time I watch your videos. And that’s after nearly 40 years working with SQL databases. Thanks.
@DatabaseDude
Ай бұрын
Glad to help!
@monawoka97
Ай бұрын
Man as a C++ dev SQL is like a whole other world. It makes sense but I have to stare at the syntax for so long to understand what it's doing.
@DatabaseDude
Ай бұрын
I am the same with C++ :-)
@orterves
Ай бұрын
I would have probably done that dual cross joined to a query of players, then left joined to the player quarter
@DatabaseDude
Ай бұрын
Indeed - there are plenty of solutions, but I always like solutions that are simple and easy.
@LewisCowles
Ай бұрын
Why did you use this very Oracle specific syntax? It's interesting, but I'm not sure folks shouldn't jam in a few CTE's (1-4 is a very short union) and then cross-join distinct players, with the quarters. Even using NVL vs COALESCE seems to be a quite specific oracle-ism. Nice video, just looked like you were doing this on a phone, to which I'd considered that it would be sqlite.
@DatabaseDude
Ай бұрын
"very Oracle specific syntax" ...... Um... partitioned outer is in the SQL Standard (F403). Just because other vendors haven't caught up doesn't mean its "specific"
@professortrog7742
Ай бұрын
@@DatabaseDude F403 is intentionally omitted in the PostgreSQL implementation. And Dual is an oracle-ism too.
@jcwynn4075
Ай бұрын
@@DatabaseDude that's literally the definition of specific 😂 also, word of advice: didn't be sassy in your own comment section
@ehsnils
Ай бұрын
I started with SQL in the beginning of the 1990's on Oracle (5.1) and at that time there wasn't even a "LEFT" or "RIGHT" join, at most you could use a (+) syntax to make some things happening - and for some tricks it was necessary to create temporary tables to be efficient.
@DatabaseDude
Ай бұрын
ANSI joins came in for oracle at around 9i timeframe
@vikram4755
Ай бұрын
Wow.. thankfully after this i am not the one who doesnt know this 😂 Thanks Connor.
@LuminousWatcher
Ай бұрын
I guess that the title should have been "The JOIN syntax in Oracle SQL that no-one knows about" - I do not see this in MySQL, DB2 or MariaDB, which i usually work with - an interesting function nontheless
@DatabaseDude
Ай бұрын
It's not up to Oracle what other vendors decide to leave out of their implementation. Partitioned outer join is in the SQL standard
@RonPowell-i9g
Ай бұрын
I'm not a database guy at all. I've done a bit of SQL work a long time ago. Real reason I'm here? The greyhound thumbnail picture :)
@orterves
Ай бұрын
I work with SQL and databases daily I'm still only here because of the greyhound
@DatabaseDude
Ай бұрын
Real reason I make tech videos? To post pictures of my hound :-)
@DatabaseDude
Ай бұрын
Bailey (my hound) likes your comment
@RonPowell-i9g
Ай бұрын
@@DatabaseDude I fully endorse this course of action, sir!
@StephenMoreira
Ай бұрын
Yea very neat, did that back in the day when I had a more SQL intensive role.
@geoffstrickler
Ай бұрын
Useful, and I think that’s unique to Oracle. The Syntax in SQL Server or other systems may be different. Now, for your specific example, what would be a useful addition is play time by quarter. Clearly that’s additional data entry and likely a separate table that tracks (at a minimum) times of player substitutions, from which you can create play-time by player by quarter, then join that to the score information. And, of course, you can also use that to show or omit players didn’t play in a game or range of games.
@DatabaseDude
Ай бұрын
Agreed - but whilst fine for a basketball game, not sure that would scale nicely to a real world enterprise use case (eg 10,000 customers across 12 months etc)
@Meower68
Ай бұрын
I've used partition by on something like row_number() but never seen it on a join. Good one! I was going to query out a unique list of players, do a cross join on quarters (probably as a TVC), giving me a full set of tuples, then join that against the actual points data. I've done something similar, in the past, on DB/2. This is simpler. Will need to see just how many other databases out there implement that.
@DatabaseDude
Ай бұрын
a cartesian product is definitely an option in this (small data) case, but if you extrapolate that to a more real world example, that could get expensive very fast
@theboss9885
Ай бұрын
Really no one knows it..thanks Conner
@optimizer77
Ай бұрын
Hi Conner, very interesting, thanks for sharing! Last time I worked with SQL was more than 10 yrs ago. I am curious, maybe it would even work by partitioning also by quarter? (basically getting rid of the synthesized table altogether and also the right outer join...)
@DatabaseDude
Ай бұрын
we need something to join to in order to use the "partition by"
@brianfisher6799
Ай бұрын
In SQL Server i just cross join into a temp table constrained by whatever interval is needed and then select from that temp and left join to the data. Could also use CTE if you have a date dimension...
@DatabaseDude
Ай бұрын
true, but I think that is going to be a much more complicated SQL
@SandeepSandhu
Ай бұрын
Awesome!!
@DatabaseDude
Ай бұрын
Thanks!
@iudithmentzel6288
Ай бұрын
I think you should start to teach APEX also. For me, it will be my only way to learn APEX ... :)
@johnkelly9970
Ай бұрын
This is good. Well explained
@DatabaseDude
Ай бұрын
Thank you!
@johnkelly9970
Ай бұрын
@@DatabaseDude Wondering if there's a CROSS APPLY and OUTER APPLY video in the works ? : )
@m9600174
Ай бұрын
Can you use this trick for both the quarter and player at once (so you don't have to generate the fake table with the 4 quarters)?
@DatabaseDude
Ай бұрын
You need to join to *something*
@thomasbrotherton4556
Ай бұрын
Why not do some type of pivot? You could list one row per player, with columns for each quarter going across.
@DatabaseDude
Ай бұрын
Sure, that would work, but why immediately jump to changing the requirement?
@lyrebird712
Ай бұрын
Would love if I could use this at work. Neither of our databases support this so I have a whole list of utility tables that I join with to get similar functionality.
@DatabaseDude
Ай бұрын
"neither"- what database ?
@lyrebird712
Ай бұрын
@@DatabaseDude MySQL 5.1 and 8.-something (don't recall the exact version, most of our applications are on the old one). I did look the docs up the other day at work and didn't see it :(
@andymoss4285
Ай бұрын
I like this trick
Ай бұрын
PARTITION BY is regular SQL. However, CONNECT BY is an Oracle-specific feature. It could have been stated early in the video. Nice video and good information but currently just wasted my time.
@DatabaseDude
Ай бұрын
true, but that element is not really the emphasis of the video - its just to synthesize some data. So you could take part and utilise whatever suits (generate_series etc etc)
@fisherh9111
Ай бұрын
this is why I prefer Excel.
@DatabaseDude
Ай бұрын
fine for 100 rows...not so fine for 100million
@fisherh9111
Ай бұрын
@@DatabaseDude who needs more than 100 rows? Only 12 months in the year!
@debpatro
Ай бұрын
🙏, I learned a new thing today.
@DatabaseDude
Ай бұрын
That is why this channel exists
@nothinghere1996
27 күн бұрын
great language.
@janbrittenson210
Ай бұрын
Why not just have a second table with the players who played in each quarter and join with that. It would seem that if a player only played in one quarter you don't want 0's listed for the three they didn't play in...
@DatabaseDude
Ай бұрын
You could do that, but now you've got another table to maintain, populate, keep in sync with the base data.
@mohamedaminesekkai7326
Ай бұрын
Does it work on 11g 11.2.0.3 ?
@DatabaseDude
Ай бұрын
Been there since Oracle 9i
@jasfigueroa
Ай бұрын
I had not seen that use of PARTITION BY before so I asked our AI SQL Overlord for an explanation.... It seems that there is no spoon ;) The query you provided is a bit unusual in that it includes a PARTITION BY clause without an OVER clause, which is typically how window functions are used. In SQL, PARTITION BY is usually used within the context of a window function to define the partitions on which the window function operates. However, the provided query seems to mix the syntax and semantics of different SQL dialects or perhaps contains a typo. Clarifying the Intended Query Let's break down and correct the query for better understanding. Original Oracle Query sql Copy code SELECT qtr, player, NVL(pts, 0) FROM ( SELECT quarter, player, SUM(points) pts FROM basketball GROUP BY quarter, player ) b PARTITION BY (b.player) -- This seems incorrect or misplaced RIGHT OUTER JOIN ( SELECT rownum qtr FROM dual CONNECT BY LEVEL
@DatabaseDude
Ай бұрын
No typo :-) That is right where the PARTITION command goes for a partitioned join
@orterves
Ай бұрын
The AI gets confused because this is a very obscure syntax that Oracle implements (and pretty much nothing else does, apparently).
@RoamingAdhocrat
Ай бұрын
ah, you started out so personable, but as soon as you said `select from dual` your eyes went black and a swarm of snakes came out of your mouth
@DatabaseDude
Ай бұрын
you'll need to upgrade to 23ai then
@peabody3000
Ай бұрын
why not a simple left outer join?
@DatabaseDude
Ай бұрын
Try it - you don't get the correct answer
@user-vo3st8kx7s
Ай бұрын
I'm not convinced, first I'm 100% percent sure right join are evil, second the partition thing smells too much Oracle to be angel.😊
@DatabaseDude
Ай бұрын
If joins are evil, then perhaps relational databases are not for you :-)
@user-vo3st8kx7s
Ай бұрын
@@DatabaseDude read again, "right join" otherwise i won't bother watching.
Пікірлер: 72