What is a JOIN in MySQL, and why is it used?

a. INNER JOIN

• Returns only matching rows between tables.

• Most commonly used join.

Example:

SELECT users.id, users.name, orders.amount

FROM users

INNER JOIN orders ON users.id = orders.user_id;

b. LEFT JOIN (LEFT OUTER JOIN)

• Returns all rows from the left table and matching rows from the right table.

• Non-matching rows from the right table appear as NULL.

c. RIGHT JOIN (RIGHT OUTER JOIN)

• Opposite of LEFT JOIN — returns all rows from the right table.

d. FULL OUTER JOIN

• MySQL does not support this directly.

• Can be simulated using UNION between LEFT and RIGHT JOIN.

e. CROSS JOIN

• Produces a Cartesian product (all combinations).

• Usually used for generating data sets.

• To fetch user details along with their orders.

• To connect posts with authors, comments, tags, etc.

• To aggregate data across multiple related tables.

• To enforce normalized relational schema while still retrieving combined data.