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.
ReplyDeleteHello everyone! I recently came across an amazing solution for creating stunning displays for your business or event. The Retractable Banner Stands from https://bannerprintingsandiego.com/product-category/stands-and-displays/retractable-banner-stands/ are the perfect tools for showcasing your brand or message!These banner stands are designed to be lightweight, portable, and easy to set up, making them ideal for use at trade shows, exhibitions, or even in your own storefront! With a wide range of sizes and styles available, you can create a custom display that perfectly suits your needs.
ReplyDelete