[Mysql] 인라인뷰, 조건의 쌍비교 방식

INLINE VIEW란?

• 가상의 테이블 오브젝트인 VIEW를 서브쿼리로 구현하는 활용방법이다.
• VIEW와 마찬가지로 테이블을 연산하여 만들어 활용하는 형태이고 서브쿼리의 출력 결과가 가상의 데이터로 구성된다.
• 데이터 값들은 모두 기반이 되는 테이블의 데이터를 가공하여 사용한다.
• 함수나 표현식과 같은 연산을 사용하는 경우, 컬럼명으로 사용할 수 없기 때문에 이 경우 COLUMN ALIAS를 활용하여 컬럼명을 바꾸어 출력하도록 작성한다.
• 또한 JOIN과 같이 테이블을 대체하는 경우 인라인 뷰에서는 테이블 명이 없기 때문에 TABLE ALIAS를 필수적으로 추가해서 사용해야 한다.

사원들 중 본인이 근무하는 부서의 평균 급여값보다 더 높은 급여를 받는 직원의 last_name, salary, department_id, 부서의 급여 평균값을 출력하시오.

SELECT employee_id, last_name, salary, b.department_id dept_id, salavg
FROM employees a JOIN (	SELECT department_id, AVG(salary) salavg
				FROM employees
				GROUP BY department_id) b
ON a.department_id = b.department_id
AND salary > salavg;

employee_id|last_name|salary  |dept_id|salavg      |
-----------+---------+--------+-------+------------+
        201|Hartstein|14300.00|     20|10450.000000|
        114|Raphaely |12100.00|     30| 4565.000000|
        120|Weiss    | 8800.00|     50| 3823.111111|

연습문제

employees 테이블로부터 평균 이상의 급여를 받는 모든 사원의 employee_id, last_name, salary를 출력하되 salary를 기준으로 오름차순 정렬하여 출력하시오.

SELECT employee_id, last_name, salary
FROM employees
WHERE salary >= (
	SELECT AVG(salary)
	FROM employees
)
ORDER BY salary ASC;

employee_id|last_name |salary  |
-----------+----------+--------+
        203|Mavris    | 7150.00|
        123|Vollman   | 7150.00|
        165|Lee       | 7480.00|

SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
		(SELECT job_id
		 FROM employees
		 WHERE last_name = 'Lee')
		 AND salary > (SELECT salary
		 FROM employees
		 WHERE last_name = 'Lee');

last_name |job_id|salary  |
----------+------+--------+
Tucker    |SA_REP|11000.00|
Bernstein |SA_REP|10450.00|
Hall      |SA_REP| 9900.00|
Olsen     |SA_REP| 8800.00|

SELECT department_id, MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
					FROM employees
					WHERE department_id = 30
);

department_id|MIN(salary)|
-------------+-----------+
           10|    4840.00|
           20|    6600.00|
           40|    7150.00|

조건의 쌍비교 방식

• 컬럼들을 쌍으로 묶어서 쌍의 값들 모두를 한 번에 비교하는 방식
• 컬럼명은 상관 없으나 컬럼의 구성 순서는 동일하게 맞춰야 한다.
• department_id, salary를 동시에 비교하여 동일한 값의 쌍을 가진 행들만 결과로 출력된다.

주의! 리터럴(값)로 줄 때 데이터타입, 순서가 같아야함

SELECT employee_id, last_name, salary,
department_id
FROM employees
WHERE (department_id, salary) IN (
	SELECT department_id, MIN(salary)
	FROM employees
	GROUP BY department_id
);

employee_id|last_name |salary  |department_id|
-----------+----------+--------+-------------+
        101|Kochhar   |18700.00|           90|
        102|De Haan   |18700.00|           90|
        107|Lorentz   | 4620.00|           60|
        113|Popp      | 7590.00|          100|

• 리터럴로 줄 때

SELECT employee_id, last_name, salary,
department_id
FROM employees
WHERE (department_id, salary) = (110, 9130);

employee_id|last_name|salary |department_id|
-----------+---------+-------+-------------+
        206|Gietz    |9130.00|          110|