A join combines rows from two tables based on a related column, and the join type decides what happens to rows that have no match.
INNER JOIN returns only the rows that match in both tables.
LEFT JOIN returns all rows from the left table and the matching rows from the right.
RIGHT JOIN returns all rows from the right table and the matching rows from the left.
FULL OUTER JOIN returns all rows from both tables, with NULL where a side has no match.
CROSS JOIN returns the Cartesian product, every row paired with every row.
Rows with a NULL join key never match, so an inner join drops them.
T-SQL adds CROSS APPLY and OUTER APPLY, which run a table-valued function or subquery once for each left row. CROSS APPLY keeps only left rows that get a result, and OUTER APPLY keeps every left row, with NULL when nothing comes back.
Example:
-- INNER JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.ID;
Rewriting in plainer words…
This answer doesn't lend itself to a diagram - it reads best . No credits were charged.
