During the recent Comprehensive Database Performance Health Check, I was asked by my client this very simple but interesting question – what is the difference between Join or Inner Join. Before I answered him, I asked him back about his current understanding. The client DBA said he believes when he writes INNER JOIN, SQL Server knows to do INNER JOIN but if he does not write the INNER keyword and only writes JOIN, it will actually do CROSS JOIN which will be actually very expensive.
It is NOT true. Let us prove with the help of a demonstration in this video.
JOIN Keyword is kind of synonyms (or equivalent) to keyword INNER JOIN. They both produce the same results, same execution plan and even the same performance.
Now that we know both are equal, let me tell you what is my preference between both of these keywords. I prefer to use INNER JOIN because it is very clear to read it. Additionally, when I am doing SQL Server performance tuning and want to check the execution plan of the query with the help of various query hints, I find keyword INNER JOIN more accommodating to the hints.
Well, there you go. I really enjoyed building this video and also writing a blog post about it. If you have any other idea on which I should build video, please do let me know and I will be happy to create small to the point video on the topic.
Thanks again for reading the blog post and please do not forget to subscribe to the youtube channel and also click on the bell icon, so you get notification whenever I publish a new video.
Reference: Pinal Dave (https://blog.sqlauthority.com)