A left outer join keeps the rows required by the main side of a query even when related data is absent. Columns from an unmatched related table can be null, letting an API return the primary record instead of silently dropping it. Use it when missing related data is an expected business condition rather than proof that the primary record does not exist. section 8.6 section 10.4
Choose the left side deliberately: it is the entity the response must preserve. An INNER JOIN requires a matching row and can therefore make an invalid ACH file disappear when parsed header or control records were not written. Replacing only the optional parsed-record joins with LEFT JOIN allows the file-list query and its tests to return the file despite its exception. section 8.6
Keep a company when contact details are missing
A company-detail query starts with companies and left-joins company_addresses and company_phones. The company remains available even if an address or phone was never populated or was lost during migration. Because each address and phone can produce rows, application logic then collects unique address and phone IDs before building the response. section 10.4
Listing 8.20 Updating our queries to LEFT JOIN
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)sd LEFT JOIN ach_file_control_records AS afcr#1 ➥ USING (ach_records_type_9_id) #1 LEFT JOIN ach_file_headers AS afh #1 ➥ USING (ach_records_type_1_id) #1 ORDER BY af.created_at DESC
Compare the retained INNER JOINs for required ACH record structure with the LEFT JOINs for optional file-control and header records. section 8.6
Left joins also support graceful fallbacks. A batch search preserves batches whose company is not stored locally, then uses COALESCE to prefer the company-table name, fall back to the batch-header name, and finally return an empty string. Exception-detail queries similarly retain exceptions when recovery options or company information are unavailable; the UI can hide an empty company name while displaying “None Provided” for a missing recovery option. section 10.5.1 section 10.7.3
Use a left outer join when:
- The primary row must appear even if optional related records are missing, such as an ACH file with no parsed header or control record. section 8.6
- A company may lack addresses or phone numbers, while its core record still needs to be returned. section 10.4
- Search results must include batches whose company name is absent from the local companies table. section 10.5.1
- An exception response needs optional recovery, batch-header, or company details without excluding the exception itself. section 10.7.3
