SQL | LeetCode 1907. Salary 범주별 계좌 수 세기 (UNION vs. UNION ALL)

Table: Accounts

+-------------+------+
| Column Name | Type |
+-------------+------+
| account_id  | int  |
| income      | int  |
+-------------+------+
account_id is the primary key (column with unique values) for this table.
Each row contains information about the monthly income for one bank account.

 

Write a solution to calculate the number of bank accounts for each salary category. The salary categories are:

• "Low Salary": All the salaries strictly less than $20000.
• "Average Salary": All the salaries in the inclusive range [$20000, $50000].
• "High Salary": All the salaries strictly greater than $50000.

The result table must contain all three categories. If there are no accounts in a category, return 0.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Accounts table:
+------------+--------+
| account_id | income |
+------------+--------+
| 3          | 108939 |
| 2          | 12747  |
| 8          | 87709  |
| 6          | 91796  |
+------------+--------+
Output: 
+----------------+----------------+
| category       | accounts_count |
+----------------+----------------+
| Low Salary     | 1              |
| Average Salary | 0              |
| High Salary    | 3              |
+----------------+----------------+
Explanation: 
Low Salary: Account 2.
Average Salary: No accounts.
High Salary: Accounts 3, 6, and 8.

1. 각 소득(income)을 급여 범주로 변환한다.
2. 범주별로 몇 개의 계좌가 있는지 세준다.
3. 범주가 하나라도 빠지면 안 되므로, UNION ALL을 사용하여 각 범주를 수동으로 구성한다.

select 'Low Salary' as category, count(*) as accounts_count
from accounts where income < 20000

union all
select 'Average Salary' AS category, count(*) as accounts_count
from accounts where income between 20000 AND 50000

union all
select 'High Salary' as category, count(*) as accounts_count
from accounts where income > 50000

UNION vs. UNION ALL

• UNION: 중복을 제거한다 → 결과에서 중복된 행은 하나만 남김
• UNION ALL: 중복을 제거하지 않는다 → 모든 행을 그대로 포함

SELECT 'Low Salary' AS category, COUNT(*) FROM Accounts WHERE income < 20000
UNION
SELECT 'Low Salary' AS category, COUNT(*) FROM Accounts WHERE income < 20000;

• 결과: 1개 행만 반환됨

SELECT 'Low Salary' AS category, COUNT(*) FROM Accounts WHERE income < 20000
UNION ALL
SELECT 'Low Salary' AS category, COUNT(*) FROM Accounts WHERE income < 20000;

• 결과: 2개 행 반환됨