Blog

Important SQL interview question on Joins

SQL

Important SQL interview question on Joins

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.

Subhajit

Leave your thought here

Your email address will not be published. Required fields are marked *