지난 포스팅에서 SQL 집계 함수와 정렬에 대해서 다뤄봤습니다.
[Database] SQL GROUP BY | ORDER BY | HAVING | ROLLUP | CUBE | UNION ALL | SQL 집합 연산자 (java로 웹개발하기)(day23)
지난 포스팅에서 SQL 함수에 대해서 다뤄봤습니다. https://healthdevelop.tistory.com/entry/db4 [Database] SQL 문자열 함수 | SQL 문자열 길이, 문자 제거, 대문자 변환, 문자열 붙이기, 문자열 변환 | 지난..
healthdevelop.tistory.com
이번 시간에는 SQL 조인문(JOIN)에 대해서 다뤄보고자 합니다.
▶ JOIN
JOIN이란,,
하나 이상의 테이블에서 데이터를 조회하기 위해 사용하고
수행 결과는 하나의 Result Set으로 나오는 구문입니다.
보통의 경우 두 개의 테이블에서 공통된 부분을 찾아
두 테이블을 연관시키기 위해 사용되곤 합니다.
위 테이블의 정의와 표시를 보면
두 테이블은 DEPT_CODE와 DEPT_ID로 연결할 수 있겠죠.
JOIN은 두 가지 전용구문으로 쓰일 수 있는데요.
바로 예시를 통해 알아보겠습니다.
1. 오라클 전용 구문
대표적으로 쓰이는 구문입니다.
위에 보인느 것처럼 테이블 명을 기술하고, where절에 합칠 칼럼을 명시합니다.
2. ANSI 표준 구문
ANSI 표준구문에서
연관짓고자 하는 칼럼명이 같을 경우,
USING절을 이용합니다.
▶ INNER JOIN과 OUTER JOIN
기본적으로 JOIN은 INNER JOIN이며,
두 개 이상의 테이블을 조인할 때 일치하는 값이 없는 행은 조인에서 제외됩니다.
OUTER JOIN은 일치하지 않은 값도 포함이 되며,
반드시 OUTER JOIN 명시해야 합니다
예시는 아래와 같습니다.
LEFT 조인이 생소하실텐데,
쉽게 설명하자면
왼쪽 칼럼의 테이블을 기준으로 오른쪽 테이블과 대응해봤을 때,
대응되는 것이 있다면 조회를 해주고,
추가로 왼쪽 테이블에는 값이 있지만, 오른쪽 테이블엔 해당 칼럼에 대응되는 값이 없을 때에도
조회를 해줍니다.(대신 오른쪽 칼럼에는 NULL)
OUTER JOIN의 종류는 세가지가 있습니다(OUTER는 생략가능)
FULL OUTER JOIN은
LEFT 와 RIGHT JOIN을 합친 결과와 같습니다.
▶ CROSS JOIN
CROSS JOIN이란,
카테시안 곱(Cartesian Product)라고도 하며
조인되는 테이블의 각 행들이 모두 매핑된 데이터가 검색되는 조인 방법입니다.
검색되는 데이터 수는 ‘행의 컬럼 수 * 또 다른 행의 컬럼 수‘로 나옵니다.
예시는 아래와 같습니다.
JOIN한 두 테이블에 모든 조합을 나타내죠.
▶ NON_EQU JOIN
NON_EQU JOIN이란,
지정한 컬럼 값이 일치하는 경우가 아닌 값의 범위에 포함되는 행들을
연결하는 방식입니다.
예시는 아래와 같습니다.
(=) 등호 대신 > , < , <=, >= 연산을 조건으로 한 것이죠.
▶ SELF JOIN
SELF JOIN이란,
두 개 이상의 서로 다른 테이블을 연결하는 것이 아닌
같은 테이블을 조인하는 것입니다.
예시는 아래와 같습니다.
▶ 다중 JOIN
가끔 두개 뿐만 아니라 3개, 혹은 그 이상의 수로 테이블을 조인해야할 상황이 있는데요.
그럴때는 아래와 같이 하면 됩니다.
이렇게 JOIN문을 두 번 사용해주면 되는 것이죠.
그럼 이제 조인문을 사용한 SQL 예제를 풀어봅시다.
SQL(JOIN) 예제
※ 모든 SQL 예제는 테이블을 생성하지 않고 문제만 봐도 풀 수 있습니다!
모든 테이블은 ID로 구분하고 ID로 조인합니다.
(EX- EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID)
난이도 ☆
예제 1
Q : 1. 매니저가 존재하는 부서의 사원들을 조회
(DEPARTMENTS, EMPLOYEES)
※ EMPLOYEE는 DEPARTMENTS에 속함
풀이 :
SELECT E.EMPLOYEE_ID,
D.DEPARTMENT_NAME,
D.MANAGER_ID,
E.FIRST_NAME || ' ' || E.LAST_NAME AS "NAME"
FROM DEPARTMENTS D JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE D.MANAGER_ID IS NOT NULL
ORDER BY 1;
난이도 ☆
예제 2
Q : 유럽, 아메리카, 아시아, 중앙아시아/아프리카 지역(REGIONS)에 있는 부서의 수를 조회
(REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS)
※ REGIONS에 COUNTRIES가 속함
COUNTRIES에 LOCATIONS가 속함
LOCATIONS에 DEPARTMENTS가 속함
풀이 :
SELECT R.REGION_NAME,COUNT(DEPARTMENT_ID)
FROM REGIONS R JOIN COUNTRIES C
ON R.REGION_ID = C.REGION_ID
JOIN LOCATIONS L
ON C.COUNTRY_ID = L.COUNTRY_ID
JOIN DEPARTMENTS D
ON L.LOCATION_ID = D.LOCATION_ID
GROUP BY R.REGION_NAME;
난이도 ☆
예제 3
Q : 모든 부서의 위치(주소)를 조회
-- (REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS)
※ REGIONS에 COUNTRIES가 속함
COUNTRIES에 LOCATIONS가 속함
LOCATIONS에 DEPARTMENTS가 속함
풀이 :
SELECT D.DEPARTMENT_NAME, L.STREET_ADDRESS
FROM REGIONS R JOIN COUNTRIES C
ON R.REGION_ID = C.REGION_ID
JOIN LOCATIONS L
ON C.COUNTRY_ID = L.COUNTRY_ID
JOIN DEPARTMENTS D
ON L.LOCATION_ID = D.LOCATION_ID;
난이도 ☆
예제 4
Q : 아메리카 지역에 해당하는 부서와 소속 사원들을 조회
(REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS, EMPLOYEES)
※ REGIONS에 COUNTRIES가 속함
COUNTRIES에 LOCATIONS가 속함
LOCATIONS에 DEPARTMENTS가 속함
DEPARTMENTS에 EMPLOYEES가 속함
풀이 :
SELECT D.DEPARTMENT_NAME, E.FIRST_NAME || ' ' || E.LAST_NAME AS "NAME"
FROM REGIONS R JOIN COUNTRIES C
ON R.REGION_ID = C.REGION_ID
JOIN LOCATIONS L
ON C.COUNTRY_ID = L.COUNTRY_ID
JOIN DEPARTMENTS D
ON L.LOCATION_ID = D.LOCATION_ID
JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
WHERE R.REGION_ID = 3;
난이도 ☆
예제 5
Q : 소속 지역별 평균급여 랭크 조회(지역은 대륙단위로 묶어서 한다.)
-- (REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS, EMPLOYEES)
※ REGIONS에 COUNTRIES가 속함
COUNTRIES에 LOCATIONS가 속함
LOCATIONS에 DEPARTMENTS가 속함
DEPARTMENTS에 EMPLOYEES가 속함
풀이 :
SELECT R.REGION_NAME, AVG(E.SALARY)
FROM REGIONS R JOIN COUNTRIES C
ON R.REGION_ID = C.REGION_ID
JOIN LOCATIONS L
ON C.COUNTRY_ID = L.COUNTRY_ID
JOIN DEPARTMENTS D
ON L.LOCATION_ID = D.LOCATION_ID
JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY REGION_NAME;
난이도 ☆
예제 6
Q : 국가별 평균급여 랭크 조회
-- (COUNTRIES, LOCATIONS, DEPARTMENTS, EMPLOYEES)
※ COUNTRIES에 LOCATIONS가 속함
LOCATIONS에 DEPARTMENTS가 속함
DEPARTMENTS에 EMPLOYEES가 속함
풀이 :
SELECT COUNTRY_NAME, AVG(SALARY),RANK() OVER(ORDER BY AVG(SALARY) DESC) AS "RANK"
FROM COUNTRIES C JOIN LOCATIONS L
ON C.COUNTRY_ID = L.COUNTRY_ID
JOIN DEPARTMENTS D
ON L.LOCATION_ID = D.LOCATION_ID
JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY COUNTRY_NAME;
난이도 ☆
예제 7
Q : 국가별/부서별 평균급여 조회(전체 평균 및 국가 단위 평균, 부서 단위 평균의 통계가 필요)
(COUNTRIES, LOCATIONS, DEPARTMENTS, EMPLOYEES)
※ COUNTRIES에 LOCATIONS가 속함
LOCATIONS에 DEPARTMENTS가 속함
DEPARTMENTS에 EMPLOYEES가 속함
풀이 :
SELECT C.COUNTRY_NAME, D.DEPARTMENT_NAME, AVG(SALARY)
FROM COUNTRIES C JOIN LOCATIONS L
ON C.COUNTRY_ID = L.COUNTRY_ID
JOIN DEPARTMENTS D
ON L.LOCATION_ID = D.LOCATION_ID
JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY CUBE(C.COUNTRY_NAME, D.DEPARTMENT_NAME);
여기까지 SQL JOIN에 대해서 알아봤습니다.
다음 포스팅에서는 서브쿼리(SUBQUERY)에 대해서 다뤄보고자 합니다.
