What Each Join Type Must Preserve: INNER, LEFT, RIGHT and FULL
An INNER JOIN keeps only rows that matched on both sides; a LEFT JOIN keeps every row of the left table and fills the right-hand columns with NULL when nothing matched; RIGHT is the mirror and FULL keeps unmatched rows from both. That promise is a contract the engine must honour, and it limits which join algorithms and which join orders are legal. So an outer join genuinely gives the optimizer less freedom than an inner join does.
Questions this Concept answers
- Why does an outer join leave the optimizer less freedom than an inner join over the same tables?
Combining Tables in SQL with INNER LEFT RIGHT and FULL OUTER JOIN
A SQL join is a relational operation that combines rows from two or more tables by evaluating a predicate over shared key columns, producing a single result relation. The join type determines which n…