Full Outer join vs Cross join

A cross join produces a Cartesian product between the two tables, returning all possible combinations of all rows. If table A has 30 rows and table B has 20 rows, a CROSS JOIN will result in 600 rows..

SELECT *
FROM   employee CROSS JOIN department;

A full outer join combines the effect of applying both left and right outer joins.  It returns all rows in both tables that match the query’s where clause, and in cases where the on condition can’t be satisfied for those rows it puts null values in for the unpopulated fields.

SELECT *
FROM   employee
       FULL OUTER JOIN department
          ON employee.DepartmentID = department.DepartmentID;

Leave a Reply