240521 MariaDB(4) - 조인/UNION 연산자/인라인 뷰

(이어서)

 

외부 조인(OUTER JOIN)

 

조인의 조건에 만족되지 않는 행까지도 조회하기 위해서 사용되는 조인

LEFT OUTER JOIN은 왼쪽 테이블(employee)의 데이터를 모두 조회하려고 할 때 사용한다.

RIGHT OUTER JOIN은 오른쪽 테이블(department)의 데이터를 모두 조회하려고 할 때 사용한다.

 

왼(오른) 쪽에 NULL 값이 존재해도 다 조회가 된다.

 

SELECT *
FROM employees e
LEFT OUTER JOIN departments d ON e.dept_no = d.dept_no;
-- RIGHT OUTER JOIN departments d on e.dept_no = d.dept_no;

 

이너 조인과 한 번 비교해 보자!

-- OUTER JOIN과 비교할 INNER JOIN 구문
-- 부서가 지정되지 않은 사원 2명에 대한 정보가 조회되지 않는다.
-- 부서가 지정되어 있어도 부서에 속해있는 사원이 없으면 부서에 대한 정보가 조회되지 않는다.
SELECT e.emp_name,
	d.dept_title,
    e.dept_code,
    e.salary
FROM employee e
INNER JOiN department d ON d.dept_id = e.dept_code
ORDER BY e.dept_code;

 

여기서 INNER JOIN을 LEFT OUTER JOIN으로 바꾸었을 때

아래 출력 결과와 같이 부서 코드가 없던 사원의 정보가 출력된다. (출력 결과는 일부만)

 

LEFT OUTER JOIN 으로 인한 NULL 값을 가진 정보 출력

 

또한 LEFT OUTER JOIN 시에 OUTER은 생략 가능하다.

 

-

 

상호 조인(CROSS JOIN)

한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인한다.

조인 조건 없이 그냥 사용하면 된다.

 

테이블 1 | 테이블 2

    행 1    |  행 4

    행 2    |  행 5

    행 3    |  행 6  이 있을 경우,

 

행 1 - 행 4 조인, 행1 - 행 5 조인, 행1 - 행 6 조인 / 행2 - 행4 조인, 행2- 행5 조인, 행2 - 행6 조인.. 

이런 식으로 모든 행들끼리 조인이 된다.

 

결과는 첫 번째 행의 개수 X 두 번째 행의 개수만큼 조회된다.,

(근데 테스트 용도?로는 사용할 수 있지만 실제 운영 환경에서는 잘 사용되지 않는다고 한다.)

 

SELECT *
FROM employee e
CROSS JOIN departments d;

 

-

 

자체 조인(SELF JOIN)

동일한 테이블을 가지고 조인하여 데이터를 조회한다.

같은 테이블을 조인하고 구분을 위해서 다른 별칭을 준다.

 

셀프 조인 시에는 열의 이름이 동일하기 때문에

조회 결과에서 헷갈릴 수 있으니 별칭을 주거나 보는 데 주의!

 

SELECT *
FROM employees e
INNER JOIN employees m on e.manager_no = m.emp_no;

 

-

 

관련 실습 문제 몇 가지를 풀어보자.

Q1. 70년대생이면서 여자이고, 성이 전 씨인 직원들의 직원명, 주민번호, 부서명, 직급명을 조회

SELECT e.emp_name AS '직원명',
	e.emp_no AS '주민번호',
    d.dept_title AS '부서명',
    j.job_name AS '직급명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN job j ON e.job_code = j.job_code
WHERE e.emp_no LIKE '7%'
	AND SUBSTRING(e.emp_no, 8, 1) = '2'
    AND e.emp_name LIKE '전%';

 

Q1 결과

 

 

Q2. 각 부서별 평균 급여를 조회하여 부서명, 평균 급여를 조회.

단, 부서 배치가 안된 사원들의 평균도 같이 나오게끔 조회

SELECT IFNULL(d.dept_title, '부서없음') AS '부서명',
	FLOOR(AVG(e.salary)) AS '평균급여'
FROM employee e
LEFT OUTER JOIN department d ON e.dept_code = i.dept_id
GROUP BY d.dept_title
ORDER BY d.dept_title;

 

Q2 결과

 

Q3. 각 부서별 총 급여의 합이 천만 원 이상인 부서명, 급여의 합 조회

SELECT d.dept_title AS '부서명',
	FLOOR(SUM(e.salary)) AS '급여의 합'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
GROUP BY e.dept_code
HAVING FLOOR(SUM(e.salary)) >= 10000000
ORDER BY e.dept_code;

 

Q3 결과

 

이 문제 풀 때 계속 에러가 떴었는데 그 이유가 HAVING 때문이었다..

HAVING이 아니라 WHERE절을 썼었는데,

GROUP 함수에 대한 조건은 HAVING절에 써야 하는 것이었다 .. !!!!

 

Q4. 이름에 '형'자가 들어있는 직원들의 사번, 직원명, 직급명을 조회

SELECT e.emp_id AS '사번',
	e.emp_name AS '직원명',
    j.job_name AS '직급명'
FROM employee e
INNER JOIN job j ON e.job_code = j.job_code
WHERE e.emp_name LIKE '%형%';

 

Q4 결과

 

Q5. 해외영업팀에 근무하는 직원들의 직원명, 직급명, 부서코드, 부서명 조회

SELECT e.emp_name AS '직원명',
	j.job_name AS '직급명',
    d.dept_id AS '부서코드',
    d.dept_title AS '부서명'
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
INNER JOIN job j ON e.job_code = j.job_code
WHERE dept_title LIKE '해외%'
ORDER BY d.dept_title;

 

Q5 결과

 

- 

 

UNION/UNION ALL 연산자

두 쿼리의 결과를 하나로 합치는 연산자

중복된 열은 제거되고 데이터가 정렬되어 조회된다.

UNION ALL 연산자는 UNION 연산자와 다르게 중복된 열까지 모두 출력된다.

 

예제 실습을 해보자.

-- employee 테이블에서 부서 코드가 D5인 사원들의 사번, 직원명, 부서코드, 급여 조회
SELECT emp_id,
	emp_name,
    dept_code,
    salary
FROM employee
WHERE dept_code = 'D5';

-- employee 테이블에서 급여가 300만원 초과인 사원들의
-- 사번, 직원명, 부서코드, 급여 조회
SELECT emp_id,
	emp_name,
    dpet_code,
    salary
FROM employee
WHERE salary > 30000000;

 

위 두 쿼리를 UNION 연산자를 사용해서 하나로 합쳐보자.

SELECT emp_id,
	emp_name,
	dept_code,
	salary
FROM employee
WHERE dept_code = 'D5'

UNION

SELECT emp_id,
	emp_name,
	dept_code,
	salary
FROM employee
WHERE salary > 3000000;

 

위에서 조회된 결과가 다 합쳐졌다.

 

그런데 첫 번째 쿼리문 결과 + 두 번째 쿼리문 결과는 14건인데, 

UNION 연산 후 결과가 12건이다. 그 이유는 무엇일까?

그 이유는 조회한 두 쿼리문에 중복되는 결과가 제외되고 나오는 것을 알 수 있다.

* UNION ALL 연산자를 쓰면 중복되는 데이터도 함께 출력해 준다.

 

UNION을 사용한 쿼리문 대신에 WHERE절에 OR 연산자를 사용해서도 처리가 가능하다.

SELECT emp_id,
	emp_name,
    dept_code,
    salary
FROM employee
WHERE dept_code = 'D5' OR salary > 30000000;

 

하나의 쿼리로 처리할 수 있기 때문에 

UNION 연산자는 조금 비효율적이라서 많이 쓰이지는 않는다.

 

또한, 두 쿼리문의 열의 개수가 같아야만 조회가 되며,

열 이름의 순서가 같아야만 올바른 결과 조회가 된다.

 

* UNION 연산자는 위와 같이 대체가 가능하지만, UNION ALL 연산자는 대체할 연산자가 없다

 

-

 

서브 쿼리

하나의 SQL문 안에 포함된 또 다른 SQL문

 

아래 예시를 살펴보자

-- 1. 노옹철 사원과 같은 부서원들을 조회
-- 	1) 노옹철 사원의 부서코드 조회
SELECT emp_name,
	dept_code
FROM employee
WHERE emp_name = '노옹철';

-- 	2) 부서코드가 노옹철 사원의 부서코드와 동일한 사원 조회
SELECT emp_name,
	dept_code
FROM employee
WHERE dept_code = 'D9';
-- 여기서 D9은 첫번째 쿼리문의 결과로 가져온 것임

-- 	3) 위 두 단계를 서브쿼리를 사용하여 하나의 쿼리문으로 작성
SELECT emp_name,
	dept_code
FROM employee
WHERE dept_code = (
	SELEcT dept_code
    FROM employee
    WHERE emp_name = '노옹철'
);

 

1, 2 나눠서 실행한 결과와 3번의 결과는 아래와 동일한 것을 알 수 있었다.

서브 쿼리 예시 결과

 

서브 쿼리 실습 예시를 살펴보자!

-- 전 직원의 평균 급여보다 더 많은 급여를 받고 있는 직원들의
-- 사번, 직원명, 직급 코드, 급여를 조회
-- 1) 전 직원의 평균 급여 조회
SELECT FLOOR(AVG(salary))
FROM employee;

-- 2) 평균 급여보다 더 많은 급여를 받고 있는 직원들 조회
SELECT emp_id,
	emp_name,
    job_code,
    salary
FROM employee
WHERE salary > 3047662;

-- 3) 위의 2단계를 서브 쿼리를 사용하여 하나의 쿼리문으로 작성
SELECT emp_id, 
	emp_name,
    job_code,
    salary
FROM employee
WHERE salary > (
	SELECT FLOOR(AVG(salary))
    FROM employee
);

 

위 쿼리문의 결과로 아래와 같은 결과를 조회할 수 있었다.

 

서브 쿼리 예시 결과

 

-

 

서브 쿼리 구분

서브쿼리는 서브쿼리를 수행한 결괏값의 행과 열의 개수에 따라서 분류할 수 있다.

 

1) 단일행 서브 쿼리

서브 쿼리의 조회 결과 값의 개수가 1개일 때

 

단일행 서브 쿼리 예시 1

-- 노옹철 사원의 급여보다 더 많이 받는 사원의
-- 사번, 직원명, 부서명, 급여 조회

-- 노옹철 사원의 급여
SELECT salary
FROM employee
WHERE emp_name = '노옹철';

-- 노옹철 사원의 급여보다 더 많이 받는 사원을 조회
SELECT e.emp_id,
	e.emp_name,
    d.dept_title,
    e.salary
FROM employee e
INNER JOIN department d ON e.dept_code = d.dept_id
WHERE salary > (
	SELECT salary
    FROM employee
    WHERE emp_name = '노옹철'
);

 

 

예시 1의 결과는 아래와 같다.

조인을 통한 복잡한 쿼리문에서도 위와 같은 서브 쿼리문으로 응용할 수 있다.

 

예시1 결과

 

 

2) 다중행 서브 쿼리

서브 쿼리의 조회 결과 값의 개수가 여러 행 일 때

 

-- 각 부서별 최고 급여를 받는 직원의 이름, 직급코드, 부서코드, 급여 조회
-- 부서별 최고 급여
SELECT dept_code, MAX(salary)
FROM employee
GROUP BY dpet_code; -- (2890000, 3660000, 2490000, 3760000, 3900000, 2550000, 8000000)

-- 위 급여를 받는 사원들 조회
SELECT emp_name,
	job_code,
    dept_code,
    salary
FROM employee
-- WHERE salary IN (2890000, 3660000, 2490000, 3760000, 3900000, 2550000, 8000000)
WHERE salary IN (
	SELECT MAX(salary)
    FROM employee
    GROUP BY dept_code
)
ORDER BY dept_code;

 

다중행 서브 쿼리 예시 결과

 

일반적인 비교 연산자(크기, 동등 비교, >, <, = 등..)는 사용할 수 없다. (비교하려는 값의 개수가 맞지 않기 때문에 에러가 남)

IN, ANY, ALL 등의 연산자 사용 필요! 

 

다음 예시는 SELECT 절에 서브 쿼리를 사용하는 방법이다.

-- 직원에 대한 사번, 이름, 부서코드, 구분(사원/사수) 조회
-- 사수에 해당하는 사번을 조회
SELECT DISTINCT manager_id
FROM employee
WHERE manager_id IS NOT NULL;

-- SELECT절에 서브 쿼리를 사용하는 방법
SELECT emp_id,
	emp_name,
    dept_code,
    CASE WhEN emp_id IN (
    		SELECT DISTINCT manager_id
            FROM employee
            WHERE manager_id IS NOT NULL
         ) THEN '사수'
           ELSE '사원'
      END AS '구분'
FROM employee;

 

WHERE절뿐만 아니라 SELECT절에도 위와 같이 사용할 수 있다.

위 쿼리문의 결과는 아래와 같다.

 

SELECT절 서브 쿼리 예시 결과

 

IN, ANY, ALL 등의 연산자를 사용해야 한다고 했는데, 그에 관련된 예시를 해보자.

아래는 ANY 연산자를 사용한 것이다.

 

-- 대리 직급임에도 과장 직급들의 최소 급여보다
-- 많이 받는 직원들의 사번, 이름, 직급, 급여 조회

-- 과장 직급의 급여 조회
SELECT salary
FROM employee
WHERE job_code = 'J5'; -- (2200000, 2500000, 3760000)

-- ANY는 서브 쿼리의 결과 중 하나라도 조건을 만족하면 TRUE를 반환한다.
-- salary > ANY (2200000, 2500000, 3760000) 와 같은 의미 = 괄호 안 값보다 큰 값이 있다면 true
SELECT emp_id,
	emp_name,
    job_code,
    salary
FROM employee
WHERE job_code = 'J6'
	AND salary > ANY (
		SELECT salary
        FROM employee
        WHERE job_code = 'J5'
    );

 

위 결과로는 아래와 같다.

 

ANY 연산자 사용

 

다만, 여기서 중요한 것은 조건을 주고 비교를 할 때 일반적인 비교 연산자는 사용하면 에러가 난다는 것이다.

ANY는 서브 쿼리의 결과 중 하나라도 조건을 만족하면 TRUE를 반환한다.

 

 

다음은 ALL 연산자를 사용한 예시이다. ( + 조인 )

-- 과장 직급임에도 차장 직급의 최대 급여보다 더 많이 받는
-- 직원들의 사번, 이름, 직급, 급여 조회

-- 차장 직급의 최대 급여 조회
SELECT salary
FROM employee e
INNER JOIN job j ON e.job_code 
WHERE j.job_name = '차장';

-- ALL은 서브 쿼리의 결과 모두가 조건을 만족하면 TRUE를 반환한다.
SELECT e.emp_id,
	e.emp_name,
    j.job_name,
    e.salary
FROm employee e
INNER JOIN job j ON e.job_code = j.job_code
WHERE j.job_name = '과장'
	AND salary > ALL (
    	SELECT salary
        FROM employee e
        INNER JOIN job j ON e.job_code = j.job_code
        WHERE j.job_name = '차장'
    );

 

결과 화면

 

즉, 다중행 서브 쿼리 같은 경우에는 하나의 값과는 비교가 불가능하기 때문에

ANY, ALL 등과 같은 연산자를 사용해주어야 한다.

(= ANY 가 되면 IN이랑 같음, =ALL은 못씀)

 

3) 다중열 서브 쿼리

서브 쿼리의 조회 결과 값은 한 행이지만 열의 수가 여러 개일 때

 

다음은 예시 중 하나이다.

-- 하이유 사원의 같은 부서코드, 같은  직급코드에 해당하는 사원들 조회
-- 하이유 사원의 부서 코드와 직급 코드 조회 (D5, J5)
SELECT dept_code,
	job_code
FROM employee
WHERE emp_name = '하이유';

-- 부서코드가 D5이면서 직급코드가 J5인 사원들을 조회
SELECT emp_name,
	dept_code,
    job_code
FROM employee
WHERE dept_code = 'D5' AND job_code = 'J5';

SELECT emp_name,
	dept_code,
    job_code
FROM employee
WHERE (dept_code, job_code) = (('D5', 'J5'));
-- WHERE (dept_code, job_code) IN (('D5', 'J5')); 와 동일

-- D5, J5 자리에 위 쿼리 넣어주기
SELECT emp_name,
	dept_code,
    job_code
FROM employee
WHERE (dept_code, job_code) = (
	SELECT dept_code,
    	job_code
    FROM employee
    WHERE emp_name = '하이유'
);

 

쌍비교 방식을 사용한 것이다. (마지막줄 참조)

dept_code와 job_code를 괄호로 쌍으로 묶어준 다음 비교하는 방식이다.

 

('D5', 'J5')를 넣어준 부분에 맨 처음 작성했던 쿼리를 넣어주면 된다.

 

결과

 

 

또 다른 예시를 살펴보자.

-- 박나라 사원과 직급코드가 일치하면서 같은 사수를 가지고 있는
-- 사원의 사번, 이름, 직급, 코드, 사수 사번 조회

-- 박나라 사원의 직급 코드와 사수의 사번 조회
SELECT job_code,
	manager_id
FROM employee
WHERE emp_name = '박나라';

-- 다중열 서브 쿼리를 사용해서 쿼리를 작성
SELECT emp_id,
	emp_name,
    dept_code,
    manager_id
FROM employee
WHERE (job_code, manager_id) = (
	SELECT job_cde,
    	   manager_id
    FROM employee
    WHERE emp_name = '박나라'
);

 

결과는 아래와 같다.

 

 

4) 다중행 다중열 서브 쿼리

서브 쿼리의 조회 결괏값이 여러 행, 여러 열일 경우

 

예시를 한 번 보자.

-- 각 부서별 최고 급여를 받는 직원의 사번, 이름, 부서 코드, 급여 조회
-- 부서별 최고 급여 조회
SELECT dept_code,
	MAX(salary)
FROM employee
GROUP BY dept_code;

-- 각 부서별 최고 급여를 받는 직원 조회
-- 이 쿼리는 비효율적
SELECT emp_id,
	emp_name,
    dept_code,
    salary
FROM employee
WHERE dept_code = 'D1' AND salary = '3660000'
	OR dept_code = 'D2' AND salary = '2490000';
    
-- 쌍비교 방식
SELECT emp_id,
	emp_name,
    dept_code,
    salary
FROM employee
WHERE (dept_code, salary) IN (('D1', 3660000), ('D2', 2490000));

-- 다중행 다중열 서브 쿼리를 이용해서 조회
SELECT emp_id,
	emp_name,
    dept_code,
    salary
FROM employee
WHERE (dept_code, salary) IN (
	SELECT dept_code,
    	   MAX(salary)
    FROM employee
    GROUP BY dept_code
)
ORDER BY dept_code;

 

그런데 왜 NULL 값은 나오지 않을까?

NULL 값은 비교값이 아니라서 대상에서 제외되는데,

 

NULL 값을 출력하고 싶은 경우에는

아래와 같이 비교 대상과 출력하고자 하는 대상 모두에게 IFNULL을 씌워주면 된다.

 

... (생략)
WHERE (IFNULL(dept_code, '부서 없음'), salary) IN (
	SELECT IFNULL(dept_code, '부서 없음'),
    	   MAX(salary)
    FROM employee
    GROUP BY dept_code
)
ORDER BY dept_code;

 

결과 화면

 

다중행 다중열 서브 쿼리의 예시 문제 2가지를 풀어보겠다.

 

-- 각 부서별 최소 급여를 받는 사원들의 사번, 이름, 부서코드, 급여 조회
-- 각 부서별 최소 급여 조회
SELECT IFNULL(dept_code, '부서 없음'),
	MIN(salary)
FROM employee
GROUP BY dept_code;

-- 다중행 다중열 서브 쿼리를 사용해서 조회
SELECT emp_id,
	emp_name,
    IFNULL(dept_code, '부서 없음'),
    salary
FROM employee
WHERE (IFNULL(dept_code, '부서 없음'), salary) IN (
	SELECT IFNULL(dept_code, '부서 없음'),
    	MIN(salary)
    FROM employee
    GROUP BY dept_code
 )
 ORDER BY dept_code;

 

쿼리문을 다 작성하고 보니 또 NULL 값이 안 나온다는 걸 깨달아서

급하게 IFNULL을 추가해주었는데, 

이 경우에 SELECT절에 IFNULL을 추가 안 해줘서 계속 값이 올바르게 나오지 않았다. T.T

IFNULL 추가할 때 꼭 ,, 하나라도 빼먹지 말고 추가해주기..

 

서브 쿼리 내에 ORDER BY를 했을 때 왜 결과는 정렬되어 나오지 않을까?

> 서브 쿼리 동작 후 메인 쿼리가 동작해서 조건에 만족하는 애들을 먼저 보여주기 때문

순서 정렬을 하고 싶으면 메인 쿼리에 ORDER BY를 넣어주면 된다.

 

결과 화면

 

-

 

인라인 뷰

FROM절에 서브 쿼리를 제시하고, 서브 쿼리를 수행한 결과를 테이블 대신에 사용한다.

 

SELECT e.사번,
	e.이름,
    e.급여,
    e.연봉
FROM (
	SELECT emp_id AS '사번',
    	emp_name AS '이름',
        salary AS '급여',
        salary * 12 AS '연봉'
    FROM employee
) e;

 

* 별칭을 설정해주지 않으면 오류가 발생한다.

 

FROM절 안 쿼리 > 서브 쿼리

서브 쿼리 Result Set인데, 이것을 테이블처럼 생각해서 메인 쿼리를 실행한다.

 

만약 e.salary * 12 를 SELECT절에 넣는다면, 이건 열 이름이 아니라 연산을 한 것이다.

연산식을 결과로 가져오는 경우 반드시 별칭을 붙여주고 그 별칭으로 값을 가져와야 한다.

별칭으로 값을 SELECT 하지 않을 경우 오류가 난다.

(정해진 별칭을 테이블 내 열로 쓰기 때문에)

 

그럼 인라인 뷰는 어디에 사용할까?

윈도우 뷰 같은 기능은 WHERE 절에 사용할 수 없다.

이런 경우에 결과를 서브 쿼리로 사용해서 테이블로 사용한다.