What is the difference between "INNER JOIN" and "OUTER JOIN"?

Sql Join Inner Join Outer Join

When working with relational databases, it is common to combine rows from multiple tables based on a related column between them. This is done using joins, which are used to retrieve and combine data from two or more tables in a database. Two common types of joins are "INNER JOIN" and "OUTER JOIN". In this article, we will explore the difference between these joins and also discuss LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

The INNER JOIN

The INNER JOIN is used to return only the rows from the tables that have a matching value in both tables. It selects records that have matching values in both tables involved in the join. Let's consider an example to understand this better.

Example:

We have two tables: Customers and Orders. The Customers table contains information about customers, and the Orders table contains information about customer orders. Both tables have a common column, customer_id, which can be used to join the two tables.


        Customers Table:
        +----+----------+
        | id | name     |
        +----+----------+
        | 1  | John     |
        | 2  | Mary     |
        | 3  | David    |
        +----+----------+
        
        Orders Table:
        +----+-------------+-----------+
        | id | customer_id | product   |
        +----+-------------+-----------+
        | 1  | 1           | Laptop    |
        | 2  | 1           | Smartphone|
        | 3  | 3           | Headphones|
        +----+-------------+-----------+
        
        SQL Query:
        SELECT Customers.name, Orders.product
        FROM Customers
        INNER JOIN Orders
        ON Customers.id = Orders.customer_id;

The above query will return only the rows where there is a matching customer_id in both tables:


        +------+-----------+
        | name | product   |
        +------+-----------+
        | John | Laptop    |
        | John | Smartphone|
        | David| Headphones|
        +------+-----------+

The OUTER JOIN

The OUTER JOIN, on the other hand, returns all rows from one table and the matched rows from the other table. If the rows in the joined tables do not match, the result will have NULL values in the columns from the second table. There are three types of OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

The LEFT OUTER JOIN

The LEFT OUTER JOIN returns all rows from the left table (the table before the JOIN keyword), and the matched rows from the right table. If there is no match, NULL values are returned for the columns of the right table. Let's consider an example to illustrate this:

Example:

We have two tables: Customers and Orders. Both tables contain the same data as mentioned in the INNER JOIN example above.


        SQL Query:
        SELECT Customers.name, Orders.product
        FROM Customers
        LEFT OUTER JOIN Orders
        ON Customers.id = Orders.customer_id;

The above query will return all the rows from the left table (Customers) and the matching rows from the right table (Orders). If there is no match, NULL values will be returned for the columns from the right table:


        +-------+-----------+
        | name  | product   |
        +-------+-----------+
        | John  | Laptop    |
        | John  | Smartphone|
        | Mary  | NULL      |
        | David | Headphones|
        +-------+-----------+

The RIGHT OUTER JOIN

The RIGHT OUTER JOIN is similar to the LEFT OUTER JOIN, but it returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for the columns of the left table. Let's consider the same example:


        SQL Query:
        SELECT Customers.name, Orders.product
        FROM Customers
        RIGHT OUTER JOIN Orders
        ON Customers.id = Orders.customer_id;

The above query will return all the rows from the right table (Orders) and the matching rows from the left table (Customers). If there is no match, NULL values will be returned for the columns from the left table:


        +-------+-----------+
        | name  | product   |
        +-------+-----------+
        | John  | Laptop    |
        | John  | Smartphone|
        | David | Headphones|
        | NULL  | Tablet    |
        +-------+-----------+

The FULL OUTER JOIN

The FULL OUTER JOIN returns all rows from both tables, matching them where possible, and returning NULL values for the non-matching rows. Let's consider the same example:


        SQL Query:
        SELECT Customers.name, Orders.product
        FROM Customers
        FULL OUTER JOIN Orders
        ON Customers.id = Orders.customer_id;

The above query will return all the rows from both tables, matching them where possible. If there is no match, NULL values will be returned for the non-matching rows:


        +-------+-----------+
        | name  | product   |
        +-------+-----------+
        | John  | Laptop    |
        | John  | Smartphone|
        | Mary  | NULL      |
        | David | Headphones|
        | NULL  | Tablet    |
        +-------+-----------+

Conclusion

In conclusion, the difference between INNER JOIN and OUTER JOIN lies in how they handle unmatched rows. The INNER JOIN only returns the rows that have matching values in both tables, while the OUTER JOIN returns all the rows from one table and the matching and non-matching rows from the other table. The LEFT OUTER JOIN returns all rows from the left table and the matching rows from the right table, the RIGHT OUTER JOIN returns all rows from the right table and the matching rows from the left table, and the FULL OUTER JOIN returns all rows from both tables, matching them where possible and returning NULL values for non-matching rows.

Related Questions