JOINs and Their Types

JOINs & Their Types in DBMS:

• INNER JOIN: Returns only the records that have matching values in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL from the right side.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table. If there is no match, the result is NULL from the left side.
• FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either the left or right table. It effectively combines LEFT and RIGHT joins.
• CROSS JOIN: Returns the Cartesian product of the two tables, meaning every row from the first table is combined with every row from the second table.
• SELF JOIN: Joins a table to itself. This is useful for comparing rows within the same table or for querying hierarchical data stored in a flat table.