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.
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 outer 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 (https://blog.sqlauthority.com)
402 Comments. Leave new
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.
Mr pinal dave. need a help on this immediately. I need to generate a report.
Lets guess a column in a table has the following data:
Robert
Tommy
Russel
Honda
i need to arrange it like this
Robert
Tommy
Russel
Honda
Robert | Tommy
Tommy | Russel
..
Tommy|Russel| Honda
…
Tommy|Russel|Honda|Robert
there shudnt be:
Tommy | Rusell
Rusell | Tommy
Only One of them allow.
@Pana
How’s this?
WITH
Data(First_Name)
AS
(
SELECT ‘Robert’ UNION ALL
SELECT ‘Tommy’ UNION ALL
SELECT ‘Russel’ UNION ALL
SELECT ‘Honda’
),
Data_RN(First_Name, RN)
AS
(
SELECT
First_Name,
ROW_NUMBER() OVER(ORDER BY First_Name)
FROM
Data
),
CTE(RN, First_Name, List)
AS
(
SELECT
RN,
First_Name,
CAST(First_Name AS VARCHAR(MAX))
FROM
Data_RN
UNION ALL
SELECT
Data_RN.RN,
Data_RN.First_Name,
CTE.List + ‘|’ + Data_RN.First_Name
FROM
CTE,
Data_RN
WHERE
Data_RN.RN = CTE.RN + 1
)
SELECT
List
FROM
CTE;
I agree! Excelent!
awesome site…………..yaar
Agree!
Nicely presented.
Live example diya kro
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?
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191171(v=sql.105)
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?
@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 )
SELECT * from t1, t2
This is giving same Cross Join result.
@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 :)
Hello Ben…,
I am not able to get your point. can u please describe me other syntax for CROSS JOIN ?
HI @Ben Nadel good point
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
Ya its true..
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.
@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…