Difference Between Left Join and Left Outer Join in SQL

 

Introduction: Hello Guys, “Welcome back” Today, i am here with another one new great article. In this article I will explain about the difference between Left Join and Left Outer Join in SQL Server. Joins are used to combine or merge the data from more than one or multiple tables in databases. When we are working with relational databases, the SQL Query left join and left outer join are often used exchange ably. In this article, I am going to explain about the difference of left join vs. left outer join, syntax, behavior, and when need to use it.

Left Join

Left join in SQL fetch data from two tables based on a specific column in each table. When performing operations on data across multiple tables in a relational database, the common approach is to use a specific column in each table to establish a relationship or to filter and combine data. A LEFT JOIN returns all records from the "left" table (the first table specified in the FROM clause) and the matching records from the "right" table (the table specified after the LEFT JOIN keyword).

 

 

If there are no matching records in the right table for a given record in the left table, the columns from the right table will contain NULL values in the result set. It includes all records from the left table, even if no matching records exist in the right table. If a row in the left table has no corresponding match in the right table, the right table’s columns have null values for the particular rows. All the rows from the right table that do not have matching rows in the left table are excluded from the output

The syntax of left join:

SELECT name_of_collumns_need_to_show 

FROM left_table 

LEFT JOIN right_table 

ON left_table.column_name = right_table.column_name;

 In this syntax, name_of_collumns_need_to_show means the
columns that we want to fetch for showing in our output left_table  and right_table  are the tables of the  database. we . left join is operation. left_table.column_name and right_table.column_name are the column names we use to match
the rows in eft_table  and right_table.

To understand the concept of  left join, I am explaining with example.

Left join example

Suppose there is table named Customers with the columns name customer_id, name, and city for customers at a Confectionary store.

customer_id	name	city
1	Bharat	Chandigarh
2	Vikram	Amritsar
3	Ruhika	Batala
4	Nancy	Jammu

Suppose there is table named Orders with the columns name order_id, cust_id, and product for the orders.

order_id	Customer_id	product
101	1	Laptop
102	2	Smartphone
103	1	Headphone
104	5	Earphone

Now, if you want to fetch the customer_id, name, city, and product for the customers, we need to use
left join on the Customers and Orders table, as shown in the following SQL
statement: 

SELECT customer_id, name, city, product  

FROM Customers LEFT JOIN Orders  

ON Customers.customer_id = Orders.cust_id; 

After execution of this SQL Statement, It shows the following output

After executing this statement, we get the following output:

 

 

Customer_id	Name	city	Product
1	Bharat	Chandigarh	Headphones
1	Bharat	Chandigarh	Laptop
2	Vikram	Amritsar	Smartphone
3	Ruhika	Batala	Null
4	Nancy	Jammu	Null

 

You can check that all the rows from the Customers table are fetched as output of the table. For the customer ID 3 and 4, there is no any data available in the Orders table. Due to this, the city and product columns in the output table for these customer IDs show value as NULL, as these columns are fetched from the Orders table. Also, there is no matching customer for the customer id 5 from the Orders table. so, this row from the Orders table is not showing in the fetched output.

Left outer join

The left outer join in SQL is the same like as the left join. It fecth all the records from the left table and the matching records from the right table.

 If no match is found in the right table, it will return NULL values as right table’s columns.

The syntax for the left outer join is as follows:

SELECT name_of_collumns_need_to_show 

FROM left_table 

LEFT OUTER JOIN right_table 

ON left_table.column_name = right_table.column_name;

You can check that the syntax for the left outer join is the same like as that for the left join with an additional OUTER keyword. Anyway, just syntax difference in the SQL Statement no effect on how it will works. When we executed the left outer join statement,  It gives the same output like as the left join statement.

Left outer join example

To see how left outer join works, let’s again fetch the customer_id, name, city, and product from the Customers and the Orders table using LEFT OUTER JOIN.

 

SELECT customer_id, name, city, product  

FROM Customers LEFT OUTER JOIN Orders  

ON Customers.customer_id = Orders.cust_id; 

 

If we execute this statement, we get the following output.

Customer_id	Name	city	Product
1	Bharat	Chandigarh	Headphones
1	Bharat	Chandigarh	Laptop
2	Vikram	Amritsar	Smartphone
3	Ruhika	Batala	Null
4	Nancy	Jammu	Null

Conclusion: In above code, I explained about the difference between Left Join and Left Outer Join in SQL. This code is very helpful for every developer. Bye Bye and take care of you all Developers. We will come back shortly with the new article.

 

Regards

Using Asp.net