What is the JOIN operator in SQL?

The JOIN operator in SQL is used to combine rows from two or more tables based on a related condition between them. This allows you to retrieve data that is related across tables, for example, combining order information with customer details.

There are several types of JOIN:

• INNER JOIN — returns only the rows that match the condition in both tables.
• LEFT JOIN (LEFT OUTER JOIN) — returns all rows from the left table and the matching rows from the right table; if there is no match, the right side will contain NULL.
• RIGHT JOIN (RIGHT OUTER JOIN) — returns all rows from the right table and the matching rows from the left table.
• FULL JOIN (FULL OUTER JOIN) — returns all rows from both tables, filling in NULLs where there are no matches.

Example of INNER JOIN:

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