MySQL多表关联无记录需返回默认值，左连接是否可行？

Absolutely—using a LEFT JOIN (with the right table ordering and condition placement) is exactly the fix you need here. Let me break down why your previous COALESCE/IFNULL attempts didn't work, and how to make this work correctly.

Why COALESCE/IFNULL Failed #

Those functions only handle NULL values within existing rows. If your query returns zero rows entirely (like when there are no TRANSACTION_LINE records with line_object_type = 6), there's no row for those functions to act on. They can't create a row out of thin air—you need a way to guarantee at least one row is returned, which is where LEFT JOIN comes in.

The Solution: Left Join with EX_WORK as the Base Table #

The trick is to start your query with EX_WORK (or a virtual table if EX_WORK might also be empty) as the "driving" table, then LEFT JOIN to the other tables. This ensures you get at least the rows from EX_WORK even if there are no matches in the other tables.

Key Rules to Follow:

• Place the line_object_type = 6 condition in the ON clause of the TRANSACTION_LINE join, not the WHERE clause. If you put it in WHERE, it will filter out rows where there's no match, turning your LEFT JOIN into an INNER JOIN.
• Use COALESCE to fall back to your default values for columns that might be NULL from the left join.

Example Query

Assuming your other two tables are joined to TRANSACTION_LINE, here's how the query would look:

SELECT
  -- Use EX_WORK's key_1 directly, since you want its default value
  ew.key_1 AS key_1,
  -- line_Id will automatically be NULL if no TRANSACTION_LINE match exists
  tl.line_Id,
  -- Fall back to 'OFF' if TENDER_CODE is NULL (no matching transaction line)
  COALESCE(tl.TENDER_CODE, 'OFF') AS TENDER_CODE
FROM EX_WORK ew
-- Left join to TRANSACTION_LINE, with the type filter in the ON clause
LEFT JOIN TRANSACTION_LINE tl
  ON tl.line_object_type = 6
  -- Add joins to your other two tables here, also using LEFT JOIN to preserve rows
  LEFT JOIN Table3 t3 ON tl.some_matching_id = t3.some_matching_id
  LEFT JOIN Table4 t4 ON t3.another_matching_id = t4.another_matching_id;

What If EX_WORK Might Also Be Empty?

If EX_WORK could have no rows either, you can create a virtual base table to guarantee a row exists for your defaults:

SELECT
  COALESCE(ew.key_1, 'your_default_key_value') AS key_1,
  tl.line_Id,
  COALESCE(tl.TENDER_CODE, 'OFF') AS TENDER_CODE
-- Generate a virtual row with your desired default key_1
FROM (SELECT 'your_default_key_value' AS key_1 FROM DUAL) ew
LEFT JOIN TRANSACTION_LINE tl
  ON tl.line_object_type = 6
  -- Add other left joins here as needed
WHERE tl.line_object_type IS NULL; -- Optional: Only return default if no matches exist

Final Note #

This approach will reliably return the default values you want when there are no matching TRANSACTION_LINE records. The LEFT JOIN ensures you have a row to work with, and COALESCE handles the NULL values from the unmatched joins.

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