Logical Joins
A join combines rows from two tables on a matching condition.
- An inner join keeps only rows that match in both tables.
- A left outer join keeps all left rows, filling unmatched right columns with nulls.
- A full outer join keeps unmatched rows from both sides.
- A cross join pairs every left row with every right row.
Physical Join Algorithms
The planner picks how to execute the join.
- A nested loop scans the inner table for each outer row, good for small inputs.
- A hash join builds a hash table on one side, great for large unsorted equality joins.
- A merge join walks two sorted inputs together, efficient when both are ordered.
Why It Matters
Choosing the wrong join condition can explode results into a near cartesian product. Knowing the physical algorithm helps explain why an indexed, sorted, or small input runs so much faster.
Key idea
Joins combine tables logically as inner or outer, while the engine physically runs them as nested loop, hash, or merge joins.