PHP MySQL使用CASE语句时左连接NULL结果无法显示求助

Hey there! Let's break down why you're only seeing Clients with matching Hours records (and non-zero Onsite/Remote Hours) instead of all Clients that have those hours—whether they have a Hours entry or not.

The Most Likely Culprit: WHERE Clause Filtering NULLs #

When you use a LEFT JOIN, Clients without matching Hours rows will have NULL values for all Hours table columns. If your WHERE clause includes conditions like h.Onsite_Hours > 0 or h.Remote_Hours > 0, those NULL values will fail the check (since NULL doesn't evaluate to true for any comparison) and get filtered out—turning your LEFT JOIN into an accidental INNER JOIN.

Example of the Problematic Query #

Let's say your current code looks something like this:

SELECT 
  c.ClientID,
  c.ClientName,
  CASE WHEN h.HourType = 'Onsite' THEN h.Hours ELSE 0 END AS Onsite_Hours,
  CASE WHEN h.HourType = 'Remote' THEN h.Hours ELSE 0 END AS Remote_Hours
FROM Clients c
LEFT JOIN Hours h ON c.ClientID = h.ClientID
WHERE h.Onsite_Hours > 0 OR h.Remote_Hours > 0 -- This filters out NULL Hours rows

Fix 1: Move Hours Filters to the JOIN ON Clause #

Instead of filtering in WHERE, add your Hours-specific conditions directly to the LEFT JOIN's ON clause. This only limits which Hours rows get joined, but keeps all Clients rows intact:

SELECT 
  c.ClientID,
  c.ClientName,
  COALESCE(SUM(CASE WHEN h.HourType = 'Onsite' THEN h.Hours ELSE 0 END), 0) AS Onsite_Hours,
  COALESCE(SUM(CASE WHEN h.HourType = 'Remote' THEN h.Hours ELSE 0 END), 0) AS Remote_Hours
FROM Clients c
LEFT JOIN Hours h 
  ON c.ClientID = h.ClientID
  AND h.HourType IN ('Onsite', 'Remote') -- Filter Hours types here instead of WHERE
GROUP BY c.ClientID, c.ClientName

The COALESCE ensures that if there are no matching Hours rows, we return 0 instead of NULL for the hour totals.

Fix 2: Handle NULLs in Your WHERE Clause #

If you need to filter for Clients with non-zero hours (either from Hours table or their own Client-level hours), use COALESCE to replace NULLs with 0 (or the Client's own hour values if they exist in the Clients table):

SELECT 
  c.ClientID,
  c.ClientName,
  COALESCE(h.Onsite_Hours, c.Onsite_Hours) AS Onsite_Hours, -- Use Client's hours if no Hours entry
  COALESCE(h.Remote_Hours, c.Remote_Hours) AS Remote_Hours
FROM Clients c
LEFT JOIN Hours h ON c.ClientID = h.ClientID
WHERE 
  COALESCE(h.Onsite_Hours, c.Onsite_Hours) > 0 
  OR COALESCE(h.Remote_Hours, c.Remote_Hours) > 0

If Your CASE Logic Is the Issue #

Double-check that your CASE statements aren't accidentally excluding NULLs. For example, if you're writing CASE WHEN h.Onsite_Hours IS NOT NULL THEN ..., it will skip Clients without Hours entries. Instead, use COALESCE to fall back to the Client's own hour values:

CASE 
  WHEN h.Onsite_Hours IS NOT NULL THEN h.Onsite_Hours 
  ELSE c.Onsite_Hours 
END AS Onsite_Hours

If you can share your exact SQL query and table schema, we can refine this further—but these fixes should resolve the core issue of missing Client records when there's no matching Hours data.

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