Differences between Inner Join and Outer Join
Inner Join vs. Outer Join[edit]
In SQL (Structured Query Language), `JOIN` clauses are used to combine rows from two or more tables based on a related column between them.[1] 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.[2] 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.[3][4]
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.[5] 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.[4] 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.[3] 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.[2]
- **Right Outer Join** (or `RIGHT JOIN`): Returns all records from the right table, and the matched records from the left table.[2]
- **Full Outer Join** (or `FULL JOIN`): Returns all records when there is a match in either the left or the right table.[3]
Comparison Table[edit]
| Category | Inner Join | Outer Join |
|---|---|---|
| **Matching Rows** | Returns only the rows that have matching values in both tables.[5] | Returns matching rows, as well as non-matching rows from one or both tables.[3] |
| **Non-Matching Rows** | Excludes rows from both tables that do not match the join condition.[5] | 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.[4] | The result can be the records from the left table, right table, or both, in addition to the intersection.[4] |
| **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.[5] | Finding records that do not have corresponding entries in the other table, or when all data from a primary table is required. |
See also[edit]
References[edit]
- ↑ "w3schools.com". Retrieved December 15, 2025.
- ↑ 2.0 2.1 2.2 "freecodecamp.org". Retrieved December 15, 2025.
- ↑ 3.0 3.1 3.2 3.3 "datacamp.com". Retrieved December 15, 2025.
- ↑ 4.0 4.1 4.2 4.3 "stackoverflow.com". Retrieved December 15, 2025.
- ↑ 5.0 5.1 5.2 5.3 "w3schools.com". Retrieved December 15, 2025.
