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.