What is an inner join in SQL? And what is an outer join?

Joins in SQL: Inner, Outer, and Semi

Joins are one of the most fundamental concepts in SQL, yet they are frequently misunderstood. The differences between inner joins, outer joins, and semi-joins can be subtle, especially when conditions and aggregates are involved. This article clarifies how each join type behaves using PostgreSQL syntax and examples.

Implicit vs. explicit join syntax

Before examining join types, it helps to understand the two ways to write a join. An implicit join lists tables in the FROM clause and places the join condition in the WHERE clause:

PgSQL

An explicit join uses the JOIN keyword and puts the condition in the ON clause:

PgSQL

These two queries are semantically identical. PostgreSQL's optimizer generates the same execution plan for both in most cases. However, there is one configuration detail worth noting. The join_collapse_limit setting in postgresql.conf controls how many explicit joins the planner will reorder:

PgSQL

With the default value, the optimizer can reorder up to 8 explicit joins freely. Beyond that, explicit joins are processed in the order written in the query. Implicit joins, on the other hand, are always subject to automatic reordering. In practice, this rarely matters for typical applications, but it becomes relevant for very complex queries with many tables.

Inner vs. outer joins

The core distinction is straightforward: an inner join returns only rows that have matching entries on both sides. An outer join always returns all rows from one side (or both), filling in NULL values where no match exists on the other side.

Consider a LEFT JOIN, which takes all rows from the left table and finds matches on the right:

PgSQL

The mirror image is a RIGHT JOIN, which behaves the same way but with the roles of the tables swapped:

PgSQL

A FULL JOIN takes all rows from both sides. Rows without a counterpart on the opposite side are padded with NULL values:

PgSQL

Common outer join pitfalls

A frequent mistake is adding extra conditions to the ON clause of an outer join, expecting the result set to shrink. This does not happen. The ON condition only determines which rows match; an outer join still produces all rows from the preserved side. Consider this query:

PgSQL

This query returns the same number of rows as the unmodified left join. The additional condition merely turns some matching values into NULL. The result set is not filtered. This behavior often surfaces as an apparent performance problem or, worse, as incorrect results when combined with aggregate functions:

PgSQL

Users expect certain rows to be excluded by the extra condition, but outer join semantics do not work that way. If filtering is the goal, the condition belongs in the WHERE clause.

Semi-joins and the IN operator

A semi-join is conceptually different from a regular join. Consider an IN subquery:

PgSQL

The IN operator acts as an implicit DISTINCT filter. The execution plan shows this duplicate elimination, often through a HashAggregate node:

PgSQL

The counterpart, NOT IN, removes rows that match any value in the subquery:

PgSQL

Understanding whether a query needs duplicate elimination or full row preservation is the key to choosing the right join type and avoiding subtle bugs.