FULL OUTER JOIN and CROSS JOIN in SQL — Concepts and Use Cases

Table of Contents

1. Introduction
2. What is a FULL OUTER JOIN?
3. Syntax of FULL OUTER JOIN
4. When to Use FULL OUTER JOIN
5. FULL OUTER JOIN Example
6. Filtering with FULL OUTER JOIN
7. Handling NULLs in FULL OUTER JOIN
8. Emulating FULL OUTER JOIN in MySQL/SQLite
9. What is a CROSS JOIN?
10. Syntax of CROSS JOIN
11. When to Use CROSS JOIN
12. CROSS JOIN Example
13. CROSS JOIN vs INNER JOIN
14. Cartesian Product Explained
15. Combining CROSS JOIN with Filters
16. Real-World Example: FULL OUTER JOIN
17. Real-World Example: CROSS JOIN
18. Performance Considerations
19. Common Mistakes with OUTER and CROSS JOIN
20. Summary and What’s Next

1. Introduction

Two advanced SQL join types — FULL OUTER JOIN and CROSS JOIN — help retrieve either complete or combinatorially exhaustive datasets. In this module, you’ll learn when and how to use them, their performance implications, and real-world relevance.

2. What is a FULL OUTER JOIN?

A FULL OUTER JOIN returns all rows from both tables. If there’s no match, the missing side is filled with NULL.

3. Syntax of FULL OUTER JOIN

sqlCopyEditSELECT a.column, b.column
FROM tableA a
FULL OUTER JOIN tableB b
ON a.id = b.a_id;

This includes:

• Rows with matching values
• Rows from tableA without match in tableB
• Rows from tableB without match in tableA

4. When to Use FULL OUTER JOIN

Use it when:

• You want to retain all data from both tables
• You need to analyze unmatched records on both sides
• You’re building data comparison or auditing reports

5. FULL OUTER JOIN Example

students

library_users

sqlCopyEditSELECT s.name, l.student_id
FROM students s
FULL OUTER JOIN library_users l
ON s.id = l.student_id;

Result:

6. Filtering with FULL OUTER JOIN

To find unmatched rows only:

sqlCopyEditSELECT *
FROM tableA a
FULL OUTER JOIN tableB b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;

Useful for auditing mismatches between datasets.

7. Handling NULLs in FULL OUTER JOIN

Expect NULL in columns where no match occurs.

Use COALESCE() to default values:

sqlCopyEditSELECT COALESCE(a.name, b.name) AS entity_name
FROM a
FULL OUTER JOIN b ON a.id = b.id;

8. Emulating FULL OUTER JOIN in MySQL/SQLite

Since MySQL and SQLite don’t support FULL OUTER JOIN natively, emulate it using UNION:

sqlCopyEditSELECT * FROM A
LEFT JOIN B ON A.id = B.id

UNION

SELECT * FROM A
RIGHT JOIN B ON A.id = B.id;

9. What is a CROSS JOIN?

A CROSS JOIN returns the Cartesian product of two tables — every row from table A is paired with every row from table B.

10. Syntax of CROSS JOIN

sqlCopyEditSELECT a.column, b.column
FROM tableA a
CROSS JOIN tableB b;

OR:

sqlCopyEditSELECT a.column, b.column
FROM tableA a, tableB b;

11. When to Use CROSS JOIN

• To generate all possible combinations
• For matrix-style reports (e.g., regions × quarters)
• For calendar creation, shift schedules, or test datasets

12. CROSS JOIN Example

colors

sizes

sqlCopyEditSELECT color, size
FROM colors
CROSS JOIN sizes;

Result:

13. CROSS JOIN vs INNER JOIN

14. Cartesian Product Explained

If table A has 3 rows and table B has 4 rows:

• CROSS JOIN will return 3 × 4 = 12 rows

15. Combining CROSS JOIN with Filters

sqlCopyEditSELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s
WHERE c.color != 'Red';

Generates combinations, but filters out unnecessary pairs.

16. Real-World Example: FULL OUTER JOIN

Find active and inactive product SKUs between two systems:

sqlCopyEditSELECT *
FROM systemA_products a
FULL OUTER JOIN systemB_products b
ON a.sku = b.sku
WHERE a.sku IS NULL OR b.sku IS NULL;

Helps with reconciliation reports.

17. Real-World Example: CROSS JOIN

Generate price list for all sizes of all products:

sqlCopyEditSELECT p.name, s.size
FROM products p
CROSS JOIN sizes s;

Use this for e-commerce variants, test data generation, etc.

18. Performance Considerations

Indexing improves FULL OUTER JOIN, but doesn’t apply to CROSS JOIN (no match condition).

19. Common Mistakes with OUTER and CROSS JOIN

• Misusing CROSS JOIN instead of INNER JOIN
• Forgetting to handle NULL in FULL OUTER JOIN
• Overloading CROSS JOIN on large tables → memory spikes
• Not supporting FULL JOIN in DBMS (like MySQL, SQLite)

20. Summary and What’s Next

• FULL OUTER JOIN combines unmatched + matched data from both tables
• CROSS JOIN generates every possible row combination
• Use each judiciously for comparisons, reconciliations, and generating combinations