SQL Server Interview Question - What are the differences between INNER JOIN, LEFT JOIN and RIGHT JOIN in SQL Server?

Answer:

Let us Assume we have following two tables:-
Create a new table as"Customers":



Note that the "Cust_Id" column is the primary key in the "Customers" table.
This means that no two rows can have the same Cust_Id.

The Cust_Id distinguishes two persons even if they have the same name.

Next, we have the "Orders" table:


Note that the "Order_Id" column is the primary key in the "Orders" table and that the "Cust_Id" column refers to the persons in the "Customers" table without using their names.
Notice that the relationship between the two tables above is the "Cust_Id" column.
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table.
For Example:-
The Following is the example for LEFT JOIN:
Considering the above two tables:
Query:- Select * from Customers left join Orders on Customers.Cust_Id = Orders.Cust_Id
The output will look like following:


RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table.
For Example:-
The Following is the example for RIGHT JOIN:
Considering the above two tables:
Query:- Select * from Customers right join Orders on Customers.Cust_Id = Orders.Cust_Id
The output will look like following:


INNER JOIN: The INNER JOIN keyword return rows when there is at least one match in both tables.
For Example:-

The Following is the example for RIGHT JOIN:

Considering the above two tables:
Query:- Select * from Customers inner join Orders on Customers.Cust_Id = Orders.Cust_Id
The output will look like following:

Regards,
Please click here to see more SQL Server interview questions