LEFT JOIN vs LEFT OUTER JOIN

Before exploring the differences between Inner Join Vs Outer Join, let us first see what is a SQL JOIN?

A join clause is used to combine records or to manipulate the records from two or more tables through a join condition. The join condition indicates how columns from each table are matched against each other.

Join is based on a related column between these tables. A most common example is the join between two tables through the primary key column and foreign key column.

Suppose, we have got a table which contains employee Salary and there is another table which contains employee details.

In this case, there will be a common column like employee ID which will join these two tables. This Employee ID column would be the primary key of the employee details tables and foreign key in the employee salary table.

It is very important to have a common key between the two entities. You can think of a table as an entity and the key as a common link between the two tables which is used for join operation.

Basically, there are two types of Join in SQL i.e. Inner Join and Outer Join. Outer join is further subdivided into three types i.e. Left Outer Join, Right Outer Join, and Full Outer Join.

In this article, we will see the difference between Inner Join and Outer Join in detail. We will keep the Cross Joins and Unequal Joins out of the scope of this article.

What You Will Learn:

What is Inner Join?

An Inner Join returns only the rows that have matching values in both the tables (we are considering here the join is done between the two tables).

What is Outer Join?

The Outer Join includes the matching rows as well as some of the non-matching rows between the two tables. An Outer join basically differs from the Inner join in how it handles the false match condition.

There are 3 types of Outer Join:

• Left Outer Join: Returns all the rows from the LEFT table and matching records between both the tables.
• Right Outer Join: Returns all the rows from the RIGHT table and matching records between both the tables.
• Full Outer Join: It combines the result of the Left Outer Join and Right Outer Join.

Difference between Inner and Outer Join

[image source]

As shown in the above diagram, there are two entities i.e. table 1 and table 2 and both the tables share some common data.

An Inner Join will return the common area between these tables (the green shaded area in the diagram above) i.e. all the records that are common between table 1 and table 2.

A Left Outer Join will return all the rows from table 1 and only those rows from table 2 which are common to table 1 as well. A Right Outer Join will do just the opposite. It will give all the records from table 2 and only the corresponding matching records from table 1.

Furthermore, a Full Outer Join will give us all the records from table 1 and table 2.

Let us start with an example to make this clearer.

Suppose we have two tables: EmpDetails and EmpSalary.

EmpDetails Table:

EmployeeIDEmployeeName1John2Samantha3Hakuna4Silky5Ram6Arpit7Lily8Sita9Farah10Jerry

EmpSalary Table:

EmployeeIDEmployeeNameEmployeeSalary1John500002Samantha1200003Hakuna750004Silky250005Ram1500006Arpit8000011Rose9000012Sakshi4500013Jack250000

Let us do an Inner Join on these two tables and observe the result:

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary
FROM EmpDetails INNER JOIN EmpSalary
ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

EmployeeIDEmployeeNameEmployeeSalary1John500002Samantha1200003Hakuna750004Silky250005Ram1500006Arpit80000

In the above result set, you can see that Inner Join has returned the first 6 records that were present in both EmpDetails and EmpSalary having a matching key i.e. EmployeeID. Hence, if A and B are two entities, the Inner Join will return the result set that will be equal to ‘Records in A and B’, based on the matching key.

Let us now see what a Left Outer Join will do.

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary
FROM EmpDetails LEFT JOIN EmpSalary
ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

EmployeeIDEmployeeNameEmployeeSalary1John500002Samantha1200003Hakuna750004Silky250005Ram1500006Arpit800007LilyNULL8SitaNULL9FarahNULL10JerryNULL

In the above result set, you can see that the left outer join has returned all the 10 records from the LEFT table i.e. EmpDetails table and as the first 6 records are matching, it has returned the employee salary for these matching records.

As the rest of the records do not have a matching key in the RIGHT table, i.e. EmpSalary table, it has returned NULL corresponding to those. Since, Lily, Sita, Farah, and Jerry do not have a matching employee ID in the EmpSalary table, their Salary is showing up as NULL in the result set.

So, if A and B are two entities, then left outer join will return the result set that will be equal to ‘Records in A NOT B’, based on the matching key.

Now let us observe what the Right Outer Join does.

Query:

SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary
FROM EmpDetails RIGHT join EmpSalary
ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

EmployeeIDEmployeeNameEmployeeSalary1John500002Samantha1200003Hakuna750004Silky250005Ram1500006Arpit80000NULLNULL90000NULLNULL250000NULLNULL250000

In the above result set, you can see that the Right Outer Join has done just the opposite of the left join. It has returned all the salaries from the right table i.e. EmpSalary table.

But, as Rose, Sakshi, and Jack do not have a matching employee ID in the left table i.e. EmpDetails table, we have got their Employee ID and EmployeeName as NULL from the left table.

So, if A and B are two entities, then the right outer join will return the result set that will be equal to ‘Records in B NOT A’, based on the matching key.

Let us also see what will be the result set if we are doing a select operation on all the columns in both the tables.

Query:

SELECT *
FROM EmpDetails RIGHT JOIN EmpSalary
ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

EmployeeIDEmployeeNameEmployeeIDEmployeeNameEmployeeSalary1John1John500002Samantha2Samantha1200003Hakuna3Hakuna750004Silky4Silky250005Ram5Ram1500006Arpit6Arpit80000NULLNULL11Rose90000NULLNULL12Sakshi250000NULLNULL13Jack250000

Now, let us move into the Full Join.

A full outer join is done when we want all the data from both the tables irrespective of if there is a match or not. Hence, if I want all the employees even if I do not find a matching key, I will run a query as shown below.

Query:

SELECT *
FROM EmpDetails FULL JOIN EmpSalary
ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;

Result:

EmployeeIDEmployeeNameEmployeeIDEmployeeNameEmployeeSalary1John1John500002Samantha2Samantha1200003Hakuna3Hakuna750004Silky4Silky250005Ram5Ram1500006Arpit6Arpit800007LilyNULLNULLNULL8SitaNULLNULLNULL9FarahNULLNULLNULL10JerryNULLNULLNULLNULLNULL11Rose90000NULLNULL12Sakshi250000NULLNULL13Jack250000

You can see in the above result set that as the first six records are matching in both the tables, we have got all the data without any NULL. The next four records exist in the left table but not in the right table, thus the corresponding data in the right table is NULL.

The last three records exist in the right table and not in the left table, hence we have NULL in the corresponding data from the left table. So, if A and B are two entities, the full outer join will return the result set that will be equal to ‘Records in A AND B’, irrespective of the matching key.

Theoretically, it is a combination of Left Join and Right Join.

Performance

Let us compare an Inner Join against a Left Outer Join in the SQL server. Talking about the speed of operation, a left outer JOIN is obviously not faster than an inner join.

As per the definition, an outer join, be it the left or right, it has to perform all the work of an inner join along with the additional work null- extending the results. An outer join is expected to return a greater number of records which further increases its total execution time just because of the larger result set.

Thus, an outer join is slower than an inner join.

Moreover, there can be some specific situations where the Left join will be faster than an Inner join, but we can’t go on for replacing them with each other as a left outer join is not functionally equivalent to an inner join.

Let us discuss an instance where the Left Join might be faster than the Inner Join. If the tables involved in the join operation are too small, say they have less than 10 records and the tables do not possess sufficient indexes to cover the query, in that case, the Left Join is generally faster than Inner Join.

Let us create the two below tables and do an INNER JOIN and a LEFT OUTER JOIN between them as an Example:

CREATE TABLE #Table1
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Table1 (ID, Name) VALUES (1, 'A')
INSERT #Table1 (ID, Name) VALUES (2, 'B')
INSERT #Table1 (ID, Name) VALUES (3, 'C')
INSERT #Table1 (ID, Name) VALUES (4, 'D')
INSERT #Table1 (ID, Name) VALUES (5, 'E')

CREATE TABLE #Table2
(
ID int NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL
)
INSERT #Table2 (ID, Name) VALUES (1, 'A')
INSERT #Table2 (ID, Name) VALUES (2, 'B')
INSERT #Table2 (ID, Name) VALUES (3, 'C')
INSERT #Table2 (ID, Name) VALUES (4, 'D')
INSERT #Table2 (ID, Name) VALUES (5, 'E')

SELECT *
FROM #Table1 t1
INNER JOIN #Table2 t2
ON t2.Name = t1.Name

 IDNameIDName11A1A22B2B33C3C44D4D55E5E

SELECT * 
FROM (SELECT 38 AS bah) AS foo 
JOIN 
(SELECT 35 AS bah) AS bar
ON (55=55);

  IDNameIDName11A1A22B2B33C3C44D4D55E5E

As you can see above, both the queries have returned the same result set. In this case, if you view the execution plan of both the queries, then you will find that the inner join has costed more than the outer join. This is because, for an inner join, the SQL server does a hash match whereas it does nested loops for the left join.

A hash match is normally faster than the nested loops. But, in this case, as the number of rows is so small and there is no index to use (as we are doing join on name column), the hash operation has turned out a most expensive inner join query.

However, if you change the matching key in the join query from Name to ID and if there are a large number of rows in the table, then you will find that the inner join will be faster than the left outer join.

MS Access Inner and Outer Join

When you use multiple data sources in MS Access query, then you apply JOINs to control the records that you want to see, depending upon how the data sources are linked with each other.

In an inner join, only the related ones from both the tables are combined in a single result set. This is a default join in Access and the most frequently used one too. If you apply a join but do not explicitly specify what type of Join it is, then the Access assumes that it is an inner join.

In outer joins, all the related data from both the tables are combined correctly, plus all the remaining rows from one table. In full outer joins, all data are combined wherever possible.

Left Join vs Left Outer Join

In SQL server, the keyword outer is optional when you apply left outer join. Thus, it does not make any difference if you either write ‘LEFT OUTER JOIN’ or ‘LEFT JOIN’ as both are going to give you the same result.

A LEFT JOIN B is an equivalent syntax to A LEFT OUTER JOIN B.

Below is the list of equivalent syntaxes in the SQL server:

[image source]

Left Outer Join vs Right Outer Join

We have already seen this difference in this article. You can refer to the Left Outer Join and Right Outer Join queries and result set to see the difference.

The main difference between the Left Join and Right Join lies in the inclusion of non-matched rows. Left outer join includes the unmatched rows from the table which is on the left of the join clause whereas a Right outer join includes the unmatched rows from the table which is on the right of the join clause.

People do ask which is better to use i.e. Left join or Right join? Basically, they are the same type of operations except with their arguments reversed. Hence, when you ask which join to use, you are actually asking whether to write aa. It is just a matter of preference.

Generally, people prefer to use Left join in their SQL query. I would suggest that you should stay consistent in the way you in which are writing the query in order to avoid any confusion in interpreting the query.

We have seen all about Inner join and all type of Outer joins so far. Let us quickly summarize the difference between Inner Join and Outer Join.

Difference between Inner Join and Outer Join in Tabular Format

Inner JoinOuter JoinReturns only the rows that have matching values in both the tables.Includes the matching rows as well as some of the non-matching rows between the two tables.In case there are a large number of rows in the tables and there is an index to use, INNER JOIN is generally faster than OUTER JOIN.Generally, an OUTER JOIN is slower than an INNER JOIN as it needs to return more number of records when compared to INNER JOIN. However, there can be some specific scenarios where OUTER JOIN is faster.When a match is not found, it does not return anything.When a match is not found, a NULL is placed in the column value returned.Use INNER JOIN when you want to look up detailed information of any specific column.Use OUTER JOIN when you want to display the list of all the information in the two tables.INNER JOIN acts like a filter. There must be a match on both the tables for an inner join to return data.They act like data-add ons.Implicit join notation exists for inner join which enlists tables to be joined in the comma separated manner in the FROM clause.
Example: SELECT * FROM product, category WHERE product.CategoryID = category.CategoryID;No implicit join notation is there for outer join.Below is the visualization of an inner join:

Below is the visualization of an outer join

Inner and Outer Join vs Union

At times, we confuse Join and Union and this is also one of the most commonly asked questions in SQL interviews. We have already seen the difference between inner join and outer join . Now, let us see how a JOIN is different from a UNION.

UNION places a line of queries after each other, whereas join creates a cartesian product and subsets it. Thus, UNION and JOIN are completely different operations.

Let us run the below two queries in MySQL and see their result.

UNION Query:

SELECT 28 AS bah
UNION
SELECT 35 AS bah;

Result:

  Bah128235

JOIN Query:

SELECT * FROM
(SELECT 38 AS bah) AS foo
JOIN
(SELECT 35 AS bah) AS bar
ON (55=55);

Result:

  fooBar13835

A UNION operation puts the result of two or more queries into a single result set. This result set holds all the records that are returned through all the queries involved in the UNION. Thus, basically, a UNION is combining the two result sets together.

A join operation fetches data from two or more tables based on the logical relationships between these tables i.e. based on the join condition. In join query, data from one table is used to select records from another table. It lets you link similar data that is present over different tables.

In order to understand it very simply, you can say that a UNION combines rows from two tables whereas a join combines columns from two or more tables. Thus, both are used to combine the data from n tables, but the difference sits in how the data is combined.

Below are the pictorial representations of UNION and JOIN.

The above is a pictorial representation of a Join Operation depicting that each record in the result set contains columns from both the tables i.e. Table A and Table B. This result is returned based on the join condition applied in the query.

A join is generally the result of denormalization (opposite of normalization) and it uses the foreign key of one table to look up the column values by employing primary key in another table.

The above is a pictorial representation of a UNION Operation depicting that each record in the result set is a row from either of the two tables. Thus, the result of the UNION has combined the rows from Table A and Table B.

Further reading =>> MySQL UNION explained with examples

Conclusion

In this article, we have seen the major differences between the Inner Join and Outer Join in SQL. We also saw the classification of an Outer Join i.e. Left join, Right join and Full join. We have seen how each of these join types do work and how they vary from each other.

We also did some performance comparison between these join types. We also discussed how a join is different from a union.

Also Read =>> MySQL Join Types

Hope this article would have helped you in clearing your doubts regarding the differences between the various join types. We are sure that this indeed will make you decide which join type to choose from based upon the desired result set.

Why LEFT join is called left outer join?

Left Outer Join: Left Outer Join returns all the rows from the table on the left and columns of the table on the right is null padded. Left Outer Join retrieves all the rows from both the tables that satisfy the join condition along with the unmatched rows of the left table.

Is left outer join the same as right join?

The key difference between a left outer join, and a right outer join is that in a left outer join it's the table in the FROM clause whose all rows are returned. Whereas, in a right outer join we are returning all rows from the table specified in the join clause. See you in the next lesson.

IS LEFT join default inner or outer?

INNER is the default; LEFT , RIGHT , and FULL imply an outer join."

Why left outer join is faster than inner join?

If the tables involved in the join operation are too small, say they have less than 10 records and the tables do not possess sufficient indexes to cover the query, in that case, the Left Join is generally faster than Inner Join. As you can see above, both the queries have returned the same result set.