[SQLD] 2과목 SQL 기본 및 활용 - 조인, 집합 연산자

박진우·2022년 6월 3일
0

SQLD

목록 보기
12/21

💡 조인(JOIN)

조인의 개요...

여러 테이블(릴레이션)을 연결 또는 결합하여 데이터(릴레이션)을 만드는 과정이며, 일반적으로 PK나 FK의 연관성에 의해 성립한다.


◽ EQUI JOIN

  • 2개의 테이블 간에 일치 하는 것을 조인한다.

  • WHERE 절에서"=" 로 2개 테이블을 연결한다.

  • 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법

  • 대부분 PK ↔ FK의 관계를 기반으로 한다. But 반드시 PK ↔ FK의 관계로만 EQUI JOIN 이 성립하는 것은 아니다.

  • SQL>>

 SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명 FROM PLAYER, TEAM 
 WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID; 
 
                               ↆ 같은 코드
 
 SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명 FROM PLAYER 
 INNER JOIN TEAM ON PLAYER.TEAM_ID = TEAM.TEAM_ID;

선수 테이블과 팀 테이블에서 선수 이름과 소속된 팀의 이름을 출력

  • ✔ 조인문에 추가조건, 정렬 가능
  • ✔ AND로 조건 추가, ORDER BY로 정렬
SELECT * FROM STUDENT, DEPT

WHERE STUDENT.DEPTNO = DEPT.DEPTNO

AND STUDENT.STDNAME LIKE "박%"

ORDER BY STDNAME;

◽ Non EQUI JOIN

  • 2개 테이블 간에 정확하게 일치하지 않는 것을 조인

  • Non EQUI JOIN의 경우에는 “=” 연산자가 아닌 다른(Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN을 수행한다.

  • SQL>>

SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명, 

 S.STADIUM_NAME 구장명 FROM PLAYER P, TEAM T, STADIUM S 
 
 WHERE P.TEAM_ID = T.TEAM_ID AND T.STADIUM_ID = S.STADIUM_ID ORDER BY 선수명; 
 
                               ↆ 같은 코드
                               
                               
 SELECT PLAYER.PLAYER_
 
 SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명, 
 
 S.STADIUM_NAME 구장명 FROM PLAYER P INNER JOIN TEAM T ON P.TEAM_ID = T.TEAM_ID 
 
 INNER JOIN STADIUM S ON T.STADIUM_ID = S.STADIUM_ID ORDER BY 선수명;

선수들 별로 홈그라운드 경기장이 어디인지를 출력하고 싶다고 했을 때, 선수 테이블과 운동장 테이블이 서로 관계가 없으므로 중간에 팀 테이블이라는 서로 연관관계가 있는 테이블을 추가해서 세 개의 테이블을 JOIN 해야만 원하는 데이터를 얻을 수 있다.



◽ INNER JOIN | 내부 조인

‘내부 JOIN’ 이라고한다. JOIN 조건에서 동일한 값이 있는 행만 반환

  • "ON"문을 사용하여 테이블을 연결한다.

  • INNER JOIN구에서 2개의 테이블 이름 서술한다.

  • USING 조건절이나 ON조건절을 필수적으로 사용해야 한다.

  • DEFAULT옵션이므로 생략이 가능하지만, CROSS JOIN, OUTER JOIN과는 같이 사용할 수 없다.

  • SQL>>
 SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP, DEPT 
   
 WHERE EMP.DEPTNO = DEPT.DEPTNO; 
   
                                ↆ 같은 코드
 SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP 
   
 INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; 
   
                                ↆ 같은 코드 
        (INNER JOIN을 JOIN으로 써도 상관 없다. 디폴트값이 INNER JOIN) 
   
 SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP 
   
 JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; 

사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 출력



◽ NATURAL JOIN | 자연 조인

자연 조인은 등가 조인하는 방법 중 하나이며, 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 조인을 간단히 표현하는 방법이다.

  • 두테이블간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=)JOIN을 수행한다.

  • 추가로 USING조건절, ON조건절, WHERE절에서 JOIN조건을 정의할 수 없다.

  • JOIN에 사용된 칼럼들은 같은 데이터 유형이어야 한다.

  • ALIAS나 테이블명과 같은 접두사를 붙일 수 없다.

  • SQL>>
SELECT DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT; 

사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 출력



◽ USING 조건절

두 개의 테이블이 내부 조인으로 조인 될 때 조인하고자 하는 두 테이블의 컬럼명이 같을 경우 조인 조건을 길게 적지 않고 간단하게 적을 수 있도록 하는 역할

  • 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 =JOIN을 할 수 있다.

  • SQL Server에서는 지원하지 않는다.

  • JOIN칼럼에 대해서는 ALIAS나 테이블이름과 같은 접두사를 붙일 수 없다.

  • Oracle SQL>>
SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO); 


◽ ON 조건절

  • 칼럼 명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다.

  • WHERE 검색 조건은 충돌 없이 사용할 수 있다.

  • ON 조건절에서 사용된 괄호는 옵션사항이다.

  • ❗ ALIAS나 테이블명과 같은 접두사를 사용해야한다 .


WHERE 절과의 혼용

  • SQL>>
SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME FROM EMP E
   
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE E.DEPTNO = 30; 

부서코드 30인 부서의 소속 사원 이름 및 소속 부서 코드, 부서 코드, 부서 이름을 출력



ON 조건절 + 데이터 검증 조건 추가

  • SQL>>
SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME FROM EMP E
   
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698);
   
                                ↆ 같은 코드 
   
 SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME FROM EMP E
   
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE E.MGR = 7698; 



ON 조건절 예제

  • SQL>>
SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME FROM TEAM
   
JOIN STADIUM ON TEAM.TEAM_ID = STADIUM.HOMETEAM_ID ORDER BY TEAM_ID;

팀과 스타디움 테이블을 팀ID로 JOIN하여 팀이름, 팀ID, 스타디움 이름을 찾아본다.

(STADIUM에는 팀ID가 HOMETEAM_ID라는 칼럼으로 표시되어 있다.)



다중 테이블 JOIN

  • SQL >>
SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME FROM EMP E
   
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) JOIN DEPT_TEMP T ON (E.DEPTNO = T.DEPTNO); 

사원과 DEPT 테이블의 소속 부서명, DEPT_TEMP 테이블의 바뀐 부서명 정보를 찾아본다.




◽ CROSS JOIN | 상호 조인

테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합.

  • CARTESIAN PRODUCT 와 같은 의미이다.

    SELECT * FROM <첫 번째 테이블>
    CROSS JOIN <두 번째 테이블>

  • SQL>>
SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT ORDER BY ENAME;

사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다.




◽ OUTER JOIN | 외부 조인

조인 조건에서 행에 동일한 값이 없는 칼럼 조인

  • 2개 테이블 간에 교집합(EQUI JOIN) 조회 + 한쪽 테이블에만 있는 데이터도 포함한다.

  • Oracle 데이터베이스에서 "(+)=" 로 OUTER JOIN 가능하다.

  • JOIN 조건에서 동일한 값이 없는 행도(NULL값도) 출력된다.

  • JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다.




LEFT OUTER JOIN

조인 수행 시 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 우측테이블에서 JOIN 대상 데이터를 읽어온다 .

즉 왼쪽 테이블의 모든 값이 출력되는 조인

  • 오른쪽 테이블에서 없으면 NULL값

  • LEFT JOIN으로 OUTER 키워드를 생략해서 사용할 수 있다.

  • SQL>>
SELECT STADIUM_NAME, STADIUM.STADIUM_ID, SEAT_COUNT, HOMETEAM_ID, TEAM_NAME
   
FROM STADIUM LEFT OUTER JOIN TEAM ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID
   
ORDER BY HOMETEAM_ID; 

STADIUM에 등록된 운동장 중에는 홈팀이 없는 경기장도 있다.

STADIUM과 TEAM을 JOIN 하되 홈팀이 없는 경기장의 정보도 같이 출력하도록 한다.



RIGHT OUTER JOIN

"LEFT OUTER JOIN"와 반대로 조인 수행시 먼저 표기된 우측 테이블에 해당하는 데이터를 먼저 읽은 후,
나중 표기된 좌측 테이블에서 JOIN 대상 데이터를 읽어 온다.


FULL OUTER JOIN

합집합 개념으로 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다.

  • FULL JOIN으로 OUTER 키워드를 생략해서 사용할 수 있다.

    LEFT OUTER JOIN + RIGHT OUTER JOIN

  • SQL>>
UPDATE DEPT_TEMP SET DEPTNO = DEPTNO + 20; ➡(먼저 업데이트)
   
SELECT * FROM DEPT_TEMP; ➡(업데이트한 모두를 읽어온다)




◽ INNER vs OUTER vs CROSS JOIN 비교

  • INNER JOIN의 결과

    양쪽 테이블에 모두 존재하는 키 값이 B-B, C-C 인 2건이 출력

  • LEFT OUTER JOIN의 결과

    TAB1을 기준으로 키 값 조합이 B-B, C-C, D-NULL, E-NULL 인 4건이 출력

  • RIGHT OUTER JOIN의 결과

    TAB2를 기준으로 키 값 조합이 NULL-A, B-B, C-C 인 3건이 출력

  • FULL OUTER JOIN의 결과

    양쪽 테이블을 기준으로 키 값 조합이 NULL-A, B-B, C-C, D-NULL, E-NULL 인 5건이 출력

  • CROSS JOIN의 결과
    - JOIN 가능한 모든 경우의 수를 표시하지만 단, OUTER JOIN은 제외

    키 값 조합이 B-A, B-B, B-C, C-A, C-B, C-C, D-A, D-B, D-C, E-A, E-B, E-C 인 12건이 출력




요약

JOIN 기능 JOIN 기능
EQUI JOIN 교집합, 등가 조인 Non-EQUI JOIN 비등가 조인
INNER JOIN 교집합, ON구 OUTER JOIN 교집합 + 한쪽 테이블
INTERSECT 교집합 조회 CROSS JOIN 조건 없이 조인 




집합 연산자

  • 두개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법 중 하나이다.

  • 집합연산자는 2개 이상의 질의 결과를 하나의 결과로 만들어준다.

  • SELECT절의 칼럼 수가 동일하고 동일 위치에 존재하는 칼럼의 데이터타입이 상호 호환 가능해야 한다.

집합 연산자의 종류

집합 연산자의 연산


▪ UNION: 합집합 : 칼럼 수와 데이터 타입이 모두 동일한 테이블 간 연산만 가능하다.

  • SQL>>

    SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 UNION SELECT 테이블명 WHERE 조건절;

    • UNION ALL: 중복된 행도 전부 출력하는 합집합, 정렬 안함 (↔ UNION은 정렬을 유발함), 집합 연산자에 속함
    • SQL>>

      SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 UNION ALL SELECT 테이블명 WHERE 조건절;


▪ INTERSECT: 교집합 :

  • SQL>>

    SELECT 칼럼명 FROM 테이블명 A WHERE 조건절MS INTERSECT SELECT 테이블명 WHERE 조건절;


▪ MINUS, EXCEPT: 차집합 :

  • SQL>>

    SELECT 칼럼명 FROM 테이블명 A WHERE 조건절 MINUS SELECT 테이블명 WHERE 조건절;




0개의 댓글