Jointype

Jointype is a term used in relational databases and query processing to describe the category of a join operation that combines two data sources based on a join condition. It defines how rows from the input sources are matched, what criteria are used, and what rows appear in the result.

Common jointypes include inner join, left outer join, right outer join, full outer join, and cross join. Natural join and certain semi-join or anti-join variants are also considered jointypes in broader discussions. An inner join returns only the rows with matching values in both inputs. Left outer join preserves all rows from the left input and fills with nulls when there is no matching row on the right. Right outer join does the symmetric operation for the right input. Full outer join returns rows from both sides, pairing matches and filling with nulls where there is no counterpart. Cross join produces a Cartesian product with every combination of rows from the two inputs. Natural join uses all common columns and equality on those columns, while semi- and anti-joins describe existence or non-existence of a match, typically influencing which rows are returned and whether right-side data is projected.

In SQL and other query languages, the jointype is specified or implied by join syntax (for example, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, NATURAL JOIN) and by the use of predicates in the ON or WHERE clause. The choice of jointype impacts result cardinality, null handling, and query plans, and is a central consideration in query optimization and execution.