What methods can be used to combine data from multiple tables in a database?

Data merging from multiple tables in a database can be implemented using the following main methods:

1. JOIN — the most common method. There are different types of JOIN:

   • INNER JOIN — returns only the records that match the condition in both tables.
   • LEFT JOIN (LEFT OUTER JOIN) — returns all records from the left table and the matching ones from the right, if any.
   • RIGHT JOIN (RIGHT OUTER JOIN) — the opposite, all from the right and matching from the left.
   • FULL JOIN (FULL OUTER JOIN) — combines all records from both tables, filling NULL where there are no matches.

2. UNION / UNION ALL — combines the results of two or more queries with the same column structure. UNION removes duplicates, UNION ALL does not.

3. Subqueries — nested queries can be used to select data from one table based on data from another.

Example of INNER JOIN:

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

This query joins the employees and departments tables on the common field department_id.