[자격증 / SQLD] SQL 활용(1)_표준 조인과 집합 연산자

 

DATA ON-AIR에서 21번 표준 조인과 22번 집합 연산자를 요약 정리한 내용이다.

 

 

 

---

 

 

 

STANDARD SQL 개요

Oracle DBMS에서 보다 쉽게 데이터를 추출하거나 SQL 튜닝의 효과를 함께 얻을 수 있도록 추가된 대표적인 ANSI/ISO 표준 SQL의 기능은 다음과 같다.

• STANDARD JOIN 기능 추가 (CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)
• SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들
• ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능
• WINDOW FUNCTION 같은 새로운 개념의 분석 기능들

 

 

일반 집합 연산자

일반 집합 연산자를 현재의 SQL과 비교하면,

1. UNION 연산은 UNION 기능으로,
2. INTERSECTION 연산은 INTERSECT 기능으로,
3. DIFFERENCE 연산은 EXCEPT(Oracle은 MINUS) 기능으로,
4. PRODUCT 연산은 CROSS JOIN 기능으로 구현되었다.

 

 

순수 관계 연산자

순수 관계 연산자는 관계형 데이터베이스를 구현하기 위해 새롭게 만들어진 연산자이다.

1. SELECT 연산 -> WHERE 절로 구현
2. PROJECT 연산 -> SELECT 절로 구현 : 속성 LIST의 Attribute만 추출
3. (NATURAL) JOIN 연산 -> 다양한 JOIN으로 구현
   • WHERE 절의 INNER JOIN
   • FROM 절의 NATURAL JOIN, INNER JOIN, FROM 절의 OUTER JOIN
   • USING 조건절
   • ON 조건절 등등...
4. DIVIDE -> 현재 사용 X

 

 

 

FROM 절 JOIN 형태

ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태는 다음과 같다.

1. INNER JOIN
2. NATURAL JOIN
3. USING 조건절
4. ON 조건절
5. CROSS JOIN
6. OUTER JOIN

 

 

INNER JOIN

• JOIN 조건에서 동일한 값이 있는 행만 반환한다.
• INNER JOIN 표시는 그동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.

 

 

NATURAL JOIN

• 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행한다.
• NATURAL JOIN이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다.
• SQL Server에서는 지원하지 않는 기능이다.
• JOIN에 사용된 칼럼들은 같은 데이터 유형이어야 하며, ALIAS나 테이블 명과 같은 접두사를 붙일 수 없다.

NATURAL JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리, INNER JOIN의 경우는 2개의 칼럼으로 표시한다.

 

 

ON 조건절

JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, WHERE 절의 JOIN 조건과 같은 기능을 하면서도, 명시적으로 JOIN의 조건을 구분할 수 있다.

• 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다.
• USING 조건절과 반대로 ALIAS나 테이블명을 반드시 사용해야 한다. (DEPTNO → E.DEPTNO)
• ON 조건절에서 사용된 괄호는 생략 가능하다.
• JOIN과 무관한 일반 조건은 WHERE절에 기술한다.

-- 이름에 'S'를 포함하는 사원의 사원이름, 부서코드, 부서명 출력

SELECT  E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME 
FROM    EMP E JOIN DEPT D 
ON      (E.DEPTNO = D.DEPTNO)
WHERE   E.ENAME LIKE '%S%';

 

 

USING 조건절

• FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수가 있다.
• JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.
• JOIN에 사용되는 칼럼은 1개만 표시한다.
• 이 기능은 SQL Server에서는 지원하지 않는다.

-- 세 개의 칼럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을 
-- DEPTNO 칼럼을 이용한 [INNER] JOIN의 USING 조건절로 수행하는 예제

SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);

 

+ WHERE, ON, USING절 조건 기술 비교

-- 4개의 SQL 문장은 모두 같은 결과를 가진다.

-- 암시적 조인
SELECT ENAME, EMP.DEPTNO, DNAME
FROM   EMP, DEPT
WHERE  EMP.DEPTNO = DEPT.DEPTNO;

-- 명시적 조인
SELECT ENAME, EMP.DEPTNO, DNAME
FROM   EMP JOIN DEPT
ON     EMP.DEPTNO = DEPT.DEPTNO;

-- UISNG절
SELECT ENAME, DEPTNO, DNAME
FROM   EMP JOIN DEPT
WHERE  (DEPTNO);

-- 두 테이블 간 동일한 칼럼명이 DEPTNO 밖에 없는 경우
SELECT ENAME, DEPTNO, DNAME
FROM   EMP NATURAL JOIN DEPT;

 

 

CROSS JOIN

• 일반 집합 연산자의 PRODUCT의 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다.
• 결과는 양쪽 집합의 M * N 건의 데이터 조합이 발생한다. (M과 N은 서로 다른 두 개의 테이블을 의미)

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

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

 

 

OUTER JOIN

• JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다.
• JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.
• SQL 식에서 (+) 안붙은 쪽으로 JOIN 한다.

 

+ LEFT OUTER JOIN

먼저 표기된 좌측 테이블에 해당하는 데이터를 읽은 후, 나중에 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다. 우측 값에서 같은 값이 없는 경우 NULL 값으로 채운다.

TABLE A와 B가 있을 때 TABLE 'A'가 기준이 된다.

 

+ RIGHT OUTER JOIN

조인 수행 시 LEFT JOIN과 반대로 우측 테이블이 기준이 되어 결과를 생성한다.

TABLE A와 B가 있을 때 TABLE 'B'가 기준이 된다.

 

+ FULL OUTER JOIN

좌우측 테이블의 모든 데이터를 읽어 JOIN 하여 결과를 생성한다. 중복 데이터는 삭제한다.

TABLE 'A', 'B' 모두 기준이 되므로 RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일하다.

 

+ SELF JOIN

동일 테이블 사이의 JOIN

SELECT WORKER.EMPNO 사원번호, WORKER.ENAME 사원명, MANAGER.ENAME 관리자명 
FROM EMP WORKER, EMP MANAGER 
WHERE WORKER.MGR = MANAGER.EMPNO;

 

 

 

집합 연산자

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

• 여러 개의 질의의 결과를 연결하여 하나로 결합하는 방식을 사용한다.
• 집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만들어 준다.
• SELECT 절의 칼럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능(반드시 동일한 데이터 타입일 필요는 없음) 해야 한다.

+ 집합 연산자의 종류

집합 연산자	연산자의 의미
UNION	여러 개의 SQL의 결과에 대한 합집합으로 결과에서 모든 중복된 행을 하나의 행으로 만든다.
UNION ALL	합집합을 추출하며, 중복된 행도 결과에 그대로 표시한다.
INTERSECT	수학의 교집합으로써 두 집합의 공통집합을 추출하며, 중복된 행은 하나의 행으로 만든다.
MINUS (EXCEPT)	차집합을 추출하며, 중복된 행은 하나의 행으로 만든다.
CROSS JOIN (PRODUCT)	곱집합으로, JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 결과는 양쪽 집합의 M * N건의 데이터 조합이 발생한다.

 

 

 

---

[관련 글 보러가기]

• [자격증 / SQLD] SQL 기본(1)_TABLE과 명령어(DML, DDL, DCL, TCL)
• [자격증 / SQLD] SQL 기본(2)_WHERE 조건절(연산자)과 함수
• [자격증 / SQLD] SQL 기본(3)_GROUP BY, ORDER BY, 조인(JOIN)
• [자격증 / SQLD] SQL 활용(2)_계층형 질의, 서브쿼리
• [자격증 / SQLD] SQL 활용(3)_그룹 함수와 윈도우 함수
• [자격증 / SQLD] SQL 활용(4)_절차형 SQL

 

---

 

[참고자료]

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=4&mod=list

SQL – DATA ON-AIR