Find Mismatches Between Tables

How would you write a SQL query using joins to compare records between two tables and find mismatches?

Use the provided employee record tables. Return records that are missing from either table or have different values in any compared attribute.

Output

1. One row per mismatched employee record
2. Include employee_id, source and target values for name, status, hourly rate, and department, plus mismatch_type
3. Sort by employee_id ascending

Output details

• Return columns in exactly this order: employee_id, source_employee_name, target_employee_name, source_status, target_status, source_hourly_rate, target_hourly_rate, source_department, target_department, mismatch_type.
• Treat NULL and NULL as equal, but treat NULL versus any non-NULL value as a field mismatch.
• Preserve NULL values in output rather than replacing them with zeros, empty strings, or other defaults.
• Label mismatches exactly MISSING_FROM_SOURCE, MISSING_FROM_TARGET, or FIELD_MISMATCH based on which records or fields differ.