FULL OUTER JOIN USING Clause Returns Nulls Instead of Merged Values

---

Unexpected NULL Values in FULL OUTER JOIN Queries with the USING Clause

The core issue revolves around the behavior of the USING clause in SQLite’s newly implemented FULL OUTER JOIN functionality. When joining two tables with a shared column using FULL JOIN ... USING (Column), SQLite returns NULL for the joined column in rows where only the right table contains a value, contrary to the expected behavior of coalescing the values from both tables. For example, given two tables t1 and t2 with a column ID, a FULL JOIN should produce a result set that includes all ID values from both tables. However, in SQLite versions with the initial implementation of FULL JOIN, the output incorrectly prioritizes the left table’s ID value, resulting in NULL for unmatched right-table entries. This contrasts with databases like PostgreSQL, which correctly merge the values from both tables.

Technical Breakdown of the Problem

Consider the following example:

CREATE TABLE t1 (ID INTEGER);
CREATE TABLE t2 (ID INTEGER);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
SELECT * FROM t1 FULL JOIN t2 USING (ID);

• Expected Output (as seen in PostgreSQL):

  ID
  ----
   1
   2
  

• Actual Output in SQLite (prior to the fix):

  ID
  ----
   1
  NULL
  

The discrepancy arises from how SQLite handles the USING clause in FULL OUTER JOIN operations. The USING clause is designed to merge the joined columns into a single output column, coalescing values from both tables where possible. However, in the affected SQLite versions, the merged column retained the left table’s value even when it was NULL, effectively discarding valid data from the right table. This behavior was inconsistent with both the SQL standard and other database implementations, indicating an implementation flaw.

Impact on Queries and Applications

This issue has significant implications for applications relying on FULL OUTER JOIN to combine datasets:

1. Data Loss: Valid entries from the right table are replaced with NULL in the merged column.
2. Query Correctness: Results become misleading or incorrect, especially in aggregation or filtering operations.
3. Portability: Queries written for other databases (e.g., PostgreSQL) produce different results in SQLite, complicating cross-platform compatibility.

---

Incomplete Handling of the USING Clause in SQLite’s Initial FULL OUTER JOIN Implementation

The root cause lies in SQLite’s incomplete adaptation of the USING clause logic for FULL OUTER JOIN during its initial development phase. Prior to the introduction of FULL JOIN, SQLite supported only LEFT JOIN, where the USING clause naturally prioritizes the left table’s values. When FULL JOIN was added, the existing logic for LEFT JOIN was reused without fully accounting for the symmetric nature of FULL OUTER JOIN, which requires coalescing values from both tables.

Key Factors Contributing to the Issue

1. Legacy JOIN Logic: The USING clause implementation for LEFT JOIN was designed to handle cases where the left table’s value takes precedence. This logic was erroneously applied to FULL JOIN, which must consider both tables equally.
2. Column Coalescing Oversight: In a FULL OUTER JOIN, the merged column should derive its value from the first non-NULL value between the two tables. SQLite’s initial implementation failed to implement this coalescing step.
3. Early Development Phase: The FULL JOIN feature was still under active development and testing when the issue was reported. Edge cases, such as the USING clause behavior, had not yet been fully addressed.

Underlying Code Behavior

The SQLite engine processes USING clauses by creating an implicit equality condition between the specified columns of the joined tables. For FULL OUTER JOIN, the engine must handle three scenarios:

• Rows matching in both tables.
• Rows present only in the left table.
• Rows present only in the right table.

In the flawed implementation, the code path responsible for constructing the result set for unmatched rows (particularly those from the right table) did not properly populate the merged column with the right table’s value. Instead, it defaulted to the left table’s value, which is NULL for right-unmatched rows.

---

Resolving NULL Values in FULL OUTER JOIN and Ensuring Correct USING Clause Behavior

To address this issue, users must either apply temporary workarounds or update to a fixed version of SQLite. Below is a detailed guide to diagnosing the problem, mitigating its effects, and implementing long-term solutions.

Step 1: Diagnose the Issue

1. Reproduce the Problem:
   Execute the test query provided in the example. If the merged ID column contains NULL for the right-unmatched row, the issue is present.
2. Check SQLite Version:
   Run SELECT sqlite_version(); to determine if the version includes the initial (flawed) FULL JOIN implementation (e.g., versions between check-in fa9d206f904280e3 and f766dff012af0ea3).

Step 2: Immediate Workarounds

1. Avoid the USING Clause:
   Explicitly reference columns from both tables and use COALESCE to merge them:

   SELECT COALESCE(t1.ID, t2.ID) AS ID
   FROM t1 FULL JOIN t2 ON t1.ID = t2.ID;
   

2. Use Legacy UNION Pattern:
   Revert to the pre-FULL JOIN workaround:

   SELECT t1.ID FROM t1 LEFT JOIN t2 USING (ID)
   UNION ALL
   SELECT t2.ID FROM t2 LEFT JOIN t1 USING (ID) WHERE t1.ID IS NULL;
   

Step 3: Update to a Fixed SQLite Version

1. Download the Latest Snapshot:
   Obtain the updated SQLite version from the official download page. Ensure the version includes check-in f766dff012af0ea3 or later.
2. Verify the Fix:
   Re-run the test query. The output should now match the expected behavior:

   ID
   ----
   1
   2
   

Step 4: Adjust Query Patterns for Long-Term Stability

1. Prefer Explicit Column References:
   Even after the fix, explicitly referencing columns with COALESCE ensures clarity and guards against future regressions.
2. Monitor Documentation Updates:
   The SQLite documentation for JOIN clauses and USING will be updated post-release. Refer to the latest docs for syntax and best practices.

Developer Notes

• Feature Status: The FULL OUTER JOIN implementation was temporarily rolled back to address this and other issues. The relanded fix (check-in f766dff012af0ea3) resolves the USING clause behavior by properly coalescing columns.
• Reporting Issues: Users encountering similar problems are encouraged to report them via SQLite’s forum or directly to the maintainers.

By following these steps, users can ensure correct FULL OUTER JOIN behavior and maintain compatibility with SQL standards and other database systems.