Join & Its Types

JOIN in RDBMS:

• INNER JOIN: Returns records that have matching values in both tables. This is the most common type of join.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. The result is NULL from the right side if there is no match.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table. The result is NULL from the left side when there is no match.
• FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either the left or right table. It combines the results of both LEFT and RIGHT joins.
• CROSS JOIN: Creates the Cartesian product of two tables, meaning it combines every row from the first table with every row from the second table.
• SELF JOIN: A regular join, but the table is joined with itself. This is useful for querying hierarchical data or comparing rows within the same table.