Unified Customer and Employee Records

This challenge focuses on combining data from two distinct but related sources into a single, unified view. In many real-world scenarios, you'll encounter situations where you need to consolidate information about different entities that share common attributes. Using UNION is a fundamental SQL operation for achieving this efficiently.

Problem Description

Your task is to retrieve a list of all individuals who are either customers or employees of a company. The company maintains separate tables for its customers and employees, but both tables contain a person_id and a name. You need to generate a single result set that contains the person_id and name for all unique individuals present in either the customer table or the employee table.

Key Requirements:

• Combine records from the Customers table and the Employees table.
• The output should contain two columns: person_id and name.
• Each unique individual should appear only once in the final result, even if they exist in both tables.

Expected Behavior:

The query should return a distinct list of person_id and name pairs. If a person_id is present in both Customers and Employees tables, it should only be listed once.

Edge Cases:

• A person might be both a customer and an employee.
• A person might be only a customer.
• A person might be only an employee.
• Tables might be empty.

Examples

Example 1:

Input:

Customers table:

Employees table:

Output:

Explanation: Alice (person_id 101) is in both tables. Bob (person_id 102) is only in Customers. Charlie (person_id 103) is only in Employees. The output includes all unique individuals.

Example 2:

Input:

Customers table:

Employees table:

Output:

Explanation: No overlap between the tables. Both David and Eve are distinct individuals and are included in the result.

Example 3: (Edge Case - Empty Tables)

Input:

Customers table:

Employees table:

Output:

Explanation: When both input tables are empty, the resulting combined list is also empty.

Constraints

• The person_id column is an integer.
• The name column is a string.
• The Customers and Employees tables will have at least the person_id and name columns.
• The solution should be efficient, with a time complexity that scales well with the size of the input tables.

Notes

• Consider the behavior of UNION versus UNION ALL. Which one is appropriate here, and why?
• The order of person_id and name in the output columns matters and must match the specified order.
• Ensure that the data types of the columns being combined are compatible.