Difference between Inner & Outer Join (Left)
Introduction
Joins are an essential part of relational databases. They are used to combine data from two or more tables. There are different types of joins like inner, outer, left, right and many more. In this article, we will discuss the difference between inner and outer join specifically the left join.
Inner Join
An inner join is a join that returns only the rows that have matching values in both the tables. In other words, it only returns the rows for which there is a match in both tables. Let’s take an example of two tables:
| Table 1 | Table 2 | |
|---|---|---|
| ID | Name | ID |
| 1 | John | 1 |
| 2 | Jane | 2 |
| 3 | Bob | 3 |
If we perform an inner join on these tables using the ID column:
SELECT *
FROM table1
INNER JOIN table2
ON table1.ID = table2.ID
The result will be:
| ID | Name | ID |
|---|---|---|
| 1 | John | 1 |
| 2 | Jane | 2 |
| 3 | Bob | 3 |
As you can see, only the rows with matching IDs are returned.
Left Outer Join
A left outer join returns all the rows from the left table and the matching rows from the right table. If there is no match, the right table columns will have NULL values. Let’s take an example:
| Table 1 | Table 2 | |
|---|---|---|
| ID | Name | ID |
| 1 | John | 1 |
| 2 | Jane | 2 |
| 3 | Bob | 4 |
If we perform a left outer join on these tables using the ID column:
SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.ID = table2.ID
The result will be:
| ID | Name | ID |
|---|---|---|
| 1 | John | 1 |
| 2 | Jane | 2 |
| 3 | Bob | null |
As you can see, all the rows from the left table (table1) are returned, and the matching row from table2 is returned. Since there is no matching row in table2 for ID=3, the table2 columns have NULL values.
FAQs
Q1) What is the difference between inner join and outer join?
A) An inner join returns only the matching rows from both tables, while an outer join returns all the rows from one table and the matching rows from the other table
Q2) What is the purpose of left outer join?
A) The purpose of a left outer join is to return all the rows from the left table and matching rows (if any) from the right table.
Q3) What is the syntax for inner join and left outer join?
A) The syntax for inner join and left outer join is:
SELECT *
FROM table1
INNER JOIN table2
ON table1.ID = table2.ID
SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.ID = table2.ID