Reconcile CRM and Billing Accounts with FULL OUTER JOIN
FULL OUTER JOIN CRM and billing accounts by account_code and label each row as Matched, CRM Only, or Billing Only.
- Joins
- CASE expressions
- NULL handling
- Filtering
- Sorting
Exercise brief
Understand the request
Revenue operations controller A system reconciliation must expose matched accounts and records present in only one source.
Pair every department with the count of employees holding each job in it, but show EVERY department (even without employees) AND EVERY job (even without anyone holding it). Use FULL OUTER JOIN. Return department_id, department_name, job_id, job_title, employee_count — ordered by department_id NULLS LAST, job_id NULLS LAST. employee_count is 0 when there is no match.
Return
- Return crm_account_id, crm_account_code, billing_account_id, billing_account_code, match_status in this exact left-to-right order.
Constraints
- Use FULL OUTER JOIN where supported.
- Use a duplicate-safe LEFT JOIN plus UNION ALL emulation in MySQL.
- Do not match NULL keys with each other.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
join_crm_accounts
crm_account_idINTEGERaccount_codeVARCHAR(20)
join_billing_accounts
billing_account_idINTEGERaccount_codeVARCHAR(20)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
FULL OUTER JOIN retains unmatched rows from both inputs; equality never matches NULL to NULL.
Hint 2
Classify rows by which non-null source primary key survived.
Hint 3
MySQL needs all CRM rows UNION ALL only billing rows that found no CRM match.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT c.crm_account_id, c.account_code AS crm_account_code, b.billing_account_id, b.account_code AS billing_account_code, CASE WHEN c.crm_account_id IS NULL THEN 'Billing Only' WHEN b.billing_account_id IS NULL THEN 'CRM Only' ELSE 'Matched' END AS match_status FROM join_crm_accounts c FULL OUTER JOIN join_billing_accounts b ON c.account_code = b.account_code ORDER BY CASE WHEN COALESCE(c.account_code, b.account_code) IS NULL THEN 1 ELSE 0 END, COALESCE(c.account_code, b.account_code), CASE WHEN c.crm_account_id IS NULL THEN 1 ELSE 0 END, c.crm_account_id, b.billing_account_id;Why this works
A full reconciliation preserves both populations. Source primary keys distinguish a genuinely unmatched row from a nullable business key, and UNION ALL with an anti-matched second branch emulates FULL OUTER JOIN without dropping legitimate duplicates.
Success check
Matched, left-only, right-only, and independent NULL-key records all appear exactly once.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| crm_account_id | crm_account_code | billing_account_id | billing_account_code | match_status |
|---|---|---|---|---|
| 1 | A-100 | 501 | A-100 | Matched |
| 2 | B-200 | NULL | NULL | CRM Only |
| 3 | C-300 | 502 | C-300 | Matched |
| NULL | NULL | 503 | D-400 | Billing Only |
| 4 | NULL | NULL | NULL | CRM Only |
| NULL | NULL | 504 | NULL | Billing Only |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
Self Joins & Hierarchical Queries
Query organization charts, trees, and parent-child relationships.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
Open the interactive workspace and practice across SQL topics.