Join types
A join is a fundamental relational operation that combines rows from two or more datasets (or relations) based on a defined condition. CrateDB supports a variety of join types, enabling powerful queries across distributed data.
The diagram below illustrates the main types of joins using two sets, L (left) and R (right):
From left to right, top to bottom: Left Join, Right Join, Inner Join, Full Outer Join, and Cross Join.
Table of Contents
Cross Join
A cross join returns the Cartesian product of two or more relations. Each row in the left relation is paired with every row in the right relation. The result set contains all possible combinations of rows from both sides.
Example: If L has 3 rows and R has 4 rows, the cross join will return 12 rows.
Inner Join
An inner join returns only the rows that satisfy the join condition, typically a comparison between columns from each relation.
Only rows with matching values in both relations are included in the result.
Equi Join
An equi join is a specific type of inner join where the join condition is based solely on equality comparisons between specified columns in the participating relations.
Example:
SELECT * FROM L INNER JOIN R ON L.id = R.l_id;Outer Join
An outer join includes all rows that satisfy the join condition, as well as unmatched rows (known as dangling tuples) from one or both sides of the relation, padded with NULL values for the missing side.
Left Outer Join
Returns all rows from the left relation (L), along with matching rows from the right relation (R). If no match is found, NULL values are returned for the right-hand side.
Right Outer Join
Returns all rows from the right relation (R), along with matching rows from the left relation (L). If no match is found, NULL values are returned for the left-hand side.
Full Outer Join
Combines the results of both left and right outer joins. It returns all matching rows, as well as unmatched rows from both sides, with NULLs filling in where data is missing.
Last updated