多表左连接查询时某表无匹配数据返回空的问题排查

Ah, I see the issue here! You've placed your search filter in the wrong part of the query—specifically, inside the LEFT JOIN clause for T4 instead of using a WHERE clause. Let me break this down for you:

When you use LEFT JOIN, the ON clause is only meant to define how the tables should be linked together, not to filter the final result set. Even if there's no matching row in the right table (like T3 in your case), the row from the left table (T1) will still be included in the result—with NULL values for all the right table's columns.

By putting your (T1.mydata LIKE :s OR ...) condition inside T4's LEFT JOIN ON clause, you're only affecting which rows from T4 get joined to T1, not which T1 rows stay in the final output. Worse, when T3 has no matching entry_id, T3.mydata is NULL, so T3.mydata LIKE :s evaluates to UNKNOWN (SQL treats NULL comparisons this way). If your search string only matches T3's data, this makes the entire OR condition evaluate to UNKNOWN, which gets treated as FALSE in the ON clause—so T4 doesn't join, but T1's row should still be there... except your query returns empty, which points to the core problem: you're not filtering the result set properly.

Corrected Query #

The fix is simple: move your search condition to a WHERE clause. This way, you're telling the query to only keep T1 rows where either T1's own mydata matches, or any of the joined tables' mydata matches (if they exist).

SELECT DISTINCT T1.id AS id
FROM T1
LEFT JOIN T2 ON T1.user_id = T2.user_id AND T2.entry_id = T1.id
LEFT JOIN T3 ON T1.user_id = T3.user_id AND T3.entry_id = T1.id
LEFT JOIN T4 ON T1.user_id = T4.user_id AND T4.entry_id = T1.id
WHERE 
    T1.mydata LIKE :s
    OR T2.mydata LIKE :s
    OR T3.mydata LIKE :s
    OR T4.mydata LIKE :s;

Quick Note on NULL Handling #

When T2/T3/T4 have no matching rows, their mydata columns are NULL. NULL LIKE :s will evaluate to UNKNOWN, which the WHERE clause treats as FALSE—so those conditions just get ignored, and the query only checks the other tables (including T1 itself) for matches.

If you want to simplify the condition a bit, some databases let you use COALESCE to check all columns at once (this works the same way as the OR conditions above):

SELECT DISTINCT T1.id AS id
FROM T1
LEFT JOIN T2 ON T1.user_id = T2.user_id AND T2.entry_id = T1.id
LEFT JOIN T3 ON T1.user_id = T3.user_id AND T3.entry_id = T1.id
LEFT JOIN T4 ON T1.user_id = T4.user_id AND T4.entry_id = T1.id
WHERE COALESCE(T1.mydata, T2.mydata, T3.mydata, T4.mydata) LIKE :s;

内容的提问来源于stack exchange，提问作者Adriaan Nel