FULL JOIN

Overview

The FULL JOIN (or FULL OUTER JOIN) returns all rows from both tables. When there's a match, it returns the combined row. When there's no match, it returns NULL for columns from the table without a match.

FULL JOIN combines the results of both LEFT JOIN and RIGHT JOIN, giving you all records from both tables.

Basic Syntax

SELECT column1, column2, ...
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Note: FULL JOIN is not supported in MySQL. Use LEFT JOIN and RIGHT JOIN combined with UNION instead.

Visual Representation

Table A          Table B
┌─────┐         ┌─────┐
│  1  │         │  3  │
│  2  │─────────│  2  │  Returns: 1, 2, 3, 4, 5 (all from both)
│  3  │         │  4  │  (1 from A, NULL from B)
│  4  │         │  5  │  (2 and 3 match)
└─────┘         └─────┘  (4 and 5 from B, NULL from A)

Result: All rows from both tables
        Matching rows combined
        Non-matching rows have NULLs

Examples

Example 1: Basic FULL JOIN

SELECT 
    c.name AS customer_name,
    o.order_date,
    o.total_amount
FROM Customers c
FULL JOIN Orders o 
ON c.customer_id = o.customer_id;

Returns: All customers and all orders, with NULLs where there's no match.

FULL JOIN Components

FULL JOIN includes three types of rows:

1. Matching rows: Both tables have matching values
2. Left-only rows: Values in left table, NULL in right
3. Right-only rows: Values in right table, NULL in left

MySQL Alternative

Since MySQL doesn't support FULL JOIN, use LEFT JOIN and RIGHT JOIN with UNION:

-- FULL JOIN equivalent in MySQL
SELECT 
    c.name AS customer_name,
    o.order_date,
    o.total_amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id

UNION

SELECT 
    c.name AS customer_name,
    o.order_date,
    o.total_amount
FROM Customers c
RIGHT JOIN Orders o ON c.customer_id = o.customer_id;

When to Use FULL JOIN

• When you need all records from both tables
• For comprehensive reports showing all data
• Finding mismatches in both directions
• Data reconciliation between tables
• When you need to see orphaned records in both tables

Performance Considerations

• Slower than other JOINs: Processes all rows from both tables
• Index both join columns: Important for performance
• Consider alternatives: May be able to use UNION instead
• Filter first: Use WHERE to reduce rows before joining

Best Practices

• Handle NULLs: Be prepared for NULL values in result set
• Use COALESCE: Replace NULLs when appropriate
• Consider performance: FULL JOIN can be slow on large tables
• Verify database support: Check if your database supports FULL JOIN
• Use UNION alternative: In MySQL, use LEFT/RIGHT JOIN with UNION

Next Steps

Learn about combining result sets with UNION, or explore grouping data with GROUP BY.