JOIN
JOIN is the SQL operation combining rows of multiple tables by a join condition; types include inner, left/right/full outer, and cross joins.
Purpose
To combine related data scattered across tables by join conditions.
Mechanism
INNER JOIN: matched rows only. LEFT JOIN: all left rows + right matches (NULL if none). RIGHT JOIN: all right rows. FULL JOIN: all rows of both. CROSS JOIN: Cartesian product. Implementations: nested loop, merge, hash joins.
Use cases
- Multi-table relational queries
- Primary-foreign key data retrieval
- Report aggregation
- Data warehouse star joins
Examples
- Input
users JOIN orders ON users.id=orders.uidOutputUsers joined with their ordersInner join
Common errors
- Unindexed JOIN columns cause full table scans
- Index the join columns
Related terms
Index
An index is an auxiliary data structure (e.g., B+ tree, hash) maintained to speed up queries, trading space for time at the cost of write overhead. GLOSSARY
Primary Key
A primary key is the column or column combination that uniquely identifies each row in a table — non-null and unique; the row identity. GLOSSARY
Foreign Key
A foreign key is a column referencing the primary key of another table, establishing referential integrity between tables and preventing orphaned references. GLOSSARY
SQL
SQL (Structured Query Language) is the standard query language of relational databases, defining and manipulating data declaratively (DDL/DML/DQL/DCL). GLOSSARY