What is the function of the JOIN operator in SQL and what is it used for when working with multiple tables?

The JOIN operator in SQL is used to combine rows from two or more tables based on a related column between them. This allows you to retrieve combined data from multiple tables in a single query.

For example, if there is a Customers table and an Orders table, you can use JOIN to get a list of orders along with information about the customers who made those orders.

A simple INNER JOIN example:

SELECT Customers.Name, Orders.OrderDate
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Here, JOIN links the tables on the CustomerID field, returning only the records where there is a match in both tables.