An INNER JOIN combines related tables only when the required joined records exist. That rule is useful when a result is valid only with its supporting data, but it can also make an otherwise useful row disappear when optional data is missing. section 8.6 section 10.5.1
Choose the join type from the business requirement, not from the table relationship alone. An ACH-file query originally required both a file header and a file control record, so invalid files that skipped parsed-record creation returned no results. Replacing those joins with LEFT JOINs allowed the file to remain visible while its unavailable values could be absent. section 8.6
Use an INNER JOIN when each joined record is required for the response:
- Keep it for exception data joined to exception-code descriptions, where the query needs the description associated with each exception code. section 8.6
- Use it for required ACH file, batch, and control-record relationships in a batch search response. section 10.5.1
- Reconsider it when related company, address, phone, header, or control data can legitimately be absent; a LEFT JOIN preserves the main row. section 8.6 section 10.4
Why an invalid file vanished
A file-list query joined ACH files to header and control tables with INNER JOIN. When validation prevented those parsed records from being stored, the query returned no file at all. Changing only the optional header and control joins to LEFT JOIN preserved the file in the API response, allowing exception handling to remain visible. section 8.6
Listing 8.19 Using INNER JOIN in our original query
SELECT af.ach_files_id AS id,
af.file_name AS filename,
af.created_at AS date,
afh.immediate_origin_name AS originator,
afcr.total_debit_entry_dollar_amount AS debit_total,
afcr.total_credit_entry_dollar_amount
➥ AS credit_total,
FROM ach_files AS af
INNER JOIN ach_records_type_1 AS art1 USING (ach_files_id)
INNER JOIN ach_records_type_9 AS art9 USING
(ach_records_type_1_id)
INNER JOIN ach_file_headers AS afh USING #1
(ach_records_type_1_id) #1
INNER JOIN ach_file_control_records AS afcr USING #1
(ach_records_type_9_id) #1
ORDER BY af.created_at DESC
This query shows several INNER JOINs connecting an ACH file to its required record, header, and control tables. The header and control joins are the ones later changed when those records became optional for a file-list response. section 8.6
