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.
During quarantine many people suffered from a lot of free time. I am one of them. I am single I want to meet an appropriate woman for a serious relationship. I was recommended to look at this online US dating site papernow.org. I got acquainted with a nice woman there 2 months ago. Many women there Are looking for a serious relationship. Try to register there. Good luck.
ReplyDeleteBefore we begin the assignment, it is necessary to understand what an essay is and what its fundamental differences from other types of creative writing are. Let's begin with a definition. An essay is a https://en.writingapaper.net/edit-my-paper/ genre of literary prose of small volume and free composition. An essay of this type conveys the author's individual impression or opinion concerning a certain topic.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHello everyone, I've been doing some research on essay writing services since I have a big paper due soon. Has anyone used essaybox before? I saw their advertisements about doing all the research, writing and ensuring papers are delivered before deadlines. Their essay box sounds convenient but I'm worried about the quality of work. For those who have used them, are the essays well written and able to pass as your own work? It would be great to hear some reviews before deciding if I should use Essay Box to help with my paper.
ReplyDelete