Types of SQL Joins

Problem What are the different types of joins in SQL, and what does each one return?

Be ready to discuss

• INNER JOIN: only rows with a match on both sides; the default and the one that silently drops unmatched rows.
• LEFT (OUTER) JOIN: all rows from the left table plus matched right-side rows, NULL-filled where no match — and the classic anti-join idiom LEFT JOIN ... WHERE right.id IS NULL.
• RIGHT (OUTER) JOIN and FULL (OUTER) JOIN: the mirror of LEFT, and the union of both sides with NULL fill on either side.
• CROSS JOIN: the Cartesian product, when it is intentional (generating date/permutation grids) versus when it is an accidental missing predicate.
• SELF JOIN: joining a table to itself via aliases to compare rows within the same table — employee/manager hierarchies, finding duplicates, row-to-previous-row comparison.
• Predicate placement: why a filter on the outer table belongs in the ON clause rather than WHERE, since WHERE on a NULL-filled column silently degrades an outer join to an inner one.
• Execution: nested loop, hash join, and merge join strategies, and how indexes on the join keys drive the planner's choice.