This blog post is written in response to the T-SQL Tuesday post of JOIN. This is a very interesting subject. Years ago, I wrote my article about SQL SERVER – Introduction to JOINs – Basic of JOINs, ‑ till date, it is my most favorite article on the blog.
Today we are going to talk about join and lots of things related to the JOIN. I recently started office hours to answer questions and issues of the community. I receive so many questions that are related to JOIN. I will share few of the same over here. Most of them are basic, but note that the basics are of great importance.
Without further ado, let me continue with the question and answers.
Q: Which one of the following is a better method?
Method 1:
SELECT t1.*, t2.*
FROM t1,t2
WHERE t1.col1 = t2.col1
Method 2:
SELECT t1.*, t2.*
FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1
A: The answer to this question will bring some interesting conversation. I strongly prefer method 2 because it is much cleaner to understand and if I have to use table level hints or so on, it is much convenient to do the same. I would suggest going ahead with method 2. Currently with regard to performance and execution plan, both the methods show the same (most of the time). However, with respect to standard and future innovation, method 2 is the way to go.
When I have to perform a performance tuning task, and if I see method 1, I usually ask the developer to convert it to method 2 as I feel much more comfortable with method 2. Additionally, when you have to work with OUTER JOIN, you will have to do so.
Q: What is better ‑ subquery or JOIN?
Subquery:
SELECT t1.*
FROM t1
WHERE t1.col1 IN (SELECT t2.col1 FROM t2)
Join:
SELECT t1.*
SELECT t1.*, t2.*
FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1
In this case, there is no right answer. You should use the one that gives you optimal performance. I have seen cases when the subquery gives optimal performance as well join giving optimal performance when compared to each other. I have seen either of them performing so well that I think one has to test out both the methods before selecting one. If you are facing situation where you are not sure which method you should select, I suggest that you go with your intuition. I still prefer JOIN over any other method, but in this case, I will suggest you to test your options.
Q: How to simulate Join?
A: I get this question a lot of times, and I have no answer. Here, I want your help as I do not even understand this question.
Q: How can I change my LEFT JOIN to RIGHT JOIN and get the same answer?
A: Sure. Here is quick example of the same:
Left Join:
SELECT t1.col1, t2.col2
FROM t1 LEFT JOIN t2 ON ON t1.col1 = t2.col1
Right Join:
SELECT t1.col1, t2.col2
FROM t2 RIGHT JOIN t1 ON ON t1.col1 = t2.col1
Both of the above options will give you same result. However, the real question is why you want to do that. What is the reason that you want to change the left join to right join?
Q: Does it matter how I write tables in my join if I am using INNER JOIN only?
A: No it does not matter in case of INNER JOIN as the result will be the same, and the SQL Server Engine will figure out the optimal execution plans for your query. As your question clearly suggests that for any other kind of join (i.e., OUTER JOIN, CROSS JOIN), it will matter for sure. Additionally, there are cases with INNER JOIN ‑ when order is forced on them, they have shown a little performance enhancement. Here is a quick example of the same.
If you have attended my session of Virtual Tech Days few days ago, you would have seen the example of the how forceorder hint works.
Q: Is there a quick tutorial to Joins?
A: I have written an article on this subject earlier, and as I said earlier in this article, I personally like the same a lot. Here you can read about the same: Introduction to JOINs – Basic of JOINs.
Q: Is there any book available to learn T-SQL, which explains various concepts like this easily?
A: I am bit biased but you can read about my books over here.
Q: Is SELF JOIN is a type of INNER JOIN or OUTER JOIN?
A: In fact, it is both an inner as well as outer join. Self Join is a very interesting subject. Here is an interesting article that I have written earlier on this subject: SQL SERVER – The Self Join – Inner Join and Outer Join .
Q: In case of the OUTER JOIN, where should I put the condition?
A: This question requires a detailed answer, I have written a detailed blog post on this subject over here: How ON Clause Effects Resultset in LEFT JOIN .
Q: What is Optimal LEFT JOIN or NOT IN?
A: I personally prefer LEFT JOIN as I have seen LEFT JOIN doing better in many cases. Once again, I suggest you should test it with your query. Here is a quick example of the same: Differences Between Left Join and Left Outer Join.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Hi Pinal,
In your IN example, the two queries are not logically identical. If you have multiple rows in t2 that match, you will get extra rows appearing in the results of the JOIN option. IN performs a Semi Join here, whereas JOIN will perform an Inner Join. I’m not writing “Inner Join” in capitals because I’m talking about the logical operation, not the keyword.
Hope this helps,
Rob
SELECT t1.*, t2.*
FROM t1 INNER JOIN t2 ON t1.col1 = t2.col1
This is also better because,join type can easily be change.
For example ,I wrote inner join later on i realize it should be left join.
I just have to replace inner with left.
I like the emphasis on using the INNER JOIN clause instead of using a where to join the columns.
Thanks for such a good post.