Answer:
 

Let us Assume we have following two tables:-

Create a new table as"Customers":
 

Cust_Id	LastName	FirstName	Address	City
1	Shaikh	Moosa	Churchgate	Mumbai
2	Khan	Salman	Bandra	Mumbai
3	Shaikh	Feroz	Mahim	Chennai

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:

Order_Id	OrderNo	Cust_Id
1	7895	3
2	4678	3
3	2456	1
4	4562	1
5	4764	15

 

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:

Cust_Id	LastName	FirstName	Address	City	Order_Id	OrderNo	Cust_Id
1	Shaikh	Moosa	Churchgate	Mumbai	3	2456	1
2	Khan	Salman	Bandra	Mumbai	NULL	NULL	NULL
3	Shaikh	Feroz	Mahim	Chennai	1	7895	3
3	Shaikh	Feroz	Mahim	Chennai	2	4678	3

 

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:

Cust_Id	LastName	FirstName	Address	City	Order_Id	OrderNo	Cust_Id
3	Shaikh	Feroz	Mahim	Chennai	1	78958	3
3	Shaikh	Feroz	Mahim	Chennai	2	4678	3
1	Shaikh	Moosa	Churchgate	Mumbai	3	2456	1
NULL	NULL	NULL	NULL	NULL	4	4562	4
NULL	NULL	NULL	NULL	NULL	5	4764	6

 

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:

Cust_Id	LastName	FirstName	Address	City	Order_Id	OrderNo	Cust_Id
3	Shaikh	Feroz	Mahim	Chennai	1	78958	3
3	Shaikh	Feroz	Mahim	Chennai	2	4678	3
1	Shaikh	Moosa	Churchgate	Mumbai	3	2456	1

 

Regards,

Please click here to see more SQL Server interview questions