SQL Joins
Combining rows from multiple tables with INNER, LEFT, RIGHT, FULL, and CROSS joins, including NULL behavior and performance considerations.
A join combines rows from two or more tables by matching them on a join condition, typically a shared key. Joins are how relational databases reconstruct the connected information that normalization deliberately split apart. The most common form is the inner join, which returns only rows that have a match on both sides: SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id yields one row per order with the matching customer attached, and drops orders whose customer is missing.
Outer joins preserve unmatched rows. A LEFT JOIN returns every row of the left table, filling NULLs for the right side where no match exists; a RIGHT JOIN does the mirror image, and a FULL OUTER JOIN keeps rows from both sides. This makes outer joins the standard tool for finding orphans — for example, customers with no orders — and for reports that must not silently drop missing data. A CROSS JOIN pairs every row of one table with every row of the other (a Cartesian product), which is useful for generating combinations but dangerous at scale; a self-join joins a table to itself, used for hierarchies such as employees and their managers.
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'TR';
NULL semantics matter: NULL never equals NULL in a join condition, so rows with NULL keys never match each other, and WHERE clauses filtering on joined columns can quietly discard unmatched rows when an outer join is used. Performance is governed by indexes: joining on an indexed key allows the database to look up matches directly instead of scanning tables, and query planners choose between nested-loop, hash, and merge join strategies based on the sizes and indexes involved — the same indexing principles covered in SQL indexes and query performance.
Tags
databases queries relational algebra sql
Related articles
Click here for easy-to-read helpful e-books for anyone, anywhere, and about anything