Return Every Employee with a Mapped or Default Unique ID

Return every employee together with their mapped unique identifier. When an employee has no mapping, use a supplied default identifier.

Input Tables

employees(employee_id INTEGER PRIMARY KEY, employee_name TEXT NOT NULL)

employee_id_mapping(employee_id INTEGER PRIMARY KEY, unique_id INTEGER NOT NULL)

id_policy(default_unique_id INTEGER NOT NULL) contains exactly one row.

Each mapping row references an existing employee. The policy table supplies the otherwise unspecified fallback value for this exercise.

Output Contract

Write one read-only PostgreSQL query returning employee_id, employee_name, and unique_id, with one row for every employee and rows ordered by employee_id ascending. Use the mapped identifier when present and the policy's default otherwise.

Example

employees:

employee_id_mapping contains (employee_id = 2, unique_id = 902). id_policy contains default_unique_id = -1.

Expected result:

Constraints and Clarifications

• Employee names need not be unique.
• Missing mappings must not remove employees from the result.
• A mapped value of zero is valid and must not be treated as missing.
• There is at most one mapping per employee.