Dwg: Article written and Venn diagram created.

2025-12-15T12:46:38Z

Article written and Venn diagram created.

New page

== Inner Join vs. Outer Join ==
In SQL (Structured Query Language), `JOIN` clauses are used to combine rows from two or more tables based on a related column between them.<ref name="ref1" /> The two main categories of joins are the inner join and the outer join. The primary distinction between them lies in how they handle records that do not have a matching record in the other table.<ref name="ref2" /> An inner join returns only the rows where the join condition is met in both tables, whereas an outer join can also return rows where no match is found in the other table.<ref name="ref3" /><ref name="ref4" />

An **Inner Join** selects only the records that have matching values in both tables. If a row in the first table does not have a corresponding match in the second table, it is excluded from the result set.<ref name="ref5" /> This is the most common type of join and is often used to retrieve data that has a clear relationship across tables, such as customers and their orders.<ref name="ref4" /> The `INNER JOIN` keyword is used, although `JOIN` by itself defaults to an inner join in most SQL implementations.

An **Outer Join** returns all records from at least one of the tables, even if there is no matching record in the other table.<ref name="ref3" /> For rows that do not have a match, the columns from the other table will have `NULL` values. This type of join is useful for identifying records in one table that lack corresponding records in another. There are three types of outer joins:
* **Left Outer Join** (or `LEFT JOIN`): Returns all records from the left table, and the matched records from the right table.<ref name="ref2" />
* **Right Outer Join** (or `RIGHT JOIN`): Returns all records from the right table, and the matched records from the left table.<ref name="ref2" />
* **Full Outer Join** (or `FULL JOIN`): Returns all records when there is a match in either the left or the right table.<ref name="ref3" />

=== Comparison Table ===
{| class="wikitable"
|-
! Category !! Inner Join !! Outer Join
|-
| **Matching Rows** || Returns only the rows that have matching values in both tables.<ref name="ref5" /> || Returns matching rows, as well as non-matching rows from one or both tables.<ref name="ref3" />
|-
| **Non-Matching Rows** || Excludes rows from both tables that do not match the join condition.<ref name="ref5" /> || Includes non-matching rows from one or both tables, filling the columns of the non-matching table with `NULL` values.
|-
| **Result Set** || The result is the intersection of the two tables based on the join condition.<ref name="ref4" /> || The result can be the records from the left table, right table, or both, in addition to the intersection.<ref name="ref4" />
|-
| **Common Keywords** || `INNER JOIN`, `JOIN`. || `LEFT OUTER JOIN`, `RIGHT OUTER JOIN`, `FULL OUTER JOIN`.
|-
| **Typical Use Case** || Retrieving a dataset of records that are present in both tables being joined.<ref name="ref5" /> || Finding records that do not have corresponding entries in the other table, or when all data from a primary table is required.
|}

[[File:Venn_diagram_Differences_between_Inner_Join_versus_Outer_Join_comparison.png|thumb|center|800px|alt=Venn diagram for Differences between Inner Join and Outer Join|Venn diagram comparing Differences between Inner Join and Outer Join]]

== See also ==
* [[Join (SQL)]]
* [[Relational database]]
* [[Structured Query Language]]

== References ==
<references>
<ref name="ref1">[https://www.w3schools.com/sql/sql_join.asp "w3schools.com"]. Retrieved December 15, 2025.</ref>
<ref name="ref2">[https://www.freecodecamp.org/news/sql-join-types-inner-join-vs-outer-join-example/ "freecodecamp.org"]. Retrieved December 15, 2025.</ref>
<ref name="ref3">[https://www.datacamp.com/tutorial/inner-join-vs-outer-join "datacamp.com"]. Retrieved December 15, 2025.</ref>
<ref name="ref4">[https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join "stackoverflow.com"]. Retrieved December 15, 2025.</ref>
<ref name="ref5">[https://www.w3schools.com/sql/sql_join_inner.asp "w3schools.com"]. Retrieved December 15, 2025.</ref>
</references>

[[Category:Comparisons]]

Differences between Inner Join and Outer Join - Revision history

Dwg: Article written and Venn diagram created.