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
Great forum
In ref to Left outer Join – where Null example, how to add more conditions?
This works –
/* LEFT JOIN – WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
But this doesn’t work –
/* LEFT JOIN – WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
AND t2.col2 = ‘XYZ’
AND t1.col4 = ‘ABC’
I try it is not /* LEFT JOIN – WHERE NULL */
SELECT t1.*,w1.*
FROM Table1 w1
LEFT JOIN Table2 w2 ON w1.ID = w2.ID
WHERE w2.ID IS NULL
AND t2.col2 = ‘XYZ’
AND w1.col4 = ‘ABC’
excellent
Thank you very much for your articles. Sqlblogauthority is helping us to learn many things very clear
I am not clear the purpose of left outer join and right outer join. Same task perform both joins by replace tables name each other
I have seen many articles on joins. I liked the way you used the pictures to give a clear understanding. Thanks much for the article
it’s very useful for me understand he joins…………tanks :)
Hi Pinal Dave,
I have one doubt, Is there any difference between Left Join – Left Outer Join. And Right Join – Right Outer Join.? If yes, Could you please explain.
excellent article for clear understanding of joins with clear diagrammatic representation thank you so much I am expecting about padding concept also will you please check it for that as well as sub-queries
HI Pinal,I am from Vallabh Vidyanagar,Anand,,,
kem cho pinalbhai,,,jordar article lakho cho tame,,,thanks for your time and contribution,,,
I read many articles,but then i explicityl search on google for “SQL Joins by pinal dave” as i know ur articles are really easy to understand,,,aavjo,,,Parth Shah
Hey Pinal,
my Below query acts very weird, can you take a look.
Query1 : this returns only 2 records which is correct.
select grd_band,NA_POS_NBR from PNXDVP30.PNXVW345 where NA_POS_NBR IN (select NA_POS_NBR from PNXDVP30.PNXVW303 where pers_id = ‘0000344194’ and strt_date BETWEEN DATE ‘2014-01-01’ AND date ‘2014-09-30’) group by grd_band,NA_POS_NBR
Query2: Return 12 records.
select PNXVW345.grd_band,VW345.NA_POS_NBR from PNXDVP30.PNXVW345 VW345 inner join PNXDVP30.PNXVW303 AS VW303 on VW345.NA_POS_NBR = VW303.NA_POS_NBR where VW303.pers_id = ‘0000344194’ and VW303.strt_date BETWEEN DATE ‘2014-01-01’ AND date ‘2014-09-30’ AND VW345.From_date =VW303.strt_date AND PNXVW345.grd_band IS NOT NULL group by PNXVW345.grd_band,VW345.NA_POS_NBR
THANK YOU SO MUCH!!!!!!!!!!!!!!!!
thanks
select a.a1,b.b1,c.c1 from
A leftouterjoin B on A.a3=B.b3,C, D leftouterjoin E on D.d4=E.e4 and E.e5 like ‘%xyz%’,F
where C.c1=A.a1 and F.f1=A.a1 and F.f2=B.b2
select a.a1,b.b1,c.c1 from
A inner join C on C.c1=A.a1 innerjoin D on D.d1=A.C1 inner join F F.f1=A.a1 and F.f2=B.b2 leftouterjoin B on A.a3=B.b3 leftouterjoin E on D.d4=E.e4 and E.e5 like ‘%xyz%’
IN THE ABOVE QUERIES WHICH QUERY WILL GIVE BEST PERFORMANCE
1. Are you getting same results?
2. Did you check query plan and compare the cost?
What purpose does Cross join serves?
Any real time examples I can get…
Can you please explain this scenario.
there is a table 1 with ID column and values are 1,1,1
There is a table 2 with ID Column and values are 1,1,1
what will be the output of inner,left and right join and explain the Output.
For inner join it will be 9 and it is justified. But for left and right please explain.
Thanks Gaurav
This is a great introduction. If anyone would like to see a different set of examples regarding the Basics of SQL Joins, Jeff Atwood (CodingHorror) has a nice post here with Venn diagrams.
I cannot see the visual explanation images in your article Pinal.
I am able to see them Vidya.
The only way these diagrams make sense is when the elements of the Venn diagram circle sets are result rows. One needs to already know what those are like to use the diagrams as a mnemonic. Eg The shaded intersection is the rows returned by join; but the diagram doesn’t explain what those rows are in terms of the input tables. Eg The inner join diagram *includes* cross join; for condition 1=1, when the shaded intersection contains all possible pairings of a row from each input table. The diagrams use but do not explain (nor does the text) what the elements are that you are partitioning or how they arise from the input tables or the properties by which you are partitioning. The inner join diagram certainly doesn’t *explain* inner join; it is useful in comparing inner join to outer joins.
I suggest that you try to write the diagram key for this interpretation. And then try to write the key for the other interpretation of the inner join diagram by which you think it by itself explains/illustrates inner join. That diagram key is complex and the correspondence to inner join incomplete. (See my comments at and my answer at .) (Your text is not so clear either. Especially your description of rows returned.) Thanks.
It is a very good article
Thanks,
Gittin.
Thanks Gittin!