SQL SERVER – Introduction to JOINs – Basic of JOINs

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.
SQL SERVER - Introduction to JOINs - Basic of JOINs inner join

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.
SQL SERVER - Introduction to JOINs - Basic of JOINs left join

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.
SQL SERVER - Introduction to JOINs - Basic of JOINs right join

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.
SQL SERVER - Introduction to JOINs - Basic of JOINs outer join

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.

SQL SERVER - Introduction to JOINs - Basic of JOINs cross join - half

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

SQL SERVER - Introduction to JOINs - Basic of JOINs left join null

The above example can also be created using Right Outer Join.

SQL SERVER - Introduction to JOINs - Basic of JOINs right join null

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.

SQL SERVER - Introduction to JOINs - Basic of JOINs outer join null

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)

, , , ,
Previous Post
SQL SERVER – FIX : ERROR : The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine Error:2147749896 (0×80041008)
Next Post
SQL SERVER – Check if Current Login is Part of Server Role Member

Related Posts

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’

    Reply
  • 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’

    Reply
  • excellent

    Reply
  • chakravarthy
    May 11, 2014 4:51 pm

    Thank you very much for your articles. Sqlblogauthority is helping us to learn many things very clear

    Reply
  • brijesh sharma
    May 13, 2014 5:58 pm

    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

    Reply
  • 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

    Reply
  • Rakesh K Jayswal
    June 5, 2014 12:05 pm

    it’s very useful for me understand he joins…………tanks :)

    Reply
  • Jagadeesh Kumar
    July 3, 2014 5:57 pm

    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.

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • Misael Moneró Thompson
    December 9, 2014 7:39 pm

    THANK YOU SO MUCH!!!!!!!!!!!!!!!!

    Reply
  • thanks

    Reply
  • Uma Srinivasan
    January 9, 2015 8:24 am

    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

    Reply
  • What purpose does Cross join serves?
    Any real time examples I can get…

    Reply
  • 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

    Reply
  • bradsteinberg
    July 8, 2015 5:18 am

    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.

    Reply
  • I cannot see the visual explanation images in your article Pinal.

    Reply
  • 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.

    Reply
  • It is a very good article

    Thanks,
    Gittin.

    Reply

Leave a Reply

Menu