Can you explain what a JOIN operation in a relational database is?

The JOIN operation in a relational database is used to combine rows from two or more tables based on a related column (key) between them. This allows you to retrieve combined data from different tables in a single result.

Main types of JOIN:

• INNER JOIN — returns only the rows where there are matches 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 result is NULL on the right side.
• RIGHT JOIN (RIGHT OUTER JOIN) — the opposite of LEFT 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:

SELECT users.name, orders.id
FROM users
JOIN orders ON users.id = orders.user_id;

This query joins the users and orders tables, selecting user names and their orders where users.id matches orders.user_id.