LEFT JOIN Excluding Rows Due to WHERE Clause Misplacement

Issue Overview: LEFT JOIN Conditions Incorrectly Placed in WHERE Clause

The core issue in this scenario stems from a misunderstanding of how LEFT JOIN operations interact with filter conditions placed in the WHERE clause versus the ON clause. The original query intended to retrieve all projects from the Project_List table, along with aggregated counts from related tables (Project_Extras, Project_Highlights, and a CTE LastEntries). However, the query returns only 13 records instead of the expected 108 because critical filter conditions were placed in the WHERE clause, effectively converting LEFT JOIN operations into unintended INNER JOIN logic.

Key Observations:

1. Data Structure Dependencies:

   • Project_List is the primary table containing all projects.
   • Project_Extras and Project_Highlights contain supplemental data for some projects.
   • The LastEntries CTE computes the latest InsertDate values for each project from Project_List and Project_Extras.

2. Intent of the Query:

   • Retrieve all projects from Project_List, regardless of whether they have matching entries in the other tables.
   • For projects with no matching entries in Project_Highlights, show a count of 0.
   • Apply filters on Project_Extras (MonthlyRpt = 'y', BudgetYear = '2021') and Project_Highlights (CreatedDate LIKE '2021-07-%').

3. Problem Symptoms:

   • Missing projects from Project_List in the final result.
   • Incorrect counts (e.g., 0 not appearing for projects with no Project_Highlights entries).

Root Cause Identification:

The WHERE clause was used to enforce conditions on columns from left-joined tables (e.g., b.MonthlyRpt = 'y', c.CreatedDate LIKE '2021-07-%'). This caused the database engine to exclude rows where the left-joined tables had no matching entries (i.e., NULL values), thereby converting LEFT JOIN into an implicit INNER JOIN.

Possible Causes: Misplaced Filters and Join Logic Errors

1. Conditions in WHERE Clause Instead of ON Clause:

   • Issue: Filter conditions on columns from left-joined tables (Project_Extras, Project_Highlights) were placed in the WHERE clause. For example:

     WHERE c.CreatedDate LIKE '2021-07-%' AND b.MonthlyRpt = 'y'
     

     If Project_Highlights has no matching rows for a project, c.CreatedDate will be NULL, and the LIKE condition will exclude the row.

   • Impact: The WHERE clause acts as a post-join filter, removing rows where left-joined tables have NULL values.

2. Incorrect Join Order or Structure:

   • The original query joined four tables (Project_List, Project_Extras, Project_Highlights, LastEntries) without explicitly defining join relationships between secondary tables. For example:

     FROM Project_List AS a
     LEFT JOIN Project_Extras AS b
     LEFT JOIN Project_Highlights AS c
     LEFT JOIN LastEntries AS d
     

     Without ON clauses, this creates a cross join between all tables, leading to unintended combinatorial results.

3. Misuse of CTE (LastEntries):

   • The LastEntries CTE computes the latest InsertDate values but was joined without proper conditions, causing mismatches between Project_List and Project_Extras entries. For example:

     a.InsertDate = d.pl_insert AND b.InsertDate = d.pe_insert
     

     These conditions in the WHERE clause forced an implicit inner join between Project_List/Project_Extras and LastEntries.

4. Aggregation Inconsistencies:

   • The GROUP BY a.ProjID clause grouped results by project ID, but the WHERE clause had already excluded projects with no matches in joined tables, leading to undercounts.

Troubleshooting Steps, Solutions & Fixes

Step 1: Move All Join Conditions to ON Clauses

Rewrite the LEFT JOIN operations to include filter conditions in the ON clause instead of the WHERE clause. This preserves the "leftness" of the join by allowing non-matching rows to remain in the result set.

Original Problematic Code:

FROM Project_List AS a
LEFT JOIN Project_Extras AS b
LEFT JOIN Project_Highlights AS c
LEFT JOIN LastEntries AS d
WHERE
  c.CreatedDate LIKE '2021-07-%' 
  AND b.MonthlyRpt = 'y'
  AND b.BudgetYear = '2021'

Corrected Code:

FROM Project_List AS a
LEFT JOIN Project_Extras AS b
  ON a.ProjID = b.ProjID
  AND b.MonthlyRpt = 'y'  -- Moved to ON clause
  AND b.BudgetYear = '2021'  -- Moved to ON clause
LEFT JOIN Project_Highlights AS c
  ON a.ProjID = c.ProjID
  AND c.CreatedDate LIKE '2021-07-%'  -- Moved to ON clause
LEFT JOIN LastEntries AS d
  ON a.ProjID = d.projid
  AND a.InsertDate = d.pl_insert
  AND b.InsertDate = d.pe_insert

Explanation:

• Conditions on b.MonthlyRpt, b.BudgetYear, and c.CreatedDate are evaluated during the join, not after. This allows Project_List rows to remain even if there are no matches in Project_Extras or Project_Highlights.

Step 2: Remove Implicit Cross Joins

Ensure all joins have explicit ON clauses to prevent unintended cross joins.

Original Problematic Code:

LEFT JOIN Project_Extras AS b
LEFT JOIN Project_Highlights AS c

Corrected Code:

LEFT JOIN Project_Extras AS b ON a.ProjID = b.ProjID
LEFT JOIN Project_Highlights AS c ON a.ProjID = c.ProjID

Explanation:

• Without ON clauses, SQLite performs a cross join between Project_Extras and Project_Highlights, generating a Cartesian product. Explicit ON clauses enforce a 1:1 or 1:many relationship.

Step 3: Adjust Aggregation for NULL Handling

Use COUNT(c.CreatedDate) instead of COUNT(*) to count only non-NULL values from Project_Highlights.

Original Problematic Code:

SELECT count(c.CreatedDate)

Corrected Code:

SELECT COUNT(c.CreatedDate)  -- Counts non-NULL entries only

Explanation:

• COUNT(column) ignores NULL values, so projects with no Project_Highlights entries will show 0.

Step 4: Validate CTE (LastEntries) Logic

Ensure the LastEntries CTE correctly computes the latest dates and is joined properly.

Original Problematic Code:

LEFT JOIN LastEntries AS d
WHERE a.InsertDate = d.pl_insert AND b.InsertDate = d.pe_insert

Corrected Code:

LEFT JOIN LastEntries AS d
  ON a.ProjID = d.projid
  AND a.InsertDate = d.pl_insert
  AND b.InsertDate = d.pe_insert

Explanation:

• Moving these conditions to the ON clause ensures that Project_List rows are not filtered out if there’s no matching LastEntries row.

Step 5: Final Query Structure

Combine all fixes into a single query:

WITH LastEntries (ProjID, pl_insert, pe_insert) AS (
  SELECT
    projid,
    MAX(InsertDate),
    (SELECT MAX(InsertDate) FROM project_extras WHERE projid = e.projid)
  FROM project_list e
  GROUP BY projid
)
SELECT
  a.ProjID,
  a.Project_Name,
  COUNT(c.CreatedDate) AS HighlightCount,  -- Count non-NULL entries
  a.Manager
FROM Project_List AS a
LEFT JOIN Project_Extras AS b
  ON a.ProjID = b.ProjID
  AND b.MonthlyRpt = 'y'
  AND b.BudgetYear = '2021'
LEFT JOIN Project_Highlights AS c
  ON a.ProjID = c.ProjID
  AND c.CreatedDate LIKE '2021-07-%'
LEFT JOIN LastEntries AS d
  ON a.ProjID = d.projid
  AND a.InsertDate = d.pl_insert
  AND b.InsertDate = d.pe_insert
GROUP BY a.ProjID
ORDER BY a.Manager, a.ProjID;

Step 6: Verify Results

1. Test with Minimal Data:

   • Create a test dataset with:
     • 1 project in Project_List with no entries in Project_Highlights.
     • 1 project with valid entries in all tables.
   • Verify that the first project returns HighlightCount = 0.

2. Check Join Intermediate Results:

   • Remove the GROUP BY and COUNT to inspect raw joined data:

     SELECT a.ProjID, c.CreatedDate
     FROM Project_List AS a
     LEFT JOIN Project_Highlights AS c
       ON a.ProjID = c.ProjID
       AND c.CreatedDate LIKE '2021-07-%'
     

     Ensure projects without highlights show c.CreatedDate as NULL.

Step 7: Handle Edge Cases

1. Projects with No Project_Extras Entries:

   • If b.MonthlyRpt or b.BudgetYear is NULL due to a missing Project_Extras row, the LEFT JOIN will still include the project, but b.MonthlyRpt = 'y' in the ON clause will exclude non-matching Project_Extras rows.

2. Multiple Entries in Project_Highlights:

   • Use COUNT(DISTINCT c.CreatedDate) if duplicate dates should be counted once.

By systematically moving filter conditions from the WHERE clause to the ON clause and ensuring explicit join relationships, the query will correctly include all projects from Project_List, with accurate counts from related tables. This approach preserves the intent of LEFT JOIN while applying necessary filters during the join process.