How is table joining performed using the JOIN operator in SQL?

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

• INNER JOIN — returns only those records that have matches in both tables.
• LEFT JOIN (LEFT OUTER JOIN) — returns all records from the left table and matching records from the right; if there is no match, the right fields will be NULL.
• RIGHT JOIN (RIGHT OUTER JOIN) — the opposite, returns all from the right and matching from the left.
• FULL JOIN (FULL OUTER JOIN) — returns all records from both tables; where there is no match, the fields from the other table will be NULL.

Example of INNER JOIN:

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

Here, we join the orders and customers tables on the customer_id field, retrieving orders with customer names.