Difference Between INNER JOIN and OUTER JOIN in MySQL
INNER JOIN and OUTER JOIN are both used to combine rows from multiple tables, but they differ in how they handle unmatched rows.
• Returns ONLY the rows that have matching values in both tables.
• Rows without a match in either table are excluded.
Example:
SELECT u.id, u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
• Output: Only users who have at least one order.
OUTER JOIN returns matching rows plus non-matching rows from one or both tables.
Types of OUTER JOIN:
a. LEFT OUTER JOIN (LEFT JOIN)
• Returns all rows from the left table.
• Non-matching rows from the right table are filled with NULL.
b. RIGHT OUTER JOIN (RIGHT JOIN)
• Returns all rows from the right table.
• Non-matching rows from the left table are filled with NULL.
c. FULL OUTER JOIN
• Returns all rows from both tables (matching + non-matching).
• MySQL does NOT support this directly but can be simulated using UNION.
• INNER JOIN → Keeps ONLY matching rows.
• OUTER JOIN → Includes matching rows + non-matching rows.
• LEFT JOIN → Preserves all rows from the left table.
• RIGHT JOIN → Preserves all rows from the right table.
• FULL OUTER JOIN → Preserves all rows from both tables (not directly supported in MySQL).