Explain the difference between inner join, left join, right join, and full outer join in SQL, and provide an example of when you would use each type of join in a database query.

Explaining Inner Join, Left Join, Right Join, and Full Outer Join in SQL

Inner Join: An Inner Join in SQL returns only the rows that have matching values in both tables being joined. It combines rows from two tables based on a common column.

Left Join: A Left Join in SQL returns all the rows from the left table and matching rows from the right table. If there are no matches, NULL values are returned in the columns from the right table.

Right Join: A Right Join in SQL returns all the rows from the right table and matching rows from the left table. If there are no matches, NULL values are returned in the columns from the left table.

Full Outer Join: A Full Outer Join in SQL returns all rows when there is a match in either the left or right table. It combines the results of both Left and Right joins.

When to use each type of join:

• Inner Join: Use when you only want to retrieve rows that have matching values in both tables.
• Left Join: Use when you want to retrieve all rows from the left table and matching rows from the right table.
• Right Join: Use when you want to retrieve all rows from the right table and matching rows from the left table.
• Full Outer Join: Use when you want to retrieve all rows when there is a match in either table.

Example:

SELECT customers.customer_id, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

In this example, an Inner Join is used to retrieve customer IDs and their corresponding order IDs from the "customers" and "orders" tables.