How to do INNER JOIN, LEFT JOIN and RIGHT JOIN in LINQ as well as in SQL? Part 1

Today I will show you how to write LINQ Query as well as SQL Query. Both LINQ and SQL examples will clear your idea about INNER JOIN, LEFT JOIN and RIGHT JOIN. And also it will show you different results based on different JOINs.

Let’s create platform for it. Suppose we have following tables with data.

SELECT * FROM CUSTOMER

SELECT * FROM CITY

Now we will write SQL Queries for INNER JOIN, LEFT JOIN and RIGHT JOIN.

--Inner Join
SELECT cust.Name 
FROM CUSTOMER cust
 INNER JOIN CITY ct 
  ON ct.id = cust.city_id
  
--Left Join
SELECT cust.Name
FROM CUSTOMER cust
 LEFT JOIN CITY ct 
  ON ct.id = cust.city_id
 
--Right Join
SELECT ct.name AS City  
FROM CUSTOMER cust
 RIGHT JOIN CITY ct 
  ON ct.id = cust.city_id

After SQL, we will write LINQ Queries in C# for INNER JOIN, LEFT JOIN and RIGHT JOIN.

            
using (MyTestEntities db = new MyTestEntities())
{

 //Inner Join
 var results = (from Customer cust in db.Customers
                join City ct in db.Cities
                on cust.City_Id equals ct.Id
                select cust.Name).ToList();


 //Left Join
 results = (from Customer cust in db.Customers
            join City ct in db.Cities 
            on cust.City_Id equals ct.Id into Joined
            from lj in Joined.DefaultIfEmpty()
            select cust.Name).ToList();


 //Right Join (Just swap it)
 results = (from City ct in db.Cities
            join Customer cust in db.Customers 
            on ct.Id equals cust.City_Id into Joined
            from rj in Joined.DefaultIfEmpty()
            select ct.Name).ToList();

}

Now it's time to see results:

INNER JOIN Result for both queries will be like this

LEFT JOIN Result for both queries will be like this

RIGHT JOIN Result for both queries will be like this

Now you can compare SQL, LINQ and Result to get clear idea about it.