[SQL] 18. 집합연산자

집합연산자

• 서로 다른 쿼리 결과값들을 집합의 개념으로 연산한 결과를 출력해주는 연산자

합집합 - UNION

• 두 쿼리의 결과를 합쳐출력하는 연산자
• 중복값의 행이 있는 경우 중복값을 제거하여 출력한다.
• MySQL에서는 자동 정렬을 해주지 않는다.

 

• 10, 20 부서 직원의 출력 값

SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20);

 

employee_id|last_name|salary  |department_id|

-----------+---------+--------+-------------+

        200|Whalen   | 5324.00|           10|

        201|Hartstein|15730.00|           20|

        202|Fay      | 7260.00|           20|

 

• 30 부서 직원의 출력 값

SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (30);

employee_id|last_name |salary  |department_id|

-----------+----------+--------+-------------+

        114|Raphaely  |13310.00|           30|

        115|Khoo      | 3751.00|           30|

        116|Baida     | 3509.00|           30|

        117|Tobias    | 3388.00|           30|

        118|Himuro    | 3146.00|           30|

        119|Colmenares| 3025.00|           30|

 

• 두 쿼리의 사이에 UNION 키워드를 위치시킨다.

SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) UNION SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (30);

employee_id|last_name |salary  |department_id|

-----------+----------+--------+-------------+

        200|Whalen    | 5324.00|           10|

        201|Hartstein |15730.00|           20|

        202|Fay       | 7260.00|           20|

        114|Raphaely  |13310.00|           30|

        115|Khoo      | 3751.00|           30|

        116|Baida     | 3509.00|           30|

        117|Tobias    | 3388.00|           30|

        118|Himuro    | 3146.00|           30|

        119|Colmenares| 3025.00|           30|

 

• 20번 부서의 정보가 두번 선택되었으나 출력 결과에서는 중복값이 제거되어 한번의 결과값만 출력되었음.

SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) UNION SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (20, 30);

employee_id|last_name |salary  |department_id|

-----------+----------+--------+-------------+

        200|Whalen    | 5324.00|           10|

        201|Hartstein |15730.00|           20|

        202|Fay       | 7260.00|           20|

        114|Raphaely  |13310.00|           30|

        115|Khoo      | 3751.00|           30|

        116|Baida     | 3509.00|           30|

        117|Tobias    | 3388.00|           30|

        118|Himuro    | 3146.00|           30|

        119|Colmenares| 3025.00|           30|

• 합쳐 출력할 쿼리들의 컬럼 구성이나 데이터의 내용이 다르더라도 데이터타입과 컬럼의 수가 일치한다면 출력값을 만들어준다.
• 컬럼명은 첫번째 쿼리의 컬럼명을 사용한다.

SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) UNION SELECT department_id, department_name, manager_id, location_id FROM departments WHERE department_id <= 30;

employee_id|last_name     |salary  |department_id|

-----------+--------------+--------+-------------+

        200|Whalen        | 5324.00|           10|

        201|Hartstein     |15730.00|           20|

        202|Fay           | 7260.00|           20|

         10|Administration|  100.00|         1700|

         20|Marketing     |  201.00|         1800|

         30|Purchasing    |  114.00|         1700|

SELECT employee_id, first_name, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) UNION SELECT department_id, department_name, manager_id, location_id FROM departments WHERE department_id <= 30;

SQL Error [1222] [21000]: The used SELECT statements have a different number of columns

 

• 다음과 같이 실제 테이블의 데이터가 아닌 리터럴 값으로도 컬럼의 자리를 채울 수 있다.

SELECT employee_id, first_name, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) UNION SELECT department_id, 'first_name', department_name, manager_id, location_id FROM departments WHERE department_id <= 30;

employee_id|first_name|last_name     |salary  |department_id|

-----------+----------+--------------+--------+-------------+

        200|Jennifer  |Whalen        | 5324.00|           10|

        201|Michael   |Hartstein     |15730.00|           20|

        202|Pat       |Fay           | 7260.00|           20|

         10|first_name|Administration|  100.00|         1700|

         20|first_name|Marketing     |  201.00|         1800|

         30|first_name|Purchasing    |  114.00|         1700|

 

• NULL 값 역시 활용가능하다.

SELECT employee_id, first_name, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) UNION SELECT department_id, NULL, department_name, manager_id, location_id FROM departments WHERE department_id <= 30;

 

employee_id|first_name|last_name     |salary  |department_id|

-----------+----------+--------------+--------+-------------+

        200|Jennifer  |Whalen        | 5324.00|           10|

        201|Michael   |Hartstein     |15730.00|           20|

        202|Pat       |Fay           | 7260.00|           20|

         10|          |Administration|  100.00|         1700|

         20|          |Marketing     |  201.00|         1800|

         30|          |Purchasing    |  114.00|         1700|

• 합쳐진 쿼리 전체에 대한 정렬을 하는 경우 쿼리의 가장 아랫부분에 작성해준다.

SELECT department_id, NULL, department_name, manager_id, location_id FROM departments WHERE department_id <= 30 UNION SELECT employee_id, first_name, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) ORDER BY department_name;

department_id|NULL    |department_name|manager_id|location_id|

-------------+--------+---------------+----------+-----------+

           10|        |Administration |    100.00|       1700|

          202|Pat     |Fay            |   7260.00|         20|

          201|Michael |Hartstein      |  15730.00|         20|

           20|        |Marketing      |    201.00|       1800|

           30|        |Purchasing     |    114.00|       1700|

          200|Jennifer|Whalen         |   5324.00|         10|

 

 

UNION ALL

• 중복값제거 없이 결과를 쿼리 순서 그대로 연결하여 출력하는 연산자.

• 두 쿼리에서 20번 부서의 정보가 두번 출력되었고, 결과값에서도 중복되어 출력되는 것이 확인 됨.

SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) UNION ALL SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (20, 30);

employee_id|last_name |salary  |department_id|

-----------+----------+--------+-------------+

        200|Whalen    | 5324.00|           10|

        201|Hartstein |15730.00|           20|

        202|Fay       | 7260.00|           20|

        201|Hartstein |15730.00|           20|

        202|Fay       | 7260.00|           20|

        114|Raphaely  |13310.00|           30|

        115|Khoo      | 3751.00|           30|

        116|Baida     | 3509.00|           30|

        117|Tobias    | 3388.00|           30|

        118|Himuro    | 3146.00|           30|

        119|Colmenares| 3025.00|           30|

 

 

교집합 - INTERSECT

• 두 쿼리의 결과에서 모든 컬럼의 값이 동일한 중복된 값들을 출력해주는 연산자.

SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) INTERSECT SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (20, 30);

employee_id|last_name|salary  |department_id|

-----------+---------+--------+-------------+

        201|Hartstein|15730.00|           20|

        202|Fay      | 7260.00|           20|

 

 

차집합 - EXCEPT

• 쿼리의 결과에서 다른 쿼리의 결과값과 동일한 행을 제외한 나머지를 출력하는 연산자.
• 첫번째 쿼리에서 두번째 쿼리의 결과를 제외한 값을 출력하는 형태이므로 쿼리의 순서도 중요하다.

SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) EXCEPT SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (20, 30);

employee_id|last_name|salary |department_id|

-----------+---------+-------+-------------+

        200|Whalen   |5324.00|           10|

• 첫번째 쿼리의 결과를 두번째 쿼리가 모두 가지고 있는 경우 모든 행을 제외해야 하므로 출력되는 행이 없다.

SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20) EXCEPT SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id IN (10, 20, 30);

employee_id|last_name|salary|department_id|

-----------+---------+------+-------------+