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	employee_name
1	Employee A
2	Employee B

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

Expected result:

employee_id	employee_name	unique_id
1	Employee A	-1
2	Employee B	902

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.

Hint: Preserve the employee side of the relationship

A lookup that requires both sides to match drops exactly the employees who need the fallback value.

Employees With Mapped Unique Identifiers

Table employees

column	type	constraints
employee_id	INTEGER	PRIMARY KEY
employee_name	TEXT	NOT NULL

Sample data:

employee_id	employee_name
104	Priya Raman
101	Marcus Lee
106	Aisha Bello
103	Priya Raman
102	Dana Ortiz
105	Tomas Novak

Table employee_id_mapping

column	type	constraints
employee_id	INTEGER	PRIMARY KEY
unique_id	INTEGER	NOT NULL

Sample data:

employee_id	unique_id
105	50027
101	50011
103	0
106	-1

Table id_policy

column	type	constraints
default_unique_id	INTEGER	NOT NULL

Sample data:

default_unique_id
-1

Expected output

employee_id	employee_name	unique_id
101	Marcus Lee	50011
102	Dana Ortiz	-1
103	Priya Raman	0
104	Priya Raman	-1
105	Tomas Novak	50027
106	Aisha Bello	-1

Hints

1. Preserve the employee side of the relationship: a lookup that requires both sides to match drops exactly the employees who need the fallback value.