A FULL JOIN, also known as a FULL OUTER JOIN, is used to retrieve all records from both the left (table1) and the right (table2) tables. It combines the functionality of both LEFT JOIN and RIGHT JOIN. If there are rows in one table that do not have a match in the other, the result set will include those rows and fill the columns from the non-matching table with NULL values. This ensures that every row from both participating tables is represented in the final output, regardless of whether a match is found based on the join predicate.

B. Outer Join: This is a general category that includes LEFT, RIGHT, and FULL joins. It is not the specific type that guarantees all rows from both tables are returned.

C. Inner Join: This join returns only the rows where the join condition is met in both tables, explicitly excluding rows that do not have a match.

D. Cross Join: This join produces a Cartesian product, which is a result set where each row from the first table is combined with every row from the second table, without a join condition.

---

1. PostgreSQL 16 Documentation. (2023). 7.2.2. Joins Between Tables. "Finally, a FULL OUTER JOIN... returns all the joined rows, plus one row for each unjoined row from the left table (extended with nulls on the right), plus one row for each unjoined row from the right table (extended with nulls on the left)."

Source: PostgreSQL Documentation

2. Microsoft SQL Server Documentation. (2023). FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL). In the section for FULL [ OUTER ] JOIN, it states: "Specifies that all rows from both the left and right outer input sources are returned... If a row from the left outer input source does not match any row in the right outer input source, the row from the left outer input source is returned in the result set, and output columns from the other table contain null values."

Source: Microsoft Learn

3. Stanford University. (n.d.). CS145: Introduction to Databases, Lecture Notes on Relational Algebra. The full outer join operation (⟗) is defined as the union of the left outer join and the right outer join (R ⟗ S = (R ⟕ S) ∪ (R ⟖ S)), which mathematically ensures all tuples from both relations R and S are included in the result.

Source: Stanford CS145 Course Material