An INNER JOIN connects records from two tables by evaluating a join predicate that matches related column keys.
It represents the relational intersection of two tables based on the specified join condition.
The join predicate is stated explicitly in the ON clause, typically matching a foreign key to a primary key.
If a row in the left table matches a row in the right table, the engine concatenates their columns into a combined row.
If a row in either table finds no match in the opposite table, that row is silently omitted from the output.
For example, joining an orders table to a products table on product_id pairs each purchase with its corresponding product details.
If an order references a discontinued product_id that was deleted from the products table, that order disappears from view.
If multiple rows in the right table match a single row in the left table, the left row duplicates for every match.
This row multiplication can distort downstream aggregate calculations if an analyst is unaware of one-to-many relationships.
Joining on non-unique keys is a frequent cause of inflated revenue totals in business intelligence reporting dashboards.
Database engines execute joins using three primary physical algorithms: nested loop joins, hash joins, and merge joins.
Nested loop joins scan the inner table for each outer row, ideal for small datasets or when indexes exist on join keys.
Hash joins build an in-memory hash table of the smaller relation and probe it with the larger relation during a table scan.
Merge joins require both tables to be pre-sorted on join keys, zipping matching rows together in a single sequential pass.
Creating indexes on foreign key columns is essential for ensuring join queries execute rapidly across large production databases.