Explain the difference between INNER JOIN and OUTER JOIN in SQL and in which situations each is used.

INNER JOIN returns only those rows that have matching values in both tables based on the join condition.

OUTER JOIN returns all rows from one table and the matching rows from the other. If there is no match, NULLs are used for missing data.

Types of OUTER JOIN:

• LEFT OUTER JOIN — all rows from the left table + matching rows from the right.
• RIGHT OUTER JOIN — all rows from the right table + matching rows from the left.
• FULL OUTER JOIN — all rows from both tables, with NULLs where there are no matches.

Use cases:

• INNER JOIN — when only related data is needed, for example, orders with existing customers.
• LEFT OUTER JOIN — when you want to get all customers, including those without orders.

SQL example:

SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

This query will return all customers and their orders if they exist, otherwise NULL for orders.