A JOIN is a general operation in relational algebra for a combining operation on two relations in a relational database system. JOIN is also a keyword of the SQL language for performing analogous SQL operations.
An SQL JOIN returns rows combined from two tables and possibly satisfying a condition.
ISO/ANSI standard SQL specifies numerous JOINs.
Unconditional/Cross joins:
- CROSS JOIN
- comma (implicit join)
Comma returns a CROSS JOIN but has lower precedence than the keyword joins.
Conditional joins:
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
Each of those has an ON version, a USING version and a NATURAL version. OUTER is an optional keyword with no effect.
CROSS JOIN returns the rows that can be made by combining a row from the left table with a row from the right table. INNER JOIN ON/USING does a CROSS JOIN then keeps only rows satisfying a condition. LEFT/RIGHT/FULL OUTER JOIN ON/USING does an INNER JOIN then via UNION ALL adds the rows got by NULL-extending the rows from the LEFT/RIGHT/both input tables that did not form an INNER JOIN row.
Specific join tags:
You can specify your question by adding extra tags:
cross-join inner-join left-join right-join full-outer-join outer-join natural-join self-join
