UNION vs UNION ALL counts

SQL data engineering interview problem. Difficulty: beginner. Pattern: Set Operations. About 8 minutes. Free to practice.

Employees and contractors have separate id lists, and a few people appear in both lists. You want to see how the count changes depending on whether you remove those overlaps or keep them. Combine employees.employee_id and contractors.contractor_id into one list of ids. Return 1 row with two numbers: union_count, the number of ids when you use UNION (which removes repeated ids), and union_all_count, the number of ids when you use UNION ALL (which keeps every id, even repeated ones). In this data the ids 1 and 2 appear in both tables.

Requirements

  • Return exactly 1 row with the columns union_count and union_all_count.
  • union_count counts the ids after UNION, so each id once.
  • union_all_count counts the ids after UNION ALL, so overlapping ids are counted twice.

Constraints

  • The tables are employees (column employee_id) and contractors (column contractor_id).
  • Rename both columns to the same name, such as id, so they can be stacked.
  • In this data, ids 1 and 2 are in both tables.

Examples

Input: employees employee_id | 1 | 2 | 3 | 4 | 5 | 6 | contractors contractor_id | 1 | 2 | 19 | 20 | Output: union_count | union_all_count 8 | 10 Why this passes: UNION collapses the shared 1 and 2 (6+4-2=8). UNION ALL keeps every row from both sides (10).

Topics: lakebench, sql, union, union all.

More SQL interview questions · All interview problems · Learn data engineering

beginner

UNION vs UNION ALL counts

Interview-style drill: Compare distinct UNION count vs UNION ALL count of person ids.

Combine `employees.employee_id` and `contractors.contractor_id`. Return 1 row with: - `union_count`: COUNT of ids under UNION (deduplicated) - `union_all_count`: COUNT of ids under UNION ALL (keeps overlaps) Overlapping ids in the seed are 1 and 2.