With a bit of design work (e.g. referential integrity constraints between the Sales and Country tables), the "AU" data won't be able to be missing. Then the inner join will always work fine. Whether that is done or not depends on other factors, e.g. the number of records in the tables. The trouble with the approach in the video is that other statistics can become misleading, e.g. a query reporting the total sales from Australia.
The only issue with referential integrity is that as a developer you might not know if it exists. It is a good point regrarding AU sales. I suppose I could have chosen a better example to illustrate the point.
Great Video! Short, concise, and providing unique content. You are not going to find this kind of thing all over the internet. Glad I found your videos!
Seems like a bit of an odd one, this. It seems like the sort of thing even a beginner should understand. If I was to interview and test a junior SQL developer for a job (something I'll be helping to do within the next couple of months) and they didn't know this, I would be very hesitant to employ them! Your statement about this sort of thing separating an expert or a true professional from a novice or intermediate developer just seems way off!
This is so good. Expert level Sql RUclipsrs are so hard to come by. Subscribed. Also if I may, can you do some more Sql challenges where you solve hard problems using expert Sql. I would really love to learn and watch those. I really love Sql.
hey James, your videos are amazing. Do you mind covering scenarios on where to use group by vs window function. sometimes we run in situations where the output can be achieved using either approach. I know from a performance perspective group by is more efficient however since window functions are easier to read and write I always start with window function I need to break this mental block of mine and therefore if you can go through this topic that would be highly appreciated. Again thanks for all the amazing videos.
no disrespect because you raise an important fact about joins. that said… you should have specified that this is most relevant when a target column is nullable; eg country code in this example.. otherwise if it’s non nullable the outer join is pointless. also this is like one of the first hard lessons you learn when you start writing real queries.
That depends on the nature of your data. In this video I was mainly referring to manually written queries but take a look at my video "All about outer joins". In there I talk about that as it relates to Mstr. As a quick example, I recently worked with data where many of the attributes values could be "blank" in the fact tables so in this case I changed the behavior to outer join because I didn't want to drop rows just b/c an attribute was not populated. Now, if this is common throughout your data, then it might make sense to change the project behavior but if this is only the case for a few attribute then I would leave the project behavior as is and just change the attribute join behavior.
Hi oliver sir...I am watching your microstrategy videos...very good videos sir. Thanks sir. Please put a video for how to validate microstrategy report..my English is not good..please understand..🙂🙂
I have SQL in my blood :) So it is about data integrity. If you do not have country in dictionary so you have problem beceuse this is being used probably in a dozen of queries.
Im just proud of myself that i started learning sql 2 weeks ago and i understand about 95% of what ur talking about. Goals
Awesome video! Definitely changing how I am going to be approaching problems. Keep these coming please!!!! ...
With a bit of design work (e.g. referential integrity constraints between the Sales and Country tables), the "AU" data won't be able to be missing. Then the inner join will always work fine. Whether that is done or not depends on other factors, e.g. the number of records in the tables.
The trouble with the approach in the video is that other statistics can become misleading, e.g. a query reporting the total sales from Australia.
The only issue with referential integrity is that as a developer you might not know if it exists. It is a good point regrarding AU sales. I suppose I could have chosen a better example to illustrate the point.
Great Video! Short, concise, and providing unique content. You are not going to find this kind of thing all over the internet. Glad I found your videos!
Glad you enjoy! Thank you! :)
I learned this the hard way when I was pulling customers and comments. Inner join would not pull customers without a comment.
Seems like a bit of an odd one, this. It seems like the sort of thing even a beginner should understand. If I was to interview and test a junior SQL developer for a job (something I'll be helping to do within the next couple of months) and they didn't know this, I would be very hesitant to employ them! Your statement about this sort of thing separating an expert or a true professional from a novice or intermediate developer just seems way off!
Good one man. Optimization is much needed !!
Really Helpfull :)
I am glad that i found this channel
This is so good. Expert level Sql RUclipsrs are so hard to come by. Subscribed. Also if I may, can you do some more Sql challenges where you solve hard problems using expert Sql. I would really love to learn and watch those. I really love Sql.
Really interesting James, thanks for that 👏🏽
Loved it. I never thought of this before... Thanks mate.
Never ever ever expect complete high quality data in your database. I learned it the hard way
Outstanding video! Thank you!
you are the best ! thnx !!
I will never use inner join in my life again 😭 .... just kidding
Hahaha :)
Hahhahahah the ending made me laugh a lot. Great video, thnx for the lesson.
:)
one needs to know the biz rules and how tables are tied together. otherwise, yeah you better know why using an inner join ;-) Great video mate!
I felt so guilty the way he said, "I think you are using too many inner join".
😆
Well Played
hey James, your videos are amazing. Do you mind covering scenarios on where to use group by vs window function. sometimes we run in situations where the output can be achieved using either approach. I know from a performance perspective group by is more efficient however since window functions are easier to read and write I always start with window function I need to break this mental block of mine and therefore if you can go through this topic that would be highly appreciated. Again thanks for all the amazing videos.
Great perspective and thanks for sharing
You're very welcome. Thanks for watching! :)
Thank you, Oliver.
no disrespect because you raise an important fact about joins. that said…
you should have specified that this is most relevant when a target column is nullable; eg country code in this example.. otherwise if it’s non nullable the outer join is pointless.
also this is like one of the first hard lessons you learn when you start writing real queries.
Good thoughts.
Thanks for the video. By default the mstr reports does a inner join between fact and dimension tables, so is it advised to change this behavior ?
That depends on the nature of your data. In this video I was mainly referring to manually written queries but take a look at my video "All about outer joins". In there I talk about that as it relates to Mstr. As a quick example, I recently worked with data where many of the attributes values could be "blank" in the fact tables so in this case I changed the behavior to outer join because I didn't want to drop rows just b/c an attribute was not populated. Now, if this is common throughout your data, then it might make sense to change the project behavior but if this is only the case for a few attribute then I would leave the project behavior as is and just change the attribute join behavior.
@@JamesOliver Expecting quick byte videos like these from time to time. Thanks.:-)
Yeah! Programmers should never have inner join! Outer the better
You are master 😎
Hi oliver sir...I am watching your microstrategy videos...very good videos sir. Thanks sir. Please put a video for how to validate microstrategy report..my English is not good..please understand..🙂🙂
I have SQL in my blood :) So it is about data integrity. If you do not have country in dictionary so you have problem beceuse this is being used probably in a dozen of queries.
eh! Of-course, Left Joins are the most commonly used. Need to really shows us more 'advanced' tips.
Thank god 😂 the fact I have to explain how stupid it is to use inner joins in the real world is so sad. I use left nested
Joined 99% of the time