Q6: Explain INNER, LEFT, RIGHT, FULL, and CROSS JOINs with examples.
JOIN clauses combine columns from one or more tables using related attributes:
- INNER JOIN: Returns only matching records existing in both tables.
- LEFT (OUTER) JOIN: Returns all records from the left table and matching records from the right table. Unmatched right values return
NULL. - RIGHT (OUTER) JOIN: Returns all records from the right table and matching left records.
- FULL (OUTER) JOIN: Returns all records when a match exists in either left or right table.
- CROSS JOIN: Produces the Cartesian product of both tables (N × M rows).
Key Takeaways
- LEFT JOIN guarantees all rows from the primary left table are retained.
- CROSS JOIN produces N * M rows and should be used with caution on large dataset sizes.