(2주차 9일) SQL 함수, 조인, 서브쿼리

MariaDB

> 조인(JOIN)과 UNION 연산자

• 외부 조인(OUTER JOIN)
  
  1. 조건에 만족되지 않는 행까지 조회하기 위해서 사용되는 조인
  2. LEFT JOIN / RIGHT JOIN: 왼쪽 / 오른쪽 테이블의 데이터를 모두 조회
     * where문에서는 NULL <> 1이 true로 평가되지 않으므로 where문의 결과값에서 필터링됨

-- LEFT JOIN / RIGHT JOIN: 왼쪽 / 오른쪽 테이블의 데이터는 모두 조회함
-- 'OUTER' 생략 가능
-- where문에서는 NULL <> 1이 true로 평가되지 않으므로 where문의 결과값에서 필터링됨
SELECT emp_id, b.dept_title, dept_code, salary
FROM employee a LEFT OUTER JOIN department b ON a.dept_code = b.dept_id
ORDER BY a.dept_code;

SELECT emp_id, b.dept_title, dept_code, salary
FROM employee a RIGHT JOIN department b ON a.dept_code = b.dept_id
ORDER BY a.dept_code;

• 상호 조인(CROSS JOIN): 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인

-- 데이터가 기하급수적으로 늘어나 과부하 위험이 있음
-- 23개 행(employee) * 9개 행(department) 조합 출력
SELECT emp_name, b.dept_title
FROM employee a CROSS JOIN department b
ORDER BY emp_name;

-- 11개 행(usertbl) * 12개 행(buytbl) 조합 출력
SELECT * FROM usertbl a CROSS JOIN buytbl b ORDER BY a.userID;

• 자체 조인(SELF JOIN): 동일한 테이블을 가지고 조인하여 데이터를 조회

-- 동일한 테이블으로 조인하여 테이블 내의 인덱스를 활용하여 새로운 데이터 생성
-- employee 테이블의 emp_id, emp_name의 정보를 활용하여 사수명(mananger_name) 생성
SELECT a.emp_id, a.emp_name, a.dept_code, /*a.manager_id,*/ 
		 b.emp_id AS manager_id, b.emp_name AS manager_name FROM employee a
/*INNER*/ LEFT JOIN employee b ON a.manager_id = b.emp_id;

• UNION: 두 쿼리의 결과를 하나로 결합 -> 중복된 열 삭제, 데이터 정렬하여 출력
  UNION ALL: 중복된 열까지 모두 출력

-- 열의 개수, 순서를 맞춰주어야 함
-- 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;

-- WHERE 절을 활용하여 동일한 처리 가능
SELECT emp_id, emp_name, dept_code, salary FROM employee
WHERE dept_code = 'D5' OR salary > 3000000;

-- UNION ALL: 중복된 열까지 모두 출력
SELECT emp_id, emp_name, dept_code, salary FROM employee
WHERE dept_code = 'D5'

UNION ALL

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

 

> JOIN 연습 문제

/*
* JOIN 연습문제
*/
-- 70년대생 이면서 여자이고, 성이 전 씨인 직원들의 
-- 직원명, 주민번호, 부서명, 직급명을 조회
SELECT emp_name, emp_no, b.dept_title, c.job_name
FROM employee a LEFT JOIN department b ON a.dept_code = b.dept_id
		LEFT JOIN job c ON a.job_code = c.job_code
WHERE LEFT(emp_no, 1) = 7 AND SUBSTRING(emp_no, 8, 1) = 2 AND emp_name LIKE '전%';

-- 각 부서별 평균 급여를 조회하여 부서명, 평균 급여를 조회
-- 단, 부서 배치가 안된 사원들의 평균도 같이 나오게 조회
SELECT IFNULL(b.dept_title, '부서없음') AS dept_title, 
	FLOOR(AVG(salary)) AS avg_salary
FROM employee a LEFT JOIN department b ON a.dept_code = b.dept_id
GROUP BY dept_code
ORDER BY avg_salary;

-- 각 부서별 총 급여의 합이 1000만원 이상인 부서명, 급여의 합을 조회
SELECT b.dept_title, SUM(salary)
FROM employee a LEFT JOIN department b ON a.dept_code = b.dept_id
GROUP BY dept_code
HAVING SUM(salary) >= 10000000;

-- 이름에 '형'자가 들어있는 직원들의 사번, 직원명, 직급명을 조회
SELECT emp_id, emp_name, b.job_name
FROM employee a LEFT JOIN job b ON a.job_code = b.job_code
WHERE emp_name LIKE '%형%';

-- 해외영업팀에 근무하는 직원들의 직원명, 직급명, 부서 코드, 부서명을 조회
SELECT emp_name, c.job_name, dept_code, b.dept_title
FROM employee a LEFT JOIN department b ON a.dept_code = b.dept_id
		LEFT JOIN job c ON a.job_code = c.job_code
WHERE dept_title LIKE '해외영업%';

 

> 서브쿼리

• 하나의 SQL문 내에 포함된 다른 SQL문을 서브쿼리라고 함

-- e.g. 노옹철 사원과 같은 부서인 사원을 조회
SELECT emp_name, dept_code FROM employee
WHERE dept_code = (SELECT dept_code FROM employee WHERE emp_name = '노옹철');

-- (1) 전 직원의 평균 급여 조회
-- (2) 평균 급여보다 더 많은 급여를 받고 있는 직원들을 조회
-- (3) 위의 2단계를 서브 쿼리를 사용하여 하나의 쿼리문으로 작성
SELECT ROUND(AVG(salary)) FROM employee;

SELECT emp_name FROM employee
WHERE salary > 3047663;

SELECT emp_name, salary FROM employee
WHERE salary > (SELECT ROUND(AVG(salary)) FROM employee);

• 서브쿼리 구분: 서브쿼리를 수행한 결과값의 행, 열의 개수에 따라 분류
  
  1. 단일행 서브쿼리: 서브쿼리 조회 결과 값의 개수가 1개인 경우
  2. 다중행 서브쿼리: 서브쿼리 조회 결과 값의 개수가 여러 행인 경우
  3. 다중열 서브쿼리: 서브쿼리 조회 결과 값은 1개이지만, 여러 열로 나타나는 경우
     * 다중열의 경우, 쌍비교 활용

-- #단일행 서브쿼리
-- 노옹철 사원보다 급여가 많은 사원의 사번, 이름, 직급명, 부서명, 급여 조회
SELECT emp_id, emp_name, c.job_name, b.dept_title, salary
FROM employee a LEFT JOIN department b ON a.dept_code = b.dept_id
					 LEFT JOIN job c ON a.job_code = c.job_code
WHERE salary > (SELECT salary FROM employee WHERE emp_name = '노옹철');

-- #다중행 서브쿼리
-- 각 부서별 최고 급여를 받는 직원의 이름, 직급 코드, 부서 코드, 급여 조회
SELECT emp_name, job_code, dept_code, salary FROM employee
WHERE salary IN (SELECT MAX(salary) FROM employee GROUP BY dept_code);

-- #다중열 서브쿼리
-- 하이유 사원과 같은 부서 코드, 같은 직급 코드에 해당하는 사원 조회
SELECT emp_name, job_code, dept_code FROM employee
WHERE (job_code, dept_code) -- 쌍비교
	 	= (SELECT job_code, dept_code FROM employee WHERE emp_name = '하이유');
	 	
-- 쌍비교 옵션
SELECT emp_name, job_code, dept_code FROM employee
WHERE (job_code, dept_code) IN (('J5', 'D5'), ('J3', 'D4'));

• 서브쿼리 옵션(연산자): ANY, ALL

-- ANY: 서브쿼리 결과 중 하나라도 조건을 만족하면 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');
		
-- 과장 직급임에도 차장 직급의 최대 급여보다 많이 받는 직원의 사번, 이름, 직급, 급여 조회
-- ALL: 서브쿼리 결과 모두 조건을 만족하면 true를 반환		
SELECT emp_id, emp_name, b.job_name, salary FROM employee a
INNER JOIN job b ON a.job_code = b.job_code
WHERE b.job_name = '과장' AND 
	salary > ALL (SELECT salary FROM employee WHERE job_code = 'J4');

• 위치에 따른 서브쿼리: SELECT, FROM, WHERE, HAVING, ORDER BY절에 사용할 수 있다.

-- #FROM절에 사용되는 서브쿼리: 인라인 뷰
-- 서브쿼리 수행 결과를 테이블 대신 사용
SELECT * FROM (SELECT emp_id, emp_name, salary, salary * 12 AS 연봉 FROM employee) e;

-- employee 테이블에서 급여로 순위를 매겨 출력
SELECT emp_name, salary
FROM (SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rankno, emp_name, salary 
	FROM employee LIMIT 10) e
WHERE rankno BETWEEN 5 AND 10;

-- 각 부서별 최고 급여를 받는 직원의 이름, 직급 코드, 부서 코드, 급여 조회
SELECT emp_name, job_code, dept_code, salary 
FROM (SELECT emp_name, job_code, dept_code, salary, 
	ROW_NUMBER() OVER(PARTITION BY dept_code ORDER BY salary DESC) AS rankno FROM employee) AS T
WHERE rankno = 1;

-- #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 alias
FROM employee;

-- #WHERE절에 사용되는 서브쿼리
-- 대리 직급임에도 과장 직급의 최소 급여보다 많이 받는 직원의 사번, 이름, 직급, 급여 조회
SELECT emp_id, emp_name, job_code, salary FROM employee
WHERE job_code = 'J6' AND 
	salary > (SELECT MIN(salary) FROM employee GROUP BY job_code HAVING job_code = 'J5');

 

> 서브쿼리 연습문제

-- #다중행, 다중열 연습 문제(쌍비교)
-- 박나라 사원과 같은 직급 코드, 같은 사수에 해당하는 사원 조회
SELECT emp_id, emp_name, job_code, manager_id FROM employee
WHERE (job_code, manager_id) 
		= (SELECT job_code, manager_id FROM employee WHERE emp_name = '박나라');
		
-- 각 부서별 최고 급여를 받는 직원의 사번, 이름, 부서 코드, 급여 조회
SELECT emp_id, emp_name, IFNULL(dept_code, '부서없음') AS dept_code, salary FROM employee
WHERE (IFNULL(dept_code, '부서없음'), salary) IN 
	(SELECT IFNULL(dept_code, '부서없음'), 
	MAX(salary) AS salary FROM employee GROUP BY dept_code)
ORDER BY dept_code;
		
-- 각 부서별 최소 급여를 받는 직원의 사번, 이름, 부서 코드, 급여 조회
SELECT emp_id, emp_name, IFNULL(dept_code, '부서없음') AS dept_code, salary FROM employee
WHERE (IFNULL(dept_code, '부서없음'), salary) IN 
	(SELECT IFNULL(dept_code, '부서없음'), 
	MIN(salary) AS salary FROM employee GROUP BY dept_code)
ORDER BY dept_code;

-- 각 직급별 최소 급여를 받는 직원의 사번, 이름, 직급 코드, 급여 조회
SELECT emp_id, emp_name, job_code, salary FROM employee
WHERE (job_code, salary) IN 
	(SELECT job_code, MIN(salary) AS salary FROM employee GROUP BY job_code)
ORDER BY job_code;