22.02.04 SQL 심화 공부 (JOIN)

JOIN 교집합 : 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것

  연산자에 따라 두 가지 방식이 있다.(EQUI JOIN, Non EQUI JOIN)

 

EQUI JOIN 등가교집합 : 두 개의 테이블간에 서로 정확하게 일치하는 경우를 활용하는 조인, = 를 사용하는 조인

  대부분 기본키-외래키 관계를 기반으로 발생,(모든 조인이 그런건 아니다) ... 실무에선 이걸 많이 쓴다

 

Non EQUI JOIN 비등가 교집합: 두 개의 테이블간에 서로 정확하게 일치하지 않는 경우를 활용하는 조인(등가연산자 = 을 제외한 연산자들을 사용한 조인 > >= < <= BETWEEN 사용

 

FROM절 JOIN형태

1. INNER JOIN : JOIN 조건에서 동일한 값이 있는 행만 반환, JOIN의 default값으로 INNER는 생략가능하다

유저 테이블과 교실 테이블을 JOIN하는 예시

INNER JOIN : ON 조건절을 화룡ㅇ하면 컬럼명이 달라도 JOIN조건을 사용가능하다

employee 테이블과 포지션 테이블을 동일한 컬럼인 position_id 기준으로 INNER JOIN하기

-- 아래 쿼리는 EMPLOYEE, POSITION_T 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC EMPLOYEE;
DESC POSITION_T;

SELECT * FROM EMPLOYEE;
SELECT * FROM POSITION_T;

-- EMPLOYEE 테이블과 POSITION_T 테이블에 대해 POSITION_ID가 같은 데이터에 대해
-- 사원번호(EMPLOYEE_ID)와 이름(NAME), 직급명칭(POSITION_NAME)을 조회하는 쿼리를 작성해보세요.
SELECT EMPLOYEE_ID, NAME, POSITION_NAME FROM EMPLOYEE a INNER JOIN POSITION_T b ON a.POSITION_ID = b.POSITION_ID ORDER BY EMPLOYEE_ID;

DESC 결과

EMPLOYEE

POSITION_T

JOIN한 테이블

가게 테이블과 가게타입코드 테이블 INNER JOIN하기

-- 아래 쿼리는 STORE, STORE_TYPE_CODE 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC STORE;
DESC STORE_TYPE_CODE;

SELECT * FROM STORE;
SELECT * FROM STORE_TYPE_CODE;

-- STORE 테이블과 STORE_TYPE_CODE 테이블에 대해 store_type_code 컬럼을 이용하여 조인을 실시해,
-- 가게이름(store_name)과 타입이름(store_type_name)을 조회하는 쿼리를 작성해보세요.
SELECT store_name, store_type_name FROM STORE a JOIN STORE_TYPE_CODE b ON a.store_type_code = b.store_type_code ORDER BY store_id;

1-1. JOIN의 USING 조건절

USING 조건절 : 같은 이름을 가진 컬럼 중 원하는 컬럼에 대해 선택적으로 등가 조인 가능(Oracle만 가능하고 SQL Server에서는 못 씀.)

USING 조건절에는 a,b같은 별칭을 붙일 수 없다고 한다.

2. NATURAL JOIN : 두 테이블 간의 동일한 이름을 갖는 모든 컬럼에 대해 등가조인을 수행.. 모든 컬럼을 JOIN하기때문에 조건절을 넣을 수 없음. ...많이 사용하진 않는다.

유저 테이블과 수업 테이블의 NATURAL JOIN 예시

수강신청테이블과 학생테이블, 수업테이블을 이용해 JOIN 두번 쓰기 및 USING 써보기

-- 아래 쿼리는 CLASS_REQUEST, STUDENT, CLASS 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC CLASS_REQUEST;
DESC STUDENT;
DESC CLASS;

SELECT * FROM CLASS_REQUEST;
SELECT * FROM STUDENT;
SELECT * FROM CLASS;
-- 1. CLASS_REQUEST 테이블에 대해 JOIN절과 USING을 이용해 STUDENT테이블과 결합하여
-- 신청번호(REQUEST_ID)와 학생이름(STUDENT_NAME)을 조회하는 쿼리를 작성해보세요.
SELECT REQUEST_ID, STUDENT_NAME FROM STUDENT JOIN CLASS_REQUEST USING (STUDENT_ID) ORDER BY REQUEST_ID;

-- 2. CLASS_REQUEST 테이블에 대해 JOIN절과 USING을 이용해 CLASS테이블과 결합하여
-- 신청번호(REQUEST_ID)와 과목이름(CLASS_NAME)을 조회하는 쿼리를 작성해보세요.
SELECT REQUEST_ID, CLASS_NAME FROM CLASS a JOIN CLASS_REQUEST b ON a.CLASS_ID = b.CLASS_ID ORDER BY REQUEST_ID;

-- 3. CLASS_REQUEST 테이블에 대해 JOIN절과 USING을 이용해 STUDENT테이블, CLASS테이블과 결합하여
-- 신청번호(REQUEST_ID)와 학생이름(STUDENT_NAME), 과목이름(CLASS_NAME)을 조회하는 쿼리를 작성해보세요.
SELECT REQUEST_ID, STUDENT_NAME, CLASS_NAME FROM CLASS_REQUEST INNER JOIN STUDENT USING (STUDENT_ID) INNER JOIN CLASS USING (CLASS_ID) ORDER BY REQUEST_ID;

JOIN 결과들

로그인기록 테이블과 유저테이블 NATURAL JOIN하기

-- 아래 쿼리는 LOGIN_HISTORY, MEMBER 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC LOGIN_HISTORY;
DESC MEMBER;

SELECT * FROM LOGIN_HISTORY;
SELECT * FROM MEMBER;
-- LOGIN_HISTORY테이블과 MEMBER테이블에 대해 NATURAL JOIN을 이용하여 조회하고자 하는 컬럼을 출력해보세요.
SELECT * FROM LOGIN_HISTORY NATURAL JOIN MEMBER;

3. CROSS JOIN

CROSS JOIN : JOIN 조건이 없는 경우, 생길 수 있는 모든 데이터의 조합을 조회.. CROSS도 생략 가능하다. 다만 INNER과 다른 점은 INNER는 ON이나 USING이 필요함..?! 없으면 CROSS인듯

사람테이블과 교통수단테이블 CROSS JOIN 쿼리 

성테이블과 이름테이블 CROSS JOIN

-- 아래 쿼리는 FIRST_NAME_T, LAST_NAME_T 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC FIRST_NAME_T;
DESC LAST_NAME_T;

SELECT * FROM FIRST_NAME_T;
SELECT * FROM LAST_NAME_T;
-- FIRST_NAME_T와 LAST_NAME_T를 크로스조인으로 결합하고, first_name과 last_name을 출력하세요.
SELECT first_name, last_name FROM FIRST_NAME_T CROSS JOIN LAST_NAME_T ORDER BY first_name, last_name;

4. OUTER JOIN : 두 개의 테이블 간의 교집합을 조회하고, 한쪽테이블에만 있는 데이터도 포함시켜 조회.

빈 곳은 NULL 값으로 출력한다. WHERE 조건절에서 한쪽에만 있는 데이터를 포함시킬 테이블 쪽으로 (+)를 위치한다.

유저테이블과 클래스테이블 OUTER JOIN 쿼리문

그냥 OUTER만 쓰면 WHERE의 (+)가 있는 쪽만 모든 값이 표현됨, 없는 쪽은 NULL로... 지금 이 경우는 LEFT JOIN

표준 OUTER JOIN (LEFT JOIN)

그냥 JOIN이었다면 값이 없는 1003는 출력이 안 된다.

표준 OUTER JOIN (RIGHT JOIN)

지금은 RIGHT이므로 CLASS 테이블에 있는 모든 값은 다 표현되어야 한다.

테이블의 위치만 바꿔주면 RIGHT JOIN값이랑 LEFT JOIN값이랑 같아질 듯? 어차피 같은 값이다.

 

표준 OUTER JOIN (FULL OUTER JOIN) ... Oracle 버전

 

표준 OUTER JOIN (FULL OUTER JOIN) ... SQL 버전 .. UNION, LEFT OUTER, RIGHT OUTER 사용

INNER vs OUTER vs CROSS JOIN 

INNER JOIN : 2개 USING , ON , NATURAL 조건 필수

CROSS JOIN : 12개 모든경우조합 4x3 USING , ON , NATURAL 조건X

LEFT OUTER JOIN : 4개 왼쪽테이블은 다살린다 오른쪽 빈값은 NULL ON조건 필수

RIGHT OUTER JOIN : 3개 오른쪽테이블은 다살린다 왼쪽 빈값은 NULL ON조건 필수

FULL OUTER JOIN : 5개 둘다 살리고 빈값은 NULL, 중복된 것은 UNION에서 제거됨

 

멤버테이블과 멤버이메일테이블 LEFT OUTER JOIN

-- 아래 쿼리는 MEMBER, MEMBER_EMAIL 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC MEMBER;
DESC MEMBER_EMAIL;

SELECT * FROM MEMBER;
SELECT * FROM MEMBER_EMAIL;
-- MEMBER테이블과 MEMBER_EMAIL테이블에 대해 LEFT OUTER JOIN을 사용하여
-- member_name과 email을 결합하여 출력하세요.
SELECT member_name, email FROM MEMBER LEFT OUTER JOIN MEMBER_EMAIL ON MEMBER.member_id = MEMBER_EMAIL.member_id ORDER BY member_name;

설문조사요청테이블과 응답테이블 RIGHT OUTER JOIN

-- 아래 쿼리는 RESPONSE_DTL, REQUEST 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC RESPONSE_DTL;
DESC REQUEST;

SELECT * FROM RESPONSE_DTL;
SELECT * FROM REQUEST;

-- RESPONSE_DTL테이블을 기준으로 REQUEST테이블과 RIGHT OUTER JOIN을 실시하는 쿼리를 작성해보세요.
SELECT request_id, request_member_id, response_content FROM RESPONSE_DTL RIGHT OUTER JOIN REQUEST ON RESPONSE_DTL.response_id = REQUEST.response_id ORDER BY request_id;

프론트 git 테이블과 백엔드 git 테이블의 FULL OUTER JOIN .... ERROR 1052 (23000) at line 8: Column 'version_id' in field list is ambiguous 발생 + 해결

 

Error 코드

-- 아래 쿼리는 FRONT_VERSION_HIST, BACK_VERSION_HIST 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC FRONT_VERSION_HIST;
DESC BACK_VERSION_HIST;

SELECT * FROM FRONT_VERSION_HIST;
SELECT * FROM BACK_VERSION_HIST;
-- 1. FRONT_VERSION_HIST 테이블을 기준으로 BACK_VERSION_HIST 테이블과 버전ID로 LEFT OUTER JOIN을 해주세요.
SELECT version_id, version_content_front, version_content_back FROM FRONT_VERSION_HIST LEFT OUTER JOIN BACK_VERSION_HIST ON FRONT_VERSION_HIST.version_id = BACK_VERSION_HIST.version_id ORDER BY version_id ASC;

-- 2. FRONT_VERSION_HIST 테이블을 기준으로 BACK_VERSION_HIST 테이블과 버전ID로 RIGHT OUTER JOIN을 해주세요.
SELECT version_id, version_content_front, version_content_back FROM FRONT_VERSION_HIST RIGHT OUTER JOIN BACK_VERSION_HIST ON FRONT_VERSION_HIST.version_id = BACK_VERSION_HIST.version_id ORDER BY version_id;

-- 3. 위에서 작성한 두 쿼리에서 ORDER BY를 제거 후, 중복을 제거하는 집합연산자로 결합하고 최종 결과에 대해 정렬을 해주세요.
SELECT version_id, version_content_front, version_content_back FROM FRONT_VERSION_HIST LEFT OUTER JOIN BACK_VERSION_HIST ON FRONT_VERSION_HIST.version_id = BACK_VERSION_HIST.version_id 
UNION 
SELECT version_id, version_content_front, version_content_back FROM FRONT_VERSION_HIST RIGHT OUTER JOIN BACK_VERSION_HIST ON FRONT_VERSION_HIST.version_id = BACK_VERSION_HIST.version_id
ORDER BY version_id;

ON절 뿐 아니라 SELECT에도 어디 테이블의 어떤 컬럼인지 자세히 명시해야함. (a.version_id)

해결코드

-- 아래 쿼리는 FRONT_VERSION_HIST, BACK_VERSION_HIST 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC FRONT_VERSION_HIST;
DESC BACK_VERSION_HIST;

SELECT * FROM FRONT_VERSION_HIST;
SELECT * FROM BACK_VERSION_HIST;
-- 1. FRONT_VERSION_HIST 테이블을 기준으로 BACK_VERSION_HIST 테이블과 버전ID로 LEFT OUTER JOIN을 해주세요.
SELECT FRONT_VERSION_HIST.version_id, version_content_front, version_content_back FROM FRONT_VERSION_HIST LEFT OUTER JOIN BACK_VERSION_HIST ON FRONT_VERSION_HIST.version_id = BACK_VERSION_HIST.version_id ORDER BY version_id ASC;

-- 2. FRONT_VERSION_HIST 테이블을 기준으로 BACK_VERSION_HIST 테이블과 버전ID로 RIGHT OUTER JOIN을 해주세요.
SELECT BACK_VERSION_HIST.version_id, version_content_front, version_content_back FROM FRONT_VERSION_HIST RIGHT OUTER JOIN BACK_VERSION_HIST ON FRONT_VERSION_HIST.version_id = BACK_VERSION_HIST.version_id ORDER BY version_id;

-- 3. 위에서 작성한 두 쿼리에서 ORDER BY를 제거 후, 중복을 제거하는 집합연산자로 결합하고 최종 결과에 대해 정렬을 해주세요.
SELECT FRONT_VERSION_HIST.version_id, version_content_front, version_content_back FROM FRONT_VERSION_HIST LEFT OUTER JOIN BACK_VERSION_HIST ON FRONT_VERSION_HIST.version_id = BACK_VERSION_HIST.version_id 
UNION 
SELECT BACK_VERSION_HIST.version_id, version_content_front, version_content_back FROM FRONT_VERSION_HIST RIGHT OUTER JOIN BACK_VERSION_HIST ON FRONT_VERSION_HIST.version_id = BACK_VERSION_HIST.version_id
ORDER BY version_id;

 

 

JOIN과 WHERE 같이 사용하기! ( INNER, USING, NATURAL ,CROSS, LEFT,RIGHT OUTER)

  JOIN과 WHERE는 별개이다

INNER JOIN + WHERE문 쿼리 예시

유저요청히스토리테이블과 유저테이블 INNER JOIN과 WHERE절 함꼐 사용하기

-- 아래 쿼리는 REQUEST_HIST, MEMBER 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC REQUEST_HIST;
DESC MEMBER;

SELECT * FROM REQUEST_HIST;
SELECT * FROM MEMBER;
-- REQUEST_HIST 테이블과 MEMBER 테이블을 INNER JOIN하여 조회하되, 요청상태(req_status)가 fail인 정보만 조회하는 쿼리를 작성하세요.
SELECT request_id, req_status, member_name FROM REQUEST_HIST INNER JOIN MEMBER ON REQUEST_HIST.req_member_id = MEMBER.member_id WHERE req_status='fail' ORDER BY request_id;

아이폰에서만 실행가능한 앱, 앱테이블과 앱DTL테이블 JOIN + WHERE

-- 아래 쿼리는 APPLICATION, APPLICATION_DTL 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC APPLICATION;
DESC APPLICATION_DTL;

SELECT * FROM APPLICATION;
SELECT * FROM APPLICATION_DTL;

-- APPLICATION 테이블과 APPLICATION_DTL 테이블에 대해 app_id를 기준으로 INNER JOIN을 실시하되
-- app_device가 iphone 또는 all인 데이터에 대해서 조회하는 쿼리를 작성하세요.
SELECT APPLICATION.app_id, app_name, app_content, app_device FROM APPLICATION INNER JOIN APPLICATION_DTL 
ON APPLICATION.app_id = APPLICATION_DTL.app_id 
WHERE (app_device='iphone') OR (app_device='all') ORDER BY app_id;

비행기테이블과 비행기선로테이블 LEFT OUTER JOIN + Korea에서 출발하는 비행기찾기

-- 아래 쿼리는 AIR_ROUTE, AIRPLANE 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC AIR_ROUTE;
DESC AIRPLANE;

SELECT * FROM AIR_ROUTE;
SELECT * FROM AIRPLANE;

-- AIR_ROUTE테이블과 AIRPLANE테이블에 대해 route_id가 동일하고 
SELECT route_from, route_to, airplane_id FROM AIR_ROUTE 
LEFT OUTER JOIN AIRPLANE 
ON AIR_ROUTE.route_id = AIRPLANE.route_id;

-- 경로가 Korea로 시작하는 데이터를 조회하는 쿼리를 작성하세요.
SELECT route_from, route_to, airplane_id FROM AIR_ROUTE 
LEFT OUTER JOIN AIRPLANE 
ON AIR_ROUTE.route_id = AIRPLANE.route_id
WHERE route_from='Korea'
ORDER BY airplane_id;

셀프 조인 : 동일 테이블 사이의 조인 ( 스스로 조인? )

  동일 테이블 사이의 조인을 수행하면 테이블과 컬럼 이름이 모두 동일하기 때문에 식별을 위해 별칭 필수

 

셀프 JOIN 쿼리 예시

계층형 질의에서 셀프 LEFT OUTER JOIN하기 쿼리예시

BETA.관리자를 시각화(SELECT)할떄는 차상위란 별칭으로 보여주기

-- 아래 쿼리는 EMPLOYEE 테이블의 구조를 나타냅니다. 해당 테이블을 수정할 필요는 없습니다.
DESC EMPLOYEE;

SELECT * FROM EMPLOYEE;
SELECt * FROM EMPLOYEE;
-- EMPLOYEE 테이블을 SELF JOIN하여 사원ID, 사원이름, 관리자이름을 조회하는 쿼리를 작성해보세요.
--WHERE절 없이 하면 그냥 생략된 CROSS JOIN 된듯?
SELECT a.employee_id, a.employee_name, b.employee_name my_manager_name
FROM EMPLOYEE a, EMPLOYEE b
ORDER BY employee_id;


--WHERE문을 넣어서 INNER JOIN?으로 사원 = 부모사원(관리자)
SELECT a.employee_id, a.employee_name, b.employee_name my_manager_name
FROM EMPLOYEE a, EMPLOYEE b
WHERE a.manager_id = b.employee_id
ORDER BY employee_id;