When it comes to querying data from multiple tables, SQL offers two options: INNER JOIN and OUTER JOIN. While these two types of joins may seem similar, they have distinct differences that can greatly impact the results of a query. In this article, we will explore the difference between INNER JOIN and OUTER JOIN and when to use each type.
INNER JOIN, also known as simple or equi-join, is used to combine rows from two or more tables based on a common column. This common column is referred to as the join key, and it must exist in both tables. The rows from the tables that do not have matching values in the join key will not be included in the result set.
Let's consider an example to better understand INNER JOIN. We have two tables, "Customers" and "Orders", with the following data:
Customers Table:
| CustomerID | Name | City |
|------------|----------|----------|
| 1 | John | New York |
| 2 | Mary | London |
| 3 | David | Paris |
| 4 | Sarah | Tokyo |
Orders Table:
| OrderID | CustomerID | Product | Quantity |
|---------|------------|----------|----------|
| 1 | 1 | Laptop | 2 |
| 2 | 1 | Phone | 1 |
| 3 | 2 | Tablet | 3 |
| 4 | 3 | Headphones | 1 |
| 5 | 4 | Camera | 1 |
If we want to retrieve the name of the customers and their orders, we can use INNER JOIN on the "Customers" and "Orders" tables using the CustomerID as the join key. The query will look like this:
SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
The result set will be:
| Name | Product | Quantity |
|--------|----------|----------|
| John | Laptop | 2 |
| John | Phone | 1 |
| Mary | Tablet | 3 |
| David | Headphones | 1 |
| Sarah | Camera | 1 |
As you can see, only the rows with matching CustomerID in both tables are included in the result set. This is the basic functionality of INNER JOIN.
On the other hand, OUTER JOIN is used to combine rows from two or more tables, including the rows that do not have matching values in the join key. There are three types of OUTER JOIN: LEFT, RIGHT, and FULL. The difference between them lies in which table's rows are included in the result set.
- LEFT OUTER JOIN: This type of join includes all the rows from the left table and only the matching rows from the right table. The rows from the left table that do not have matching values in the join key will have NULL values for the columns of the right table.
- RIGHT OUTER JOIN: This is the opposite of LEFT OUTER JOIN. It includes all the rows from the right table and only the matching rows from the left table. The rows from the right table that do not have matching values in the join key will have NULL values for the columns of the left table.
- FULL OUTER JOIN: This type of join includes all the rows from both tables, regardless of whether they have matching values in the join key or not. The rows that do not have matching values will have NULL values for the columns of the other table.
To better understand OUTER JOIN, let's consider the same example but with a twist. We want to retrieve the name of all customers and their orders, even if they have not placed any orders yet. We can achieve this by using a LEFT OUTER JOIN on the "Customers" and "Orders" tables:
SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
The result set will be:
| Name | Product | Quantity |
|--------|----------|----------|
| John | Laptop | 2 |
| John | Phone | 1 |
| Mary | Tablet | 3 |
| David | Headphones | 1 |
| Sarah | Camera | 1 |
| NULL | NULL | NULL |
As you can see, the LEFT OUTER JOIN included all the rows from the left table (Customers) and only the matching rows from the right table (Orders). Since Sarah does not have any orders, her rows will have NULL values for the columns of the right table.
In conclusion, INNER JOIN and OUTER JOIN are two types of joins used in SQL to retrieve data from multiple tables. While INNER JOIN
