[ SQLD ] SQL - Join & Set Operation 240818

일반집합연산자를 사용하면 조인을 사용하지 않고 연관된 데이터를 조회할 수 있다.

일반집합연산자에는 합집합 ( Union ), 교집합 ( Intersection ), 차집합 ( Difference ), 교차곱 ( Product ) 이 있다.

4개의 일반집합연산자는 SQL문에서 다음과 같은 명령어로 구현된다.

합집합 ( UNION ) → UNION / UNION ALL ( 공통집합의 중복을 허용하고 정렬되지 않는다 )

교집합 ( Intersection ) → INTERSECT

차집합 ( Difference ) → MINUS ( Oracle ) / EXCEPT ( MS-SQL )

교차곱 ( Product ) → CROSS JOIN 곱집합은 조인에서 해결이된다. 두 집합의 모든 경우의 수를 추출한다.

 

순수관계연산자는 두 개 이상의 테이블을 조인하여 데이터를 추출하는데

종류로는 SELECT, PROJECT, JOIN, DIVIDE 가 있다.

SELECT는 여러 행 ( 레코드 / 인스턴스 ) 중에 조건에 맞는 수평적 부분집합을 추출한다

→ 여러 레코드 중 우리가 원하는 로우만 고를 수 있어야 한다. 행을 고르는 것은 WHERE절에서 구현된다.

PROJECT 는 ( 하나의 ) 로우에 대해 원하는 컬럼만 고를 수 있어야 한다. SELECT절에서 구현된다.

두 개 이상의 테이블을 합치는 경우가 필요할 때는 상호 대응하는 데이터중 동일한 데이터가 존재해야 하며

동일한 데이터를 갖는 칼럼을 기준으로 합쳐야할 테이블들이 연결된다. JOIN 은 매우 다양한 JOIN 으로 구현된다.

DIVIDE 는 현재 사용되지 않는다. 코딩으로 구현할 수 있지만 명령어로 제공되지 않는다.

Join과 정규화

정규화를 통해 테이블을 분리하고, JOIN을 통해 원하는 데이터를 얻는다.

반정규화 ( 역정규화 ) 와 JOIN된 테이블의 차이는 테이블의 구조 변화의 유무다.

반정규화는 실제로 테이블의 스키마 구조에 변형이 발생하고,

JOIN은 원하는 데이터를 얻기 위해 일시적으로 ( 두 개 이상의 ) 테이블을 연결하는 것이기 때문에

JOIN시에는 테이블의 스키마 구조에 변형이 일어나지 않는다.

 

대표적 JOIN SQL문

 

SELECT ENAME, DNAME → 합쳐진 테이블에서 ENAME과 DNAME 칼럼을 조회한다

FROM EMP, DEPT → 1) 두 개의 테이블에서 ( EMP와 DEPT )

WHERE EMP.DEPTNO = DEPT.DEPTNO; → 2) EMP 테이블의 DEPTNO 칼럼과 DEPT 테이블의 DEPTNO 칼럼을 기준으로

두 개의 테이블 ( EMP 와 DEPT ) 을 조인할건데 각 테이블의 DEPTNO 칼럼에 대응하는 동일한 데이터를 기준으로 행을 합친다

만약 여기서 두 테이블을 ( 일시적으로 ) 합치는 WHERE절을 작성하지 않고 세미콜론을 작성한 후에 실행한다면 CROSS JOIN이 되어 EMP에 존재하는 14개의 행과 DEPT에 존재하는 4개의 행이 곱해져 두 개의 테이블이 합쳐졌을 때 나올 수 있는 모든 경우에 해당하는 레코드가 출력된다.

 

WHERE절에 = ( 동등기호 ) 의 유무에 따라 Equi join / Non-Equi join 이 나뉘어진다.

=가 존재한다면 Equi join, 없다면 Non-Equi join 이다.

JOIN 키워드의 유무에 따라 암시적 조인 / 명시적 조인으로 나뉘어진다.

JOIN 키워드가 있다면 명시적 조인, 없다면 암시적 조인이다.

 

두 개의 테이블 조인시 두 개의 테이블에 존재하는 칼럼에 대응하는 동일한 데이터를 기준으로 행이 합쳐졌을 때

( 조인 조건으로 Equal ( = ) 연산을 사용했을 때 ) Equi Join, 즉 동등 조인이라 라고,  SELECT절에 컬럼을 조회할 때

두 개의 테이블에 존재하는 중복 칼럼 ( 명 ) 의 경우 칼럼명 앞에 테이블명을 붙여야한다.

- 중복되지 않는 컬럼도 컬럼명 앞에 테이블명을 붙이는 것을 권장한다. 테이블명이 긴 경우 ALIAS 를 사용할 수 있다.

 

동등 조인이 에러가 발생하는 경우

1) 중복되는 칼럼명 앞에 테이블명을 붙이지 않는 경우

2) FROM 절에서 ALIAS ( 별칭 ) 정의 후에 WHERE / SELECT 절에서 테이블명을 사용했을경우

 

동등 조인에서 셋 이상 테이블의 조인은 실제로는 두 테이블 간 조인이 연쇄적으로 일어난다.

예를 들어 A테이블, B테이블 그리고 C테이블을 동등 조인했을 때 A테이블과 B테이블을 먼저 조인하고,

그 결과물을 C테이블과 조인한다는 것이다.

 

 

 

INNER JOIN ( 내부조인 )은 서로 대응하는 내용만 검색하는 조인이고 조건절을 필수로 사용한다.

INNER JOIN 은 Default 이므로 내부 조인시 INNER 키워드 생략 가능하다.

INNER JOIN = JOIN

두 개의 테이블 ( A테이블, B테이블 ) 을 ( 내부 ) 조인했을 때  A테이블에서 조인의 대상이 되는 칼럼과

B테이블에서 조인의 대상이 되는 칼럼에 대응하는 내용 ( 데이터 ) 만을 검색한다.

예를 들어 PLAYER 테이블과 TEAM 테이블을 내부 조인했을 때 조인이 PLAYER 테이블의 TEAM_ID 와 TEAM 테이블의 TEAM_ID 를 기반으로 진행됐을 때, 각 테이블에 존재하는 TEAM_ID 에 대응하는 데이터의 값이 동일한 경우에만 테이블이 ( 일시적으로 ) 합쳐지고 합쳐진 결과를 기반으로 조회할 수 있다.

 

 

암시적으로 ( 내부 ) 조인을 진행할 때 조인조건을 작성하는 WHERE절에

조인조건과 일반조건을 모두 작성했었는데 명시적으로 ( 내부 ) 조인을 수행하게 되면

조인조건은 ON 키워드와 함께 작성하고, 일반조건은 WHERE절에 작성하게 되어

조인조건과 일반조건이 명확하게 구분되어진다.

 

1) 암시적 ( 내부 ) 조인, 조인조건과 일반조건이 WHERE절에 함께 기술됨

SELECT E.ENAME, E.DEPTNO, E.SAL, D.DNAME

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 2000;

 

- 두 개의 테이블 ( EMP 테이블과 DEPT 테이블 ) 을 조인할 건데 JOIN 이라는 키워드가 없으므로 암시적 조인이며

조인의 기본 값 ( DEFAULT ) 은 내부 조인이다. → 내부 조인인데 암시적 조인

- WHERE절에 조인 조건과 일반 조건이 함께 기술되어 있다. 조인 조건은 E.DEPTNO = D.DEPTNO 이며

일반 조건은 E.SAL > 2000 이다. 두 테이블은 각 테이블에 존재하는 DEPTNO 칼럼을 기준으로 합쳐진다.

합쳐진 테이블을 기준으로 일반조건 E.SAL > 2000 이 적용된 결과를 바탕으로 SELECT절이 실행된다.

 

2) 명시적 ( 내부 ) 조인, ON키워드와 함께 조인조건을 작성, WHERE절에 일반조건을 작성

SELECT E.ENAME, E.DEPTNO, E.SAL, D.DNAME

FROM EMP E ( INNER ) JOIN DEPT D → INNER 키워드 생략 가능

ON E.DEPTNO = D.DEPTNO → 조인조건

WHERE E.SAL > 2000;→ 일반조건, 두 개의 테이블이 합쳐진 것을 바탕으로 SAL 컬럼의 데이터 값이 2000 초과인 행들만 걸러내기

 

NATURAL INNER JOIN

- 조인 조건을 명시적으로 작성하지 않았는데 테이블이 조인된다.

- 두 테이블 간 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN을 실행한다. 

※ 칼럼 간 데이터 타입이 동일해야 하며 별도의 조인 칼럼 및 조건을 지정할 수 없다.

조인의 대상이 되는 칼럼에는 접두사 ( 테이블명 또는 ALIAS 를 사용할 수 없다 )

 

NATURAL INNER JOIN 과 INNER JOIN이 출력하는 칼럼 순서 및 칼럼 개수가 다르다. 

테이블A INNER JOIN 테이블 B: 출력되는 칼럼 순서는 테이블 A의 칼럼이 먼저 모두 출력된 후 테이블 B의 칼럼이 모두 출력된다. 

그리고 조인 조건의 칼럼명이 같을 경우 먼저 출력되는 칼럼명은 그대로, 두 번째로 출력되는 칼럼명은 기존 칼럼명에 _1이 붙게된다.

 

NATURAL INNER JOIN 의 경우 이름이 동일한 컬럼은 하나만 출력된다 ( 동일 컬럼 중복 제거 )

조인 조건이 되는 컬럼 ( 중복 컬럼 ) 인 DEPTNO는 제일먼저 출력되고, 그 다음 중복컬럼을 제외한 EMP 테이블의 모든 컬럼이 출력되고 DEPT테이블에서 중복컬럼을 제외한 모든 컬럼이 출력된다 ( 출력순서는 조인되는 조건의 컬럼, JOIN 앞에 위치한 테이블 컬럼, JOIN 뒤에 위치한 테이블의 컬럼 )

 

ON 조건절

- 암시적 JOIN 은 모든 조건을 WHERE절에 기술하는 것이다.

- 명시적 JOIN 은 JOIN 기준 조건은 ON절에 기술하는 것이다.

ON절에 JOIN 기준 조건을 기술할 때 JOIN 기준 조건을 ()괄호안에 작성하는데 괄호는 생략이 가능하다.

JOIN과 무관한 일반조건은 WHERE절에 작성한다.

 

ON 조건절을 줄여서 작성할 수 있는데 JOIN의 대상이되는 두 테이블의 칼럼명이 동일한 경우 USING 을 사용하여 다음과 같이 작성할 수 있다. ON EMP.DEPTNO = DEPT.DEPTNO → USING ( DEPTNO )

 

USING 조건절

- ON절의 = 연산자 대신 USING절 사용이 가능하다.

ON절에서는 괄호가 생략이 가능한데 USING절에서는 괄호 생략이 불가하다.

- USING절에서는 접두사 ( 테이블명 또는 ALIAS ) 를 사용할 수 없다.

 

- 암시적 조인은 두 테이블을 사용한다는 FROM절에 ( INNER ) JOIN 키워드가 직접적으로 쓰이지 않고, (,) 로 사용할 테이블을 구분하여 나열한다 ( FROM EMP, DEPT ) 그리고 두 테이블의 조인조건을 WHERE절에 작성한다 ( WHERE절에는 일반조건 또한 작성이 가능하다 ) WHERE EMP.DEPTNO = DEPT.DEPTNO → EMP테이블의 DEPTNO컬럼과 DEPT테이블의 DEPTNO컬럼의 데이터 값이 대응했을 때 동등하다는 조건하에 행을 합친다. 중복되는 컬럼의 경우 ( 조인조건에 사용된 컬럼 ) 컬럼 앞에 테이블명을 반드시 붙여줘야 한다.

- 명시적 조인의 경우 FROM절에 JOIN 키워드를 명시적으로 작성하여 JOIN의 대상이 되는 테이블명을 작성한다.

예를 들어 FROM EMP JOIN DEPT 의 경우 EMP 테이블과 DEPT 테이블을 조인한다는 것이다. 그리고 이렇게 명시적으로 작성한 경우 WHERE절이 아닌 ON절에 조인조건을 작성한다. ON EMP.DEPTNO = DEPT.DEPTNO; 는 EMP 테이블에 있는 DEPTNO와 DEPT 테이블에 있는 DEPTNO 의 데이터 값을 서로 대응했을 때 일치하는 행만이 합쳐진다는 것이다. 이것이 조인조건이다. ON절에 작성하는 두 테이블의 컬럼이 같아야 한다고 기술했을 때 두 컬럼명과 해당 컬럼의 데이터 타입이 일치한다면 조인조건을 작성한 ON절을 USING절로 작성할 수 있다. 명시적 조인의 경우 조인조건을 작성할 때 ON절이 아닌 WHERE절에 작성하면 에러가 난다. WHERE절에는 일반조건을 작성한다.

- USING절에는 컬럼명에 테이블명 또는 ALIAS를 붙일 수 없고, 오로지 컬럼명만 작성이 가능하며, 해당 컬럼명(들)은 괄호()안에 작성된다. 괄호는 생략될 수 없다. USING절을 작성한 ( INNER ) JOIN 과 NATURAL JOIN 의 차이는 USING절에는 여러 개의 컬럼을 작성할 수 있는 반면 NATURAL JOIN 은 조인조건을 직접 명시할 수 없고, 두 테이블간 동일한 컬럼명이 DEPTNO밖에 없는 경우 NATURAL JOIN 사용이 가능하다.

- USING ( DEPTNO ) 는 중복되는 컬럼이 여러 개 있음에도 DEPTNO 하나의 컬럼을 조인조건으로 한다는 것, NATURAL JOIN 은 두 테이블간 중복되는 모든 컬럼 ( 컬럼명과 데이터 타입이 완전 일치 ) 을 조인조건으로 한다. 여러 개의 컬럼이 중복되는데 전체 컬럼에 대해 동등 조인을 걸때는 NATURAL JOIN을, 여러 개의 컬럼이 중복되는데 그 중 일부에 대해서 동등조인을 걸고 싶을 때는 NATURAL JOIN을 사용할 수 없게 된다.

INNER JOIN 의 경우 조인 조건의 대상인 컬럼의 데이터 값이 대응하는 행만 출력하는 조인이었지만

OUTRE JOIN 의 경우 서로 대응하지 않는 행도 출력하는 조인이다. 조건절을 필수로 사용한다 ( NATURAL JOIN 은 조인 조건절을 생략한다 ) 

 

 

- FULL ( OUTER ) JOIN 은 RIGHT ( OUTER ) JOIN 과 LEFT ( OUTER ) JOIN 의 합집합과 동일하다. 단 중복 행 제거 후의 결과다.

즉 FULL OUTER JOIN 의 결과는 UNION ALL 이 아닌 UNION 의 결과와 동일하다.

합집합을 구하는 UNION 은 SELECT문 하나와 또 다른 SELECT문을 이어준다.

( UNION ALL 은 중복 행을 제거하지 않는다 )

 

 

직원 ( 관리자) 이 직원 ( 팀원 ) 을 관리할 때 Unary Relationship 이 성립되고,

이런 경우 SELP JOIN ( 셀프 조인 ) 이 가능하다. ( 한 명의 ) 직원이 여러 명의 직원을 관리한다.

→ ( 한 명의 ) 관리자가 여러 명의 팀원을 관리한다.

셀프 조인의 경우 FROM절에 동일한 테이블이 두 번 이상 나타난다.

물리적으로 동일한 테이블이지만 개념적으로 서로 다른 테이블로 사용하고,

( 물리적으로 동일한 테이블이지만 ) 개념적으로 상이한 테이블을 식별하기 위해 반드시 별칭 ( ALIAS ) 을 주어야 한다.

FROM EMP E INNER JOIN EMP M

 

셀프 조인을 이해하기 위한 실습 문제

- EMP 테이블로부터 사원의 사번과 이름, 그리고 매니저의 사번과 이름을 출력하기 위한 질의 ( SQL문 ) 를 작성하시오.

( 단, 매니저가 없는 사원의 정보도 출력되어야 한다 )

 

우선 EMP 테이블의 스키마 구조와 모든 레코드 ( 모든 인스턴스 )

우선 사원의 정보를 모두 출력해야 한다 ( 매니저가 없는 사원의 정보도 포함 )

사원 테이블로써의 EMP 테이블 ( EMP E ) 이 출력의 기준이 되어야 한다.

FROM EMP E LEFT OUTER JOIN EMP M

 

그리고 조인 조건을 사원 테이블의 MGR ( 매니저 번호 ) 과 매니저 테이블의 EMPNO ( 사원번호 ) 를 동등 조건으로 작성한다.

ON E.MGR = M.EMPNO → 조인 조건을 작성하는 것이 셀프 조인의 핵심

 

사원테이블의 사번 ( E.EMPNO ) 과 이름 ( E.ENAME ), 그리고 매니저의 사번 ( M.EMPNO ) 과 이름 ( M.ENAME ) 을 조회한다.

SELECT E.EMPNO, E.ENAME, M.EMPNO, M.ENAME

 

 

계층형 데이터

- 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 의미한다.

예를 들어 사원번호 7902 인 FORD 를 사원번호 7566 인 JONES 가 관리하고,

사원번호 7566인 JONES 를 사원번호 7839인 KING 이 관리한다.

KING 이 JONES 를 관리하고, JONES 가 FORD 를 관리하는 관계를 하나의 테이블에서 알 수 있다.

- 엔터티가 순환관계 모델 ( Unary Relationship ) 로 설계된 경우 발생한다.

- 계층형 질의를 통해 접근이 가능하다.

 

 

------------------ 계층형 질의 관련 일단 SKIP ------------------