Full Outer Joins in LINQ: Performance and SQL Translation

Joining data from two sources is one of the most common operations in application development.

In a typical .NET application, you may need to combine:

Customers and orders
Products and inventory
Employees and attendance
Users and subscriptions
Accounts and transactions

LINQ has traditionally provided Join, GroupJoin, and patterns that developers could use to construct left or right outer joins.

.NET 11 expands the LINQ join capabilities with first-class LeftJoin, RightJoin, and FullJoin operations. These APIs are available for Enumerable, Queryable, and AsyncEnumerable.

For database-backed applications, the change is particularly interesting because EF Core 11 can translate FullJoin into a database-level FULL JOIN.

That raises an important practical question:

Does the new LINQ syntax merely make code easier to read, or can it also produce a better database query?

The answer depends on the data source, query shape, database provider, indexes, and execution plan.

What Is a Full Outer Join?

A full outer join returns:

Matching rows from both sources.
Rows that exist only in the left source.
Rows that exist only in the right source.

Consider two datasets.

Customers:

Id	Name
1	Alice
2	Bob
3	Charlie

Orders:

Id	CustomerId	Amount
101	1	100
102	2	250
103	5	500

A full outer join produces:

Customer	Order	Result
Alice	101	Match
Bob	102	Match
Charlie	null	Left only
null	103	Right only

This is different from an inner join, which would return only Alice and Bob.

Why Full Outer Joins Are Useful

A full outer join is particularly useful when the application needs to identify differences between two datasets.

Examples include:

Expected Products
       |
       v
Actual Inventory
       |
       v
Full Join
       |
       +--> Matching
       +--> Missing Inventory
       +--> Unexpected Inventory

This makes full joins useful for:

Reconciliation
Data migration validation
Reporting
Audit processes
Synchronization
Data quality checks
Comparing source systems

FullJoin in .NET 11

.NET 11 adds a first-class FullJoin LINQ operator.

A simplified in-memory example is:

var results = customers.FullJoin(
    orders,
    customer => customer.Id,
    order => order.CustomerId,
    (customer, order) => new
    {
        Customer = customer,
        Order = order
    });

The result preserves elements from both sequences.

If there is no corresponding element on one side, the corresponding value is default.

Microsoft documents the new join operations as part of the .NET 11 LINQ improvements.

FullJoin vs Join

The difference is important.

A normal Join returns only matching elements.

var results = customers.Join(
    orders,
    customer => customer.Id,
    order => order.CustomerId,
    (customer, order) => new
    {
        Customer = customer,
        Order = order
    });

Conceptually:

Customers       Orders
    |              |
    +---- Match ---+
           |
           v
       Results

A full join instead preserves unmatched records:

Customers       Orders
    |              |
    +---- Match ---+
    |              |
    v              v
Left only      Right only
    \              /
     \            /
       FullJoin
          |
          v
       Results

FullJoin vs LeftJoin

A left join preserves every record from the left sequence.

var results = customers.LeftJoin(
    orders,
    customer => customer.Id,
    order => order.CustomerId,
    (customer, order) => new
    {
        Customer = customer,
        Order = order
    });

If an order has no matching customer, it is not included as a left-join result.

With FullJoin, it is.

Join Type	Left-only	Matching	Right-only
Inner Join	No	Yes	No
Left Join	Yes	Yes	No
Right Join	No	Yes	Yes
Full Join	Yes	Yes	Yes

This makes the intent much clearer when the business requirement is genuinely a full outer join.

FullJoin With Entity Framework Core

The feature becomes particularly interesting when working with EF Core.

For example:

var results = await context.Customers
    .FullJoin(
        context.Orders,
        customer => customer.Id,
        order => order.CustomerId,
        (customer, order) => new
        {
            Customer = customer,
            Order = order
        })
    .ToListAsync();

EF Core 11 translates this query to a database FULL JOIN for relational providers that support the operation. Microsoft documents the generated SQL as being equivalent to:

SELECT
    [c].[Id],
    [c].[Name],
    [o].[Id],
    [o].[CustomerId],
    [o].[OrderDate]
FROM [Customers] AS [c]
FULL JOIN [Orders] AS [o]
    ON [c].[Id] = [o].[CustomerId]

This is significant because the join can be executed by the database rather than reconstructing the operation in application memory.

Why SQL Translation Matters

Consider two approaches.

Database-Side Join

Database
   |
   +-- Customers
   |
   +-- Orders
   |
   v
FULL JOIN
   |
   v
Filtered Results
   |
   v
Application

Application-Side Join

Database
   |
   +-- Customers ---> Application
   |
   +-- Orders ------> Application
                         |
                         v
                       Join

The second approach can require transferring significantly more data.

For large tables, that can increase:

Network traffic
Application memory
Serialization cost
CPU consumption
Query latency

A database-side join allows the database engine to use its optimizer and available indexes.

Queryable vs Enumerable

One important LINQ concept is the difference between Enumerable and Queryable.

Enumerable generally operates on objects already in memory:

IEnumerable<Customer> customers;

Queryable builds an expression tree that a provider can translate:

IQueryable<Customer> customers;

For EF Core:

var query = context.Customers
    .FullJoin(
        context.Orders,
        c => c.Id,
        o => o.CustomerId,
        (c, o) => new
        {
            Customer = c,
            Order = o
        });

The query remains database-oriented until materialization:

var results = await query.ToListAsync();

This distinction is critical when evaluating performance.

Do Not Call ToList Too Early

Avoid:

var customers =
    await context.Customers.ToListAsync();

var orders =
    await context.Orders.ToListAsync();

var results = customers.FullJoin(
    orders,
    c => c.Id,
    o => o.CustomerId,
    (c, o) => new
    {
        Customer = c,
        Order = o
    });

This moves both datasets into application memory.

The database can no longer perform the join.

Prefer keeping the operation queryable:

var results = await context.Customers
    .FullJoin(
        context.Orders,
        c => c.Id,
        o => o.CustomerId,
        (c, o) => new
        {
            Customer = c,
            Order = o
        })
    .ToListAsync();

This allows EF Core to translate the operation.

Filtering Before the Join

Filtering can reduce the amount of data involved in the operation.

For example:

var results = await context.Customers
    .Where(c => c.IsActive)
    .FullJoin(
        context.Orders,
        c => c.Id,
        o => o.CustomerId,
        (c, o) => new
        {
            Customer = c,
            Order = o
        })
    .ToListAsync();

However, do not assume that moving every filter before the join automatically improves the query.

The resulting SQL and execution plan should be inspected.

Query optimization is provider- and workload-dependent.

Projection Matters

Avoid retrieving entire entities when the report requires only a few columns.

For example:

var results = await context.Customers
    .FullJoin(
        context.Orders,
        c => c.Id,
        o => o.CustomerId,
        (c, o) => new
        {
            CustomerId = c == null
                ? null
                : c.Id,

            CustomerName = c == null
                ? null
                : c.Name,

            OrderId = o == null
                ? null
                : o.Id,

            Amount = o == null
                ? null
                : o.Amount
        })
    .ToListAsync();

A focused projection can reduce the amount of data returned from the database.

The exact SQL should still be inspected.

Handling Null Values

Full joins naturally produce missing values.

For example:

foreach (var result in results)
{
    if (result.Customer is null)
    {
        Console.WriteLine(
            $"Order {result.Order?.Id} has no customer.");
    }

    if (result.Order is null)
    {
        Console.WriteLine(
            $"Customer {result.Customer?.Id} has no order.");
    }
}

Do not treat missing values as exceptional unless the business rule requires it.

An unmatched row is an expected outcome of a full outer join.

A Reconciliation Example

Suppose an organization needs to compare customer records with an external billing system.

Internal customers:

C001
C002
C003

Billing customers:

C001
C002
C004

A full join makes the differences visible:

C001 -> Match
C002 -> Match
C003 -> Internal only
C004 -> Billing only

The application can then classify the result:

var reconciliation =
    results.Select(x => new
    {
        CustomerId =
            x.Customer?.ExternalId ??
            x.Order?.CustomerId,

        Status =
            x.Customer is not null &&
            x.Order is not null
                ? "Match"
                : x.Customer is not null
                    ? "InternalOnly"
                    : "ExternalOnly"
    });

The exact domain model will vary, but the pattern is useful for reconciliation workflows.

Benchmarking FullJoin

A useful benchmark should compare multiple approaches.

For example:

Approach	Execution Location	Purpose
FullJoin on Enumerable	Application	In-memory baseline
FullJoin on Queryable	Database	Provider translation
Manual left/right join	Database	Compatibility comparison
Materialized collections	Application	Identify data-transfer cost

The goal is not to prove that one approach is universally faster.

The goal is to determine how each approach behaves for the application's data volume.

Benchmark In-Memory FullJoin

For an in-memory benchmark:

[Benchmark]
public void FullJoinInMemory()
{
    var results = customers.FullJoin(
        orders,
        c => c.Id,
        o => o.CustomerId,
        (c, o) => new
        {
            Customer = c,
            Order = o
        });

    _ = results.Count();
}

The benchmark should control:

Number of customers
Number of orders
Match percentage
Duplicate keys
Payload size

For example:

10,000 customers
50,000 orders
70% matching keys

The actual benchmark values should be selected based on the workload being investigated.

Benchmark Database Queries

For EF Core, measure the database query separately.

Important metrics include:

Execution time
Rows returned
Logical reads
CPU
Network transfer
Application allocations

The database execution plan is often more informative than application-level timing alone.

For SQL Server, inspect the actual execution plan and relevant query statistics.

Do not conclude that a LINQ query is inefficient merely because the C# expression looks complex.

The database sees the translated SQL.

Inspect Generated SQL

EF Core allows developers to inspect generated SQL.

For example:

var query = context.Customers
    .FullJoin(
        context.Orders,
        c => c.Id,
        o => o.CustomerId,
        (c, o) => new
        {
            Customer = c,
            Order = o
        });

Console.WriteLine(
    query.ToQueryString());

This is extremely useful during development.

You can verify whether the query actually contains the expected join.

For EF Core 11, Microsoft documents FullJoin translation to relational FULL JOIN.

Database Indexes Still Matter

A convenient LINQ API does not eliminate database optimization requirements.

If the join uses:

Customers.Id
Orders.CustomerId

the database should have appropriate keys or indexes for the workload.

For example:

CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId);

Whether an additional index is beneficial depends on the existing schema, query workload, database engine, and execution plan.

Do not add indexes blindly.

Use query plans and workload measurements to justify them.

FullJoin With Duplicate Keys

Consider:

Customers
C001

Orders
C001 - Order A
C001 - Order B

A join can produce multiple result rows.

Conceptually:

C001 + Order A
C001 + Order B

This is normal relational behavior.

Therefore, benchmark datasets should include realistic duplicate-key distributions.

Otherwise, a benchmark can underestimate the number of rows produced by the query.

FullJoin and Large Tables

For large tables, consider:

Customer rows
      +
Order rows
      |
      v
FULL JOIN
      |
      v
Potentially large result

The result itself can be much larger than expected.

Before executing a full join against large production tables, estimate:

Number of input rows
Match rate
Duplicate keys
Expected result size
Required columns
Filtering conditions

A full join is powerful, but it can intentionally return unmatched data from both sides.

When FullJoin Is Not the Right Choice

A full join should not automatically replace other join types.

Use an inner join when only matches matter.

Use a left join when the left side is authoritative and unmatched right-side data is irrelevant.

Use a right join when the reverse is true.

Use a full join when both unmatched sides are meaningful.

Choosing the correct semantic operation is more important than minimizing the number of LINQ operators.

Common Mistakes

Materializing Data Before Joining

Calling:

ToListAsync()

before the join can move the operation into application memory.

Ignoring Generated SQL

A LINQ expression is not the final database query.

Inspect the SQL.

Assuming FullJoin Is Always Faster

The new operator improves expressiveness and can translate directly to FULL JOIN, but performance still depends on schema, indexes, data distribution, provider, and execution plan. Microsoft explicitly notes that actual performance varies with the application and data.

Returning Entire Entities

Large projections can increase network and materialization costs.

Ignoring Duplicate Keys

Duplicate join keys can multiply result rows.

Benchmarking Only Tiny Datasets

Small datasets may hide query-plan and I/O behavior that appears at larger scale.

Troubleshooting

FullJoin Does Not Translate

Check that the query remains an IQueryable and that the database provider supports the required translation.

Also verify the EF Core version.

The Query Is Slow

Inspect:

Generated SQL
Execution plan
Indexes
Rows scanned
Rows returned
Database CPU
Logical reads

Do not optimize the LINQ expression before understanding the database query.

Too Many Rows Are Returned

Check duplicate join keys.

A full join preserves all matching combinations, not simply one result per key.

Memory Usage Is High

Check whether the query was accidentally materialized before joining.

Also review the projection and result size.

Results Contain Null Values

This is expected for unmatched records.

Handle the missing side explicitly.

Best Practices

Use FullJoin when the business requirement is genuinely a full outer join.
Keep EF Core queries as IQueryable until the database operation is complete.
Inspect generated SQL.
Inspect the database execution plan for important queries.
Project only the columns required by the application.
Validate indexes using actual workload evidence.
Test realistic match rates.
Test duplicate-key scenarios.
Measure database and application performance separately.
Do not assume the new LINQ operator is automatically faster.
Handle unmatched records explicitly.
Use integration tests against the actual database provider.

Frequently Asked Questions

What does FullJoin do in LINQ?

FullJoin returns matching elements plus elements that exist only in the left or right sequence. .NET 11 adds first-class FullJoin support to LINQ.

Is FullJoin available for IQueryable?

Yes. .NET 11 provides the join improvements across Enumerable, Queryable, and AsyncEnumerable.

Does EF Core 11 translate FullJoin to SQL?

Yes. EF Core 11 translates FullJoin to a relational FULL JOIN where supported.

Is FullJoin faster than a manually constructed join?

Not universally.

The advantage is that the operation expresses the intended semantics directly and can be translated to a native database full join. Actual performance depends on the provider, schema, indexes, data distribution, and execution plan.

Should I use FullJoin for every comparison?

No.

Use the join type that matches the business requirement. A full join is appropriate when unmatched rows from both sides are significant.

Can FullJoin work with in-memory collections?

Yes. The new LINQ join APIs include Enumerable implementations, so full joins can also be performed over in-memory sequences.

Conclusion

.NET 11's new LINQ join capabilities address a long-standing gap in expressive data querying.

The new FullJoin operation makes the intent of a full outer join explicit:

Left Only
    +
Matching
    +
Right Only
    =
FullJoin

For in-memory collections, it provides a direct LINQ representation of the operation.

For EF Core applications, the feature is even more significant because EF Core 11 can translate the query to a database-level FULL JOIN.

The performance opportunity, however, should be evaluated scientifically.

A useful investigation should follow:

LINQ Query
    |
    v
Generated SQL
    |
    v
Execution Plan
    |
    v
Database Metrics
    |
    v
Application Metrics

The key principle is:

A better LINQ abstraction does not automatically mean a faster query.

The real benefit comes when the new abstraction expresses the correct relational operation and the database can execute that operation efficiently.

For .NET 11 applications, FullJoin therefore deserves attention not just as a convenient API, but as a new boundary between application-level LINQ and database-level query execution.