The launch of Gandhinagar SQL Server User Group was a tremendous, astonishing success! It was overwhelming to see a large gathering of enthusiasts looking up to me (I was the Key Speaker) eager to enhance their knowledge and participate in some brainstorming discussions. Some members of User Group had requested me to write a simple article on JOINS elucidating its different types.
Download SQL Script used throughout in this article to practice along. Now, take a quick look at the following two tables I have created.
INNER JOIN
This join returns rows when there is at least one match in both the tables.
OUTER JOIN
There are three different Outer Join methods.
LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
FULL OUTER JOIN
This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.
CROSS JOIN
This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.
Additional Notes related to JOIN:
The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.
SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO
The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.
/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
The above example can also be created using Right Outer Join.
NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.
You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.
USE AdventureWorks
GO
CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
CREATE TABLE table2
(ID INT, Value VARCHAR(10))
INSERT INTO Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
USE AdventureWorks
GO
/* INNER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* RIGHT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* OUTER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
GO
/* RIGHT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO
/* OUTER JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO
/* CROSS JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
CROSS JOIN Table2 t2
GO
DROP TABLE table1
DROP TABLE table2
GO
I hope this article fulfills its purpose. I would like to have feedback from my blog readers. Please suggest me where do you all want me to take this article next.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
very good article, this is almost by far the best article that explains the various types of JOIN in SQL server i have ever read.
Nicely presented.
The images aren’t loading for me today so I can only read the descriptions. On their own the text descriptions of the LEFT / RIGHT outer joins isn’t 100% clear to me.
What about anti semi joins or semi joins?
http://msdn.microsoft.com/en-us/library/ms191171.aspx
Hi,
The article is excellent! Keep up the good work.
Best,
Joydip
Never mind my last comment, now the images have loaded I can see you have included the anti semi join, although I think left anti semi join is an operator in sql 2008.
Explanation and presentation is really very good. thanks.
I like blog post’s with many images and code examples. Great and clear article. Thanx ;)
Hello Pinal,
Very nice article. kepe it up. Hope to see more in same series.
Thanks a lot for this article. What I never really figured out suddenly seems so obvious :-)
Pinal,
Great blog post. I love the visualizations for the join. I was not even aware there was a “cross join”! Do you know if that is standard SQL or MS SQL Server only?
Hi Ben,
Thank you for your comment. Cross Join is part of Standard SQL.
Kind Regards,
Pinal
@Pinal,
Do you know if a cross-join is basically the same thing as performing a join where the condition is always true?
ie.
SELECT t1.*, t2.*
FROM t1
INNER JOIN t2
ON ( 1 = 1 )
@Ben,
Yeah, that is very True!
I never visualized that cross join like that.
Good concept.
Regards,
Pinal
@Pinal,
Ah, ok cool. I thought I had been able to make cross joins before, but never knew the “cross join” keyword. Of course, the idea of creating an ON clause that is not related to either table in the join is a bit odd :)
@Ben Nadel,
Very true!
I never knew the other syntax for cross join so thanks :)
Regards,
Pinal
Hi,
Its a very giid article by which anyone can get understand it easily and thoroughly.
Its a too good to understand it with Diagram, which gives more understanding.
Many many thanks to you.
Thanks,
Tejas
Very great article, I have to clap…
Thanks for article. I did not know about the use of where Null clause and an easy way to get the results for NOT INNER JOIN.
Pinal,
Isn’t Left outer join where NULL same as
Select col1 from Table1
except
select col1 from table2
and viceversa for right outer join where null?
Nice Article Pinal. :)
Very very very great! With some charts, it is quite easy to understand!
@Pinal
This is perhaps a point i have with ANSI syntax. I don’t think joins add rows, they restrict rows. That is, all joins are Cartesian joins (because all records of each TABLE match all records in every other TABLE) the join condition limits the actual records matched, resulting in less comments in the final result.
So, when i explain joins to other people, i first explain how the FROM clause works. That is, it loads the data into a page, and a cursor runs through the page until the EOF. (And the results returns are x,y coordinates. The “x” is the records the cursor points to, the “y” in the COLUMN in the SELECT clause.) Adding another TABLE to the FROM clause opens two pages, with the second page being run through one time for each and every record in the first page/TABLE. (This is the logical explanation, not what is actually done do to optimization and INDEXes.)
To me, the images you have here explain the effective result set, but not how it is done. The difference? Explaining the logical perspective should empower the user to do a lot more, and it isn’t just magic anymore. It is something logical. The images help by giving the “overall” view.
@Brian Tkatch
Thanks your comments are very valuable.
Regards,
Pinal
@Pinal,
Excellent article…!! Explaining “Join” with “Where Null” is great..!! A very easy and clear representation is superb..!!
Thanks, Thanks a lot for writing this article.
Can you please explain when we use all this join, what happen exactly behind the seen, how SQL server process all this joins.
Thanks…
Thank You Very Much
This is a VeryGood Artical on Joins
Firstly thanks..
Very useful and worth article to study and easy to understand.
Hope more articles from u….
article is simple but excellent
Thank You
Excellent Post !!
I love the way u used Venn Diagrams to explain the JOINS.
Thank you – your examples of queries that can be replaced by joins – and going from a venn representation of one’s query to the corresponding sql was very helpful.
Hi Sir
Can you please describe about the SELF JOIN i was questioned in one interview,where they asked the functinality of SELF JOIN with example.
Regards
Amarnath
Excellent!
Wonderful !
This is EXCELLENT !
BRAVO !
Yaar ! You sense of Explanation is Amazing
:)
K.K.B
superb article on joins… best ever explanation…
First Time i have cleared my concept.
A lots of thanks with best wishes.
Really Excellent Post !! Please post these kinds of Articles usually and help the devlopers….
Thank you for your article ! I bookmarked it on my browser.
Amazing any one can understand only one sight , today i visualize use of joins
thanks
very good one for beginner
Really very good article !! I cleared my doubts about joins..!!! Thanks a lot ..
select x1.itemname as ‘equipment name’,
x2.itemname as ‘wbs’
from jnameditem
join jnameditem x1 on jequipmentocc.oid=x1.oid
join jnameditem x2 on x2.oid=xbelongstoproject.oidorigin
i am getting multipart identifier cannot be bound error for this query …can anyone help??
Hi Pinal,
I have a question concerning a statement you made above where rather than writing a query as
SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
I should write it as
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
as it is a best practice. Does this mean that the later statement will run more quickly than the former or are you calling it a best practice from a legibility stand point?
The reason I am asking is I tend to use my WHERE clause for the main table and put my other filter conditions on my INNER JOINs for example:
SELECT *
FROM customers c
INNER JOIN sales s ON c.id = s.id AND YEAR(s.date) = 2009
WHERE c.country = ‘USA’
I find more legible than:
SELECT *
FROM customers c
INNER JOIN sales s ON c.id = s.id
WHERE c.country = ‘USA’ AND YEAR(s.date) = 2009
and I feel the same about
SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
as I can see everything in fail swoop and I don’t have to look at several places.
Naturally we are all artists and there is always a personal preference, but when it comes to speed, that takes precedence over everything!! :-)
Love the site and am becoming a huge fan, thank you for being there for us. Peace!
Very nice article. I could refresh my skills about joins quickly as i lost touch to SQL.
very nice article indeed. thanks for helping and sharing :)
Good one Pinal.
Was wondering if you have something on optimizing the joins.
This is EXCELLENT !
thanks for providing such a nice article.
GREAT,
finally I can finish my work
this is the best join’s example i have seen so for.
Cool very simple and explanatory!
This is a good example. Anyone can learn easily with such examples. Nice work. no more words to say..
Excellent Article!
After more than 15 years dealing with DB,
this is the best article that explains JOIN concept.
2 da point explanation with an image.. That’s what the others don’t do..
Thanks, very helpful.. ^^
Sir,
This is very good article about the SQL Joins and I have never seen such type of a detailed article about the subject. It is very help ful to the beginners as well as to the next level developers.
Once again thank you very much for giving us this type of articles.
Ranganath.G
Mr Dave
Thanks for a great article. Your site was the first place I checked because I find your explanations really good – simple and to the point. Personally I found the Venn diagrams the most logical way to show joins.
From glancing at your article, I noticed immediately that I was using the wrong join. Having switched from full outer to left outer, i was still getting the wrong result.
After much searching other sites and realising that your explanation was the best I could find, and a lot of playing about I realised that I had to include the ID from the original table and select distinct.
Now I need to find out how to hide the ID on the report…
Hello Chief,
Thank you for good comments on this article. To hide a column (ID here) you can select a subset of columns by placing the main query in CTE or subquery.
Regards,
Pinal Dave
Good i not ever seen the explanation for joins thanks its helped me a lot
Thanks, it helped me to know about join
Simple, easy and very well presented. Bravo
really very good i am not found like this in other wesites process of display is good.
Wonderfull representation.. I always have the doubt in Join conditions. I think i never forget hereafter..
Yes, it is very comprehensive tutorial about joins. But as you know join is a very expensive, it takes great time to execute. What in your opinion is the alternate way to avoid joins and bring some optimization to queries.
Regards.
nice article,but what about self join
what about self join,
Hello Rathnakar,
Here I described the types of joins. These are methods supported by SQL Server, in which one table can be linked with other table to get a result.
“Self join” is not a method of linking one table to another. It is just a case where one table is joined with itself to get the desired output. SQL Server does not have keyword for “self join”. Eventually “self join” is also implemented using one of the method described above.
Regards,
Pinal Dave
Hi Pinal Dave,
Thanks for prompt reply
Regards,
K.Rathnakar
Great.. good explanation ..,, much easier to understand .
thanks a lot
Very good article. I found it useful.
Love it. Thanks.
Really a nice article …
One thing i need to clarify what is the difference b/w JOINING and NESTED QUERIES in terms of performance wise and internal execution.
Thanks.
Hello Pinal\All,
Any update on the question posted by “Noel Stefan Stoyanoff” on August 25, 2009 at 2:00 am.
——————————————————
I have a question concerning a statement you made above where rather than writing a query as
–QUERY 1
SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
I should write it as
–QUERY 2
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
as it is a best practice. Does this mean that the later statement will run more quickly than the former or are you calling it a best practice from a legibility stand point?
——————————————————
The estimated execution plan for Query 1 and Query 2 do not look very different.
When dealing with large tables which approach would prove better from performace point of view.
Please let us know.
NOT IN is always a very bad operator. Especially if Table2.ID is not indexed. You will end up nested loops which takes a lot of CPU time. So execution goes like:
for-each ID in Table1
for-each ID in Table2
if Table1.ID Table2.ID
AddToResults Table1.ID
What if Table 2 only has 10 records?
Execution takes count(Table1.ID) * count(Table2.ID) * t (where t = time to do the actual comparison operation and store results) to finish. In any case.
It might be that Query Optimizer decides to do nested loop even with LEFT JOIN. Sometimes it’s faster than other join types. But I think that with NOT IN operator you lock yourself into a nested loop and optimizer can’t optimize it at all.
And remember to use indexes if possible. My friend got the task to speed up a Oracle database which had NOT IN operator used with unindexed fields. He put two indexes to the tables and query execution time dropped down from something like 2 minutes to 30 seconds. Btw. it took almost 2 days from the DB to create those indexes ;)
But if anyone has better knowledge about this I’m happy to be corrected.
Thank you Marko..
Really it’s a nice article and easy to learn.
Thank you Pinal.
Keep writting…
Hi Pinal,
This is really an excellent article on joins. As am a beginner it cleared my confusion as it covered the basic knowledge on joins which is absolutely brilliant.
Regards,
Vish.