Relational Algebra & Set Mechanics:
1. INNER JOIN:
- Returns only records that have matching keys in both tables. If a row in Table A has no match in Table B, it is excluded from the result.
2. LEFT OUTER JOIN (LEFT JOIN):
- Returns all records from the left table (Table A), along with matched records from the right table (Table B). If no match exists, NULL values are returned for Table B's columns.
3. RIGHT OUTER JOIN (RIGHT JOIN):
- The inverse of LEFT JOIN: returns all records from the right table (Table B) and matched records from the left table, with NULLs for missing Table A data.
4. FULL OUTER JOIN:
- Combines the results of both LEFT and RIGHT joins. Returns all records from both tables; rows without matches in either table are populated with NULLs.
5. CROSS JOIN (Cartesian Product):
- Matches every row in Table A with every row in Table B without a join condition, yielding N * M total rows. Useful for matrix generation or combinatorics.