What is the JOIN operator in SQL?

The SQL operator JOIN is used to combine rows from two or more tables based on a related column between them.

Main types of JOIN:

1. INNER JOIN: Returns rows that have matching values in both tables.
2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table. If there are no matches, the result from the right table contains NULL.
3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table. If there are no matches, the result from the left table contains NULL.
4. FULL JOIN (or FULL OUTER JOIN): Returns all rows where there is a match in either the left or right table. The result contains all rows from both tables, filling in NULL for missing matches.
5. CROSS JOIN: Returns the Cartesian product of rows from both tables. This is equivalent to a join without an ON condition.

Example of INNER JOIN:

SELECT
    Customers.CustomerName,
    Orders.OrderID
FROM
    Customers
INNER JOIN
    Orders
ON
    Customers.CustomerID = Orders.CustomerID;