Explain the operation of FULL OUTER JOIN in SQL and in what cases it is used?

FULL OUTER JOIN in SQL combines rows from two tables, returning all records from both tables. If there is no match for a row from one table in the other, the result set will contain NULL for the missing values.

It is used when you need to get a complete set of data from both tables, including both matching and non-matching records.

Example:

SELECT A.id, A.name, B.order_id
FROM Customers A
FULL OUTER JOIN Orders B ON A.id = B.customer_id;

In this example, all customers and all orders will be shown, even if a customer has no orders or an order has no customer.