TSQL -What Is The Difference Between INNER JOIN AND LEFT JOIN

INNER JOIN: 

Inner Join returns you all matching records from two tables. You can further join the result set to another table or result set. As long as the column values match for Joining columns , you will get the records back.

If there are duplicate records for Joining column/s and they match,then you will get duplicate records in your result set.

LEFT JOIN:

Left Join will return you all the records from Left Table and matching records from Right table. If there are not matching records for Right Table, You will get Null values in those columns. Left Join will also return you duplicate records if your tables have duplicate records in columns on which you are joining and they are matching.

Let's create an example to see them in action;)

I have created two Temp tables #Person and #Country. #Person table CountryId from #Country Table. So we can join on CountryId.

CREATE TABLE #Person
  (
     PersonID  INT IDENTITY(1, 1),
     FName     VARCHAR(100),
     LName     VARCHAR(100),
     CountryID INT
  )GO

CREATE TABLE #Country
  (
     CountryID   INT IDENTITY(1, 1),
     CountryName VARCHAR(50)
  )

GO

INSERT INTO #Person

VALUES     ('Raza',
            'Ali',
            1)GO

INSERT INTO #Person

VALUES     ('Christy',
            'Jason',
            2)GO

INSERT INTO #Person

VALUES     ('John',
            'Rivers',
            3)GO

INSERT INTO #Person

VALUES     ('Sukhjeet',
            'Singh',
            NULL)GO

INSERT INTO #Country

VALUES     ('USA'),
            ('France'),
            ('Italy')

SELECT * FROM #Country

SELECT * FROM #Person

Fig 1: Sample Temp tables 

Now if we want to find all the Persons which has the country or in other words , We want to find all the matching records. To do so we will be using INNER JOIN

SELECT P.FName,
       P.LName,
       C.CountryName

FROM   #Person P
       INNER JOIN #Country C
               ON P.CountryID = C.CountryID

Fig 2: Inner join output

As we can see that Sukhjeet Sing is not returned by our above query as there is no matching record available in #Country Table for Sukhjeet Singh. The Countryid for Sukhjeet Singh is Null and we don't have Null record in #Country Table. Even if we would have Null in Country Table, it would not return us Sukhjeet Singh as Null is not equal to Null. In such situations we can always use ISNULL function to replace with some number on both sides so we can join them.

Now if we want to get all the records from Left Table, in our case from  #Person table. No matter if it has matching records or not in #Country Table. We can use LEFT JOIN.

SELECT P.FName,
       P.LName,
       C.CountryName

FROM   #Person P
       LEFT JOIN #Country C
              ON P.CountryID = C.CountryID

Fig 3: Left Join output

As we can see that all the records from left tables are returned and if there was matching record in Right table that value is returned. If there were not matching records then NULL is returned as value.