Important SQL interview question on Joins
January 15, 2023 2023-01-15 12:49Important SQL interview question on Joins
Subhajit There are two tables, Table-1 as below
Table-2 as below
What will be the result if we perform INNER JOIN, LEFT JOIN and RIGHT JOIN in the above two tables?
Inner Join
If we perform the INNER JOIN between the above two tables as below,
SELECT * FROM [Test-1] INNER JOIN [Test-2]
ON [Test-1].TestId = [Test-2].TestId
we will get the below result
Let Table-1 be the left table and Table-2 be the right table. The above result will return the matched rows between the two tables and as the left table has two rows with value one, the result will also show two rows with value 1 as, for 1st 1 in the left table, one row matches in the right table and for the second 1 also one rows matches from the right table. This is because the right table contain 1 row with value 1.
Left Join
If we perform the LEFT JOIN between the above tables as follows,
SELECT * FROM [Test-1] LEFT JOIN [Test-2]
ON [Test-1].TestId = [Test-2].TestId
we will get the following result
Let Table-1 be the left table and Table-2 be the right table. Here the result will be same as the INNER JOIN. Since the LEFT join will fetch all the rows from the left table so here the result will contain two rows containing 1 as one matches in both the table.
Right Join
If we perform the RIGHT JOIN in the above two tables,
SELECT * FROM [Test-1] RIGHT JOIN [Test-2]
ON [Test-1].TestId = [Test-2].TestId
we sill get the following result
Let Table-1 be the left table and Table-2 be the right table. Now if we perform the right join in the above two table, we will get two rows with value 1 as 1 matches two times from the left table and a NULL in the left table as there is no rows in the left table that matches with 5 from the right table.
For more information about the Inner join, Left join and Right join, please check the links below
Please comment and share if you like this post and tell us about how we can enhance our posts in dotnetcoaching. Thanks.
