Author
Home > Technology > What is the Difference Between Inner Join and Outer Join?

Guilherme Mazui

•3 minutes to read

What is the Difference Between Inner Join and Outer Join?

🆚 Go to Comparative Table 🆚

The main difference between an inner join and an outer join in SQL lies in the way they combine data from two tables. Here are the key differences:

  1. Inner Join:
  • Returns only the matching records between the tables.
  • Requires at least a match in comparing the two tables.
  • In a Venn diagram, an inner join returns the overlapping part where the tables intersect.
  • Example syntax: SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name or SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name.
  1. Outer Join:
  • Returns the combined tuple from a specified table even if the join condition fails.
  • In a Venn diagram, an outer join returns the union of the two tables.
  • Types of outer joins include:
    • Left Outer Join: Returns all the rows from the first table, even if there are no matches in the second table.
    • Right Outer Join: Returns all the rows from the second table, even if there are no matches in the first table.
    • Full Outer Join: Returns all rows from both tables, with missing data filled in with NULL.

In summary, an inner join focuses on returning only the matching and overlapping data between two tables, while an outer join returns both matched and unmatched data, providing a more comprehensive view of the merged tables.

On this page
  • What is the Difference Between Inner Join and Outer Join?
  • Comparative Table: Inner Join vs Outer Join

    • Comparative Table: Inner Join vs Outer Join

    The main difference between an inner join and an outer join lies in the way they handle non-matching data. Here is a comparison between the two:

    Inner Join Outer Join
    Returns only the common rows between two tables. Returns both matching and non-matching rows from one or both tables.
    Each resulting row has matching data from both tables. Some resulting rows may have null values for one or both tables.
    Less data duplication, as it returns only the matching rows. More data duplication, as it returns both matching and non-matching rows.
    Faster execution, as it processes only the matching rows. Slower execution, as it processes both matching and non-matching rows.
    Suitable for finding relationships between related data in both tables. Suitable for finding relationships and including non-related data in one or both tables.

    There are three types of outer joins: left outer join, right outer join, and full outer join. Left outer join returns all rows from the left table and matching rows from the right table, with null values for non-matching rows from the right table. Right outer join returns all rows from the right table and matching rows from the left table, with null values for non-matching rows from the left table. Full outer join returns all rows from both tables, with null values for non-matching rows from either table.

    Read more:

    Guilherme Mazui

    Guilherme Mazui is graduated in journalism from the Federal University of Minas Gerais (UFMG) and a master's degree in Communication from the University of São Paulo (USP). In addition, he has experience in advertising writing and has worked as a content editor in several companies.