What type of JOIN returns all rows when there is a match in either left or right table?

The type of JOIN that returns all rows when there is a match in either the left or right table is known as a Full Outer Join.

Explanation:

1. Full Outer Join combines the results of both left and right outer joins. It returns all records from both tables with matching records where available.
2. If there is no match, NULL values are returned for columns from the table that does not have a corresponding match.
3. This type of JOIN ensures that no data from either table is lost in the result set, even if there is no correlation between the rows of the two tables.

In SQL syntax, a Full Outer Join can be written as:

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

This approach is particularly useful when you want to combine datasets and include all the information possible from both tables, regardless of whether they are related.