FULL OUTER JOIN and CROSS JOIN

We have seen INNER JOIN (only matches), LEFT JOIN (all left rows), and RIGHT JOIN (all right rows). Now let's cover the two remaining join types: FULL OUTER JOIN and CROSS JOIN.

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables. Where there is a match, the columns are combined. Where there is no match on either side, the missing columns are filled with NULL.

  Left Table        Right Table
 +-----------+     +-----------+
 |           |     |           |
 | ALL rows  |match| ALL rows  |
 | included  |<--->| included  |
 |           |     |           |
 | unmatched |     | unmatched |
 | rows get  |     | rows get  |
 | NULLs --> |     | <-- NULLs |
 +-----------+     +-----------+

 Every row from BOTH tables appears in the result.

Syntax

SELECT columns
FROM table_a
FULL OUTER JOIN table_b
  ON table_a.column = table_b.column;

You can also write FULL JOIN -- the OUTER keyword is optional.

Example: Complete Picture of Employees and Departments

SELECT
  e.name AS employee_name,
  e.department AS emp_department,
  d.name AS dept_name,
  d.budget,
  d.location
FROM employees e
FULL OUTER JOIN departments d
  ON e.department = d.name;

This query shows:

• Employees who belong to a known department (matched rows)
• Employees whose department is not in the departments table (right columns are NULL)
• Departments that have no employees (left columns are NULL)

Finding All Unmatched Rows

You can use FULL OUTER JOIN to find mismatches on both sides:

SELECT
  e.name AS employee_name,
  e.department AS emp_department,
  d.name AS dept_name
FROM employees e
FULL OUTER JOIN departments d
  ON e.department = d.name
WHERE e.name IS NULL OR d.name IS NULL;

This returns:

• Employees with no matching department (d.name IS NULL)
• Departments with no employees (e.name IS NULL)

This is useful for data integrity checks -- finding orphaned records on either side of a relationship.

Comparing the Join Types

Here is a summary of what each join type returns:

Join Type          Left Unmatched    Matched    Right Unmatched
-----------------------------------------------------------------
INNER JOIN         excluded          included   excluded
LEFT JOIN          included (NULLs)  included   excluded
RIGHT JOIN         excluded          included   included (NULLs)
FULL OUTER JOIN    included (NULLs)  included   included (NULLs)

CROSS JOIN

A CROSS JOIN produces the Cartesian product of two tables -- every row from the first table is paired with every row from the second table. There is no ON clause because no condition is being checked.

Table A (3 rows)    Table B (2 rows)    Result (3 x 2 = 6 rows)
+----+              +----+              +------+------+
| a1 |              | b1 |              | a1   | b1   |
| a2 |      x       | b2 |       =      | a1   | b2   |
| a3 |              +----+              | a2   | b1   |
+----+                                  | a2   | b2   |
                                        | a3   | b1   |
                                        | a3   | b2   |
                                        +------+------+

Syntax

SELECT columns
FROM table_a
CROSS JOIN table_b;

Warning About Row Counts

A CROSS JOIN multiplies the row counts. If Table A has 1,000 rows and Table B has 1,000 rows, the result has 1,000,000 rows. Use CROSS JOIN with caution on large tables.

Example: Every Employee-Department Combination

SELECT
  e.name AS employee_name,
  d.name AS department_name,
  d.location
FROM employees e
CROSS JOIN departments d
ORDER BY e.name, d.name;

With 8 employees and 3 departments, this produces 24 rows -- every possible pairing.

Practical Use Case: Generating a Report Grid

CROSS JOIN is useful when you need to generate all combinations, such as a report that should have a row for every employee in every department (even if that combination does not exist):

SELECT
  d.name AS department_name,
  e.name AS employee_name,
  CASE
    WHEN e.department = d.name THEN 'Current'
    ELSE 'Not assigned'
  END AS status
FROM departments d
CROSS JOIN employees e
ORDER BY d.name, e.name;

Implicit CROSS JOIN

You can also write a CROSS JOIN by simply listing tables separated by commas in the FROM clause:

-- These two are equivalent:
SELECT * FROM employees CROSS JOIN departments;
SELECT * FROM employees, departments;

However, the explicit CROSS JOIN syntax is preferred because it makes your intention clear. The comma syntax can be confused with a forgotten join condition (which is a bug).

Try It Yourself

1. Write a FULL OUTER JOIN between employees and departments. How many rows do you get? Are there any departments with no employees, or employees with no matching department?

2. Use a CROSS JOIN to generate every possible combination of employee and department. Count the total rows.

3. Write a query using FULL OUTER JOIN to find all "orphaned" records -- employees whose department does not exist in the departments table, and departments with no employees.