LEFT JOIN vs. LEFT OUTER JOIN in SQL Server, which one is better?

Question

Aryan Kumar · Answer

Both left join and left outer join are used to join two tables together, but there is a subtle difference between the two.
A left join will return all rows from the left table, even if there are no matching rows in the right table. A
left outer join will also return all rows from the left table, but it will also return
null values for the columns in the right table where there are no matching rows.
In general, left join is used when you want to return all rows from the left table, regardless of whether there are matching rows in the right table.
left outer join is used when you want to return all rows from the left table, and you also want to return
null values for the columns in the right table where there are no matching rows.
Here is a table that summarizes the differences between left join and
left outer join:
Feature
Left join
Left outer join
Returns all rows from the left table
Yes
Yes
Returns null values for the columns in the right table where there are no matching rows
No
Yes
Used when you want to return all rows from the left table
Yes
Yes
Used when you want to return null values for the columns in the right table where there are no matching rows
No
Yes
The best way to choose between left join and left outer join depends on your specific needs. If you only need to return all rows from the left table, then you should use
left join. If you also need to return null values for the columns in the right table where there are no matching rows, then you should use
left outer join.
For example, the following code will use left join to join the 
Customers and Orders tables:
SQL
SELECT *
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
This code will return all rows from the Customers table, even if there are no matching rows in the
Orders table.
The following code will use left outer join to join the Customers and
Orders tables:
SQL
SELECT *
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
This code will also return all rows from the Customers table, but it will also return
null values for the OrderID column in the Customers table where there are no matching rows in the
Orders table.

forum

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server, which one is better?

Revati S Misra

Can you answer this question?

1 Answers

Liked By

Feature	Left join	Left outer join
Returns all rows from the left table	Yes	Yes
Returns null values for the columns in the right table where there are no matching rows	No	Yes
Used when you want to return all rows from the left table	Yes	Yes
Used when you want to return null values for the columns in the right table where there are no matching rows	No	Yes