仅用指定JOIN及GROUP_CONCAT能否实现含NULL值的两表关联结果？

Absolutely yes! This is exactly what LEFT JOIN and RIGHT JOIN are designed for—they let you retain all records from one table even when there’s no matching entry in the other, which results in NULL values for the columns from the unmatched table. Here’s how to pull this off, depending on which side’s records you want to keep:

Scenario 1: Keep all records from Collections, include matching (or NULL) OverdueCollection data #

If your original "non-NULL result set" came from an INNER JOIN (which only returns rows with matches in both tables), switching to a LEFT JOIN will preserve every row in Collections. For rows where there’s no corresponding entry in OverdueCollection, all OverdueCollection columns will show NULL. You can use GROUP_CONCAT to aggregate any related overdue records (if multiple exist) into a single string, or it’ll return NULL if there are none.

Example query (assuming collection_id is the shared key):

SELECT 
  c.collection_id,
  c.collection_name,
  GROUP_CONCAT(oc.overdue_amount) AS overdue_amounts,
  oc.overdue_date -- Will be NULL if no matching overdue record
FROM Collections c
LEFT JOIN OverdueCollection oc 
  ON c.collection_id = oc.collection_id
GROUP BY c.collection_id, c.collection_name;

Scenario 2: Keep all records from OverdueCollection, include matching (or NULL) Collections data #

If you need to retain every row in OverdueCollection instead, use a RIGHT JOIN. This will keep all overdue entries, and fill in Collections columns with NULL where there’s no matching collection record. Again, GROUP_CONCAT works here to aggregate related collection data if needed.

Example query:

SELECT 
  oc.overdue_id,
  oc.overdue_amount,
  GROUP_CONCAT(c.collection_name) AS related_collections,
  c.collection_date -- Will be NULL if no matching collection record
FROM Collections c
RIGHT JOIN OverdueCollection oc 
  ON c.collection_id = oc.collection_id
GROUP BY oc.overdue_id, oc.overdue_amount;

Is There a Better Way? #

Given your constraints, these are the optimal approaches. LEFT/RIGHT JOIN are the most direct tools for this use case—they’re purpose-built to include unmatched records (and thus NULL values) while still letting you join related data.

A quick note on GROUP_CONCAT: Make sure you group by the primary key (or a unique identifier) of the table you’re retaining (e.g., c.collection_id for LEFT JOIN) to avoid unintended aggregation of unrelated rows. Also, if you need to include all records from both tables (a full outer join), your allowed tools don’t include FULL JOIN, but you could combine a LEFT JOIN and RIGHT JOIN with UNION if that’s permitted (though your constraints don’t explicitly mention UNION—if it’s off-limits, stick to single LEFT/RIGHT joins based on which side you need to prioritize).

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