Resolving NULL Handling in Multi-Table Joins with SQLite

Issue Overview: Complex Joins with Partial NULL Values

The problem revolves around joining three tables (table1, table2, and table3) under conditions where partial NULL values exist in join columns (id1 and id2 of table1). The goal is to produce a result set that includes all rows from table1, matching field1 from table2, and field2 values from table3 mapped through two different join paths. The challenge lies in ensuring that missing values (NULL in id1 or id2) do not eliminate rows from the result while correctly mapping field2 values from table3 where possible.

Key Observations from Desired Result:

1. Row Retention: All three rows from table1 (for field0 values A, B, C) must appear in the output, even when id1 or id2 is NULL.
2. Dual Mappings: For each row in table1, two separate mappings to table3 are required:
   • id for 1: Derived from table3.field2 where table3.id matches table1.id1.
   • id for 2: Derived from table3.field2 where table3.id matches table1.id2.
3. NULL Handling: When id1 or id2 is NULL, the corresponding field2 value from table3 should be omitted (not replaced with a placeholder).

Schema Constraints:

• table1 contains optional id1 and id2 columns (some rows have NULL values).
• table2 is linked via field0, which is always populated.
• table3 holds field2 values mapped through id values referenced by table1.id1 and table1.id2.

Possible Causes: Misaligned Joins and Filtering Logic

The following factors contribute to the difficulty in achieving the desired result:

1. Implicit Inner Join Behavior with NULLs

• Problem: Using INNER JOIN (explicitly or implicitly via comma-separated tables) between table1 and table3 on id1 or id2 will exclude rows where these columns are NULL, as NULL cannot satisfy equality conditions.
• Example: The initial query SELECT ... WHERE t1.id1 = t3_1.id eliminates rows where t1.id1 is NULL.

2. Cartesian Products from Unconstrained OR Conditions

• Problem: Introducing OR conditions (e.g., (t1.id1 = t3_1.id OR t1.id1 IS NULL)) without proper constraints generates unintended row combinations. This occurs because OR allows matches to multiple table3 rows when id1 or id2 is NULL.
• Example: For field0 = B (where id2 is NULL), the OR t1.id2 IS NULL condition causes all rows from table3 to match, producing redundant entries.

3. Incorrect Join Order or Structure

• Problem: Joining table3 twice (as t3_1 and t3_2) without isolating the join conditions for id1 and id2 leads to overlapping filters. This disrupts the independent mapping of id1 and id2 to their respective field2 values.
• Example: Placing both t1.id1 = t3_1.id and t1.id2 = t3_2.id in the WHERE clause forces both conditions to be met simultaneously, which fails when either id1 or id2 is NULL.

4. Misuse of UNION for Row Completion

• Problem: Using UNION to combine subsets of data for different NULL scenarios (e.g., id1 present but id2 missing) can functionally achieve the desired result but introduces redundancy and performance overhead. Each UNION segment re-scans tables, increasing execution time.

Troubleshooting Steps, Solutions & Fixes

Step 1: Use Explicit LEFT JOINs for Optional Relationships

Objective: Retain all rows from table1 while allowing optional matches to table3 for id1 and id2.

Solution:

SELECT 
  t1.field0, 
  t1.id1, 
  t1.title1, 
  t1.title2, 
  t1.id2, 
  t2.field1, 
  t3_1.field2 AS "id for 1", 
  t3_2.field2 AS "id for 2"
FROM table1 t1
JOIN table2 t2 ON t1.field0 = t2.field0
LEFT JOIN table3 t3_1 ON t1.id1 = t3_1.id
LEFT JOIN table3 t3_2 ON t1.id2 = t3_2.id;

Explanation:

• JOIN table2: Ensures field1 is included for all table1 rows (since field0 is non-NULL in both tables).
• LEFT JOIN table3 (twice): Separately maps id1 to t3_1.field2 and id2 to t3_2.field2, preserving rows even if id1 or id2 is NULL.

Outcome:

• Row A: Both id1 and id2 are present → FIS1 and FIS3.
• Row B: id1 is present, id2 is NULL → FIS2 and NULL.
• Row C: id1 is NULL, id2 is present → NULL and FIS4.

Step 2: Address NULL Propagation in Result Set

Issue: The result includes NULL for missing field2 values (e.g., id for 2 is NULL for field0 = B), but the desired result shows an empty string instead.

Solution: Use COALESCE or conditional logic to replace NULL with empty strings.

SELECT 
  t1.field0, 
  t1.id1, 
  t1.title1, 
  t1.title2, 
  t1.id2, 
  t2.field1, 
  COALESCE(t3_1.field2, '') AS "id for 1", 
  COALESCE(t3_2.field2, '') AS "id for 2"
FROM table1 t1
JOIN table2 t2 ON t1.field0 = t2.field0
LEFT JOIN table3 t3_1 ON t1.id1 = t3_1.id
LEFT JOIN table3 t3_2 ON t1.id2 = t3_2.id;

Considerations:

• Data Type Consistency: Ensure COALESCE output matches the column’s data type (e.g., use '' for strings, 0 for numbers).

Step 3: Optimize Query Performance

Issue: The UNION-based approach, while functional, may perform poorly with large datasets due to repeated table scans.

Optimization Tactics:

1. Indexing:

   • Create indexes on columns used in join conditions:

     CREATE INDEX idx_table1_id1 ON table1(id1);
     CREATE INDEX idx_table1_id2 ON table1(id2);
     CREATE INDEX idx_table3_id ON table3(id);
     

   • Effect: Speeds up lookups for id1 and id2 in table3.

2. Avoiding UNION:

   • Use the LEFT JOIN solution instead of UNION to minimize table accesses.

3. Query Planning:

   • Use EXPLAIN QUERY PLAN to analyze SQLite’s execution strategy:

     EXPLAIN QUERY PLAN
     SELECT ...; -- Your query here
     

   • Key Metrics: Look for SCAN TABLE versus SEARCH TABLE. SEARCH indicates index usage.

Step 4: Validate Edge Cases

Scenario: Rows where both id1 and id2 are NULL.

Test Data Addition:

INSERT INTO table1 (field0, title1) VALUES ('D', 'Lamp');
INSERT INTO table2 (field0, field1) VALUES ('D', 'L4');

Expected Result:

field0 | id1 | title1 | title2 | id2 | field1 | id for 1 | id for 2
-------+-----+--------+--------+-----+--------+----------+----------
D      | NULL| Lamp   | NULL   | NULL| L4     |          |          

Query Adjustment: The existing LEFT JOIN solution handles this naturally, returning NULL or empty strings for both id for 1 and id for 2.

Step 5: Alternative Approaches and Trade-offs

Approach 1: Correlated Subqueries

SELECT 
  t1.field0, 
  t1.id1, 
  t1.title1, 
  t1.title2, 
  t1.id2, 
  t2.field1, 
  (SELECT field2 FROM table3 WHERE id = t1.id1) AS "id for 1", 
  (SELECT field2 FROM table3 WHERE id = t1.id2) AS "id for 2"
FROM table1 t1
JOIN table2 t2 ON t1.field0 = t2.field0;

Pros:

• Simplifies join logic by avoiding multiple LEFT JOIN clauses.

Cons:

• May underperform with large table3 due to repeated subquery executions.

Approach 2: Combined JOIN and COALESCE

SELECT 
  t1.field0, 
  t1.id1, 
  t1.title1, 
  t1.title2, 
  t1.id2, 
  t2.field1, 
  t3_1.field2 AS "id for 1", 
  t3_2.field2 AS "id for 2"
FROM table1 t1
JOIN table2 t2 USING (field0)
LEFT JOIN table3 t3_1 ON t1.id1 = t3_1.id
LEFT JOIN table3 t3_2 ON t1.id2 = t3_2.id;

Note: USING (field0) is syntactic sugar for ON t1.field0 = t2.field0 and collapses the field0 column to a single instance in the result.

Final Recommendation:
The LEFT JOIN approach with explicit ON clauses (Step 1) is the most efficient and maintainable solution. It correctly handles NULL values, avoids Cartesian products, and leverages SQLite’s optimizer effectively. Use COALESCE or application-layer logic to format NULL values as needed.