[SQLD] 9강. SQL 기본 문법 2

Hyunjun Kim·2024년 11월 14일

SQL Developer (자격증)

목록 보기
9/11

9. SQL 기본 문법 2

9.1. JOIN

EQUI JOIN

JOIN 개요

두 개 이상의 테이블을 연결 또는 결합하여 데이터를 출력하는 것을 JOIN 이라고 부릅니다. 일반적으로 하나의 데이터베이스에 테이블은 1개가 아닌 여러 개로 구성되어 있으며 테이블 간 관계가 있습니다. 그렇기 때문에 일반적으로 사용되는 SQL 문장들의 대부분은 JOIN을 통해 데이터를 조회합니다. 이는 관계형 데이터베이스의 가장 큰 장점이자 핵심적인 기능입니다

일반적인 경우 행들은 기본키(PK)나 외래키(FK) 값의 관계 의해서 JOIN이 성립됩니다. 하지만 어떤 경우에는 논리적인 값들의 연관만으로도 JOIN이 가능합니다.

FROM 절에 여러 테이블을 나열하여 JOIN을 할 수 있는데, 이때 JOIN의 발생은 두 테이블만 JOIN이 이루어집니다. 이렇게 2개의 테이블이 JOIN이 완료되면 그다음 테이블과 방금 JOIN된 테이블과의 두 번째 JOIN이 발생합니다. 이렇게 FROM에 작성된 모든 TABLE과의 JOIN이 발생합니다.

EQUI JOIN

EQUI(등가) JOIN은 두 테이블 간에 칼럼의 값들이 서로 같은 경우 두 데이터를 하나의 데이터로 합치는 JOIN을 말합니다. 이때 주로 사용되는 칼럼이 PRIMARY KEY(PK)와 FOREIGN KEY(FK)입니다. 물론 PK와 FK 말고도 다른 칼럼의 값으로도 EQUI JOIN이 성립합니다.

기본 구조

  • JOIN 할 두 테이블을 FROM 절에 나열합니다.
  • JOIN의 조건은 WHERE 절에 = 연산자를 사용하여 표현합니다.
  • 테이블명과 칼럼명을 같이 명시하는 이유는 칼럼명이 동일한 경우 어떤 테이블의 칼럼인지 명확하게 알 수 없기 때문입니다. 즉, 가독성과 유지보수를 높이기 위해서 같이 명시합니다.
  • JOIN의 조건은 FROM 절에 작성된 테이블의 개수에서 하나를 뺀 N - 1개 이상이 필요합니다. CROSS JOIN이라는 예외가 있지만 이는 다음 장에서 언급하도록 하고 기본 구조에 대해서 살펴보겠습니다.

사원-부서 EQUI JOIN 사례

  • 테이블 명이 길고 SQL의 복잡도가 높아지면 가독성이 떨어지기 때문에 테이블명 대신 ALIAS를 주로 사용합니다.
  • 테이블에 ALIAS를 설정했다면 반드시 ALIAS 설정된 명칭으로 JOIN을 진행해야 합니다.

Non EQUI JOIN

Non EQUI(비등가) JOIN은 두 개의 테이블 간에 칼럼들이 서로 정확하게 일치하는 것이 아닌 특정 범위 내에 있는 경우 JOIN을 하고자 할 때 사용합니다. 그래서 = 연산자가 아닌 다른 (BETWEEN, >, >=, <, <= ) 연산자들을 사용을 합니다. 다만 설계상의 이유로 Non EQUI JOIN을 수행하지 못하는 경우도 있습니다.

  • 예시
SELECT e.ename '사원명', e.sal '급여', s.grade '급여등급'
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

또한 LIKE 연산으로 다른 테이블의 값을 이용하여 JOIN을 나타낼 수 있습니다.

  • 예시
SELECT COUNT(*) AS CNT
FROM emp_tbl a, rule_tbl b
WHERE a.ename LIKE b.rule;

3개 이상 TABLE JOIN

3개 이상의 테이블을 JOIN을 할 때는 FROM 절에 JOIN 하고자 하는 테이블을 차례대로 작성해 주면 됩니다. 그러면 첫 번째와 두 번째 순서의 테이블이 먼저 JOIN 이 완료되고 그다음 순서로 세 번째 테이블과의 JOIN이 이루어지면서 3개 테이블의 JOIN이 완료됩니다. (JOIN은 항상 두개의 테이블에서 일어난다)

  • 모든 테이블을 정규화 시켜서 JOIN으로 데이터를 찾기에는 JOIN 진행시 시스템의 부하가 큰 부분이 단점입니다. 그래서 무작정 정규화하기보다는 역정규화 하여 최적의 테이블 구조를 만드는 것이 중요합니다.

연습문제

문제 1

Q. 문제조인에 대한 설명으로 적절한 것은?
A. (1)조인은 다른 데이터베이스 시스템 간의 데이터 이동을 위해 사용된다
A. (2)관련된 데이터를 단 하나의 테이블에서 결합하여 검색하는 것을 조인이라고 한다
A. (3)데이터 그룹화하고 정렬하는 데 조인이 필수로 사용된다
A. (4)2개 이상의 집합을 결합하여 데이터를 출력하는 것을 조인이라고 한다

답
(4)

문제 2

Q. 문제EQUI 조인에 대한 설명으로 옳지 않은 것은?
A. (1)INNER JOIN 을 사용하여 JOIN을 만들 수 있다
A. (2)두 개의 테이블 간에 교집합을 구한다
A. (3)= 비교 연산자를 사용한다
A. (4)모든 비교 연산자를 사용해서 조인할 수 있다.

답
EQUI 조인은 =을 사용해서 조인한다 (4)

9.2. 표준 조인

STANDARD SQL 개요

현재 사용하는 집합 연산을 토대로 만든 관계형 대수들은 4개의 일반 집합 연산자와 4개의 순수 관계 연산자로 나눌 수 있습니다. SQL은 8개의 연산자를 활용한 연산을 구현하는 데 초점을 두고 진행합니다.

일반 집합 연산자

UNION 연산

  • 수학의 합집합으로 제공하기 위한 연산자입니다.
  • 공통 교집합의 중복을 없애기 위한 사전 작업으로 시스템의 부하를 주는 정렬 작업이 발생합니다

    UNION ALL

  • 공통 집합을 중복해서 그대로 보여주는 연산자입니다.
  • 데이터 정렬 및 중복 작업을 하지 않기 때문에 같은 결과라면 UNION보다 효율이 좋습니다.
  • 그대로 보여주기 때문에 정렬 작업이 일어나지 않으며 응답 속도 향상이나 자원 효율화 측면에서 UNION ALL 사용이 권장됩니다.

INTERSECTION 연산

  • 수학의 교집합으로 두 집합의 공통된 값을 추출할 때 사용하는 연산자입니다.
  • 두 집합의 공통된 요소(공통 행)를 출력합니다

DIFFERENCE 연산

  • 수학의 차집합으로 첫 번째 집합에서 두 번째 집합과의 공통집합을 제외한 부분을 출력하기 위해 사용되는 연산자 입니다.
  • Oracle에서는 MINUS로 사용하며 ANSI 표준 대부분의 DBMS 제공 회사는 EXCEPT로 사용합니다.

PRODUCT 연산

  • 수학에서의 곱집합으로 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말합니다.
  • 만약 양쪽 테이블에 자료가 각각 M개 N개가 있으면 M*N 건의 데이터 조합이 발생합니다.
  • CARTESIAN PRODUCT(데카르트의 곱)이라고도 합니다.

순수 관계 연산자

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

SELECT 연산

  • SELECT 절과 키워드는 같으나 동작은 다릅니다.
  • SELECT 문에서는 WHERE 절로 표현 가능합니다.
  • 특정 행에 대한 부분 집합이라고 할 수 있습니다.
  • SELECT 연산자는 특정 조건을 만족하는 데이터들을 뽑아서 새로운 테이블을 만들어서 사용하고 싶을 때 이용하는 연산자입니다.

PROJECT 연산

  • 특정 열에 대한 부분 집합이라고 할 수 있습니다.
  • SELECT는 특정 조건을 만족하는 데이터라면 PROJECT는 원하는 칼럼을 뽑아서 새로운 테이블을 만들어서 사용하고 싶을 때 사용하는 연산자입니다.

JOIN 연산

  • JOIN은 공통 속성을 중심으로 두 개의 테이블을 하나로 합쳐서 새로운 테이블을 만드는 연산자입니다.
  • INNER JOIN 조건과 함께 FROM 절의 NATURAL JOIN, OUTER JOIN, USING 조건절, ON 조건절 등으로 가장 다양하게 발전한 연산자입니다.

DIVIDE 연산

  • 테이블 A와 B가 있다고 가정해 보겠습니다. 여기에서 테이블 B는 테이블 A에 속해 있습니다. 이때 테이블 A와 B의 DIVIDE 연산의 결과는 다음과 같습니다.
  • A에 속해있는 B의 값을 전부 가지고 있는 데이터를 구합니다.
  • 그리고 여기에서 B의 칼럼을 제외한 값이 DIVIDE 연산의 결과입니다.
  • 현재는 SQL문으로 구현되어 있지 않은 연산자입니다.

대표적인 JOIN의 6가지 방식

2개 이상의 테이블을 합쳐서 원하는 결과를 만들어 출력하는 것을 '조인(JOIN)'이라고 합니다. JOIN은 관계형 데이터베이스에서 가장 핵심입니다. 사용자는 기존 WHERE 절의 검색 조건과 테이블 간의 JOIN 조건을 구분 없이 사용하던 방식을 그대로 사용할 수 있습니다. 동시에 추가된 선택 기능으로 JOIN 조건을 FROM 절에서 명시적으로도 정의할 수 있습니다. FROM 절에 명시적으로 사용할 수 있는 JOIN의 종류는 대표적으로 하나씩 알아가 보겠습니다.

대표적인 JOIN 방법 6가지

1. INNER JOIN

  • 일반적으로 조인이라고 부르는 것은 내부 조인(INNER JOIN)을 말하며, 조인 중에서 가장 많이 사용 됩니다.
  • JOIN 조건에서 일치하는(동일한 값이 있는) 행만 반환합니다.
  • INNER JOIN은 그동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시입니다.
  • 반드시 USING 조건절이나 ON 조건절을 사용해줘야 합니다.

2. NATURAL JOIN

  • NATURAL JOIN은 INNER JOIN의 하위 개념으로 두 테이블의 JOIN 되는 칼럼을 별도로 지정하지 않아도 동일한 이름을 갖는 모든 칼럼들에 대해 자동으로 EQUI JOIN을 수행합니다.
  • NATURAL JOIN 사용시 주의해야할 점은 JOIN 기준이 되는 칼럼에 ALIAS나 테이블 명과 같은 접두사를 칼럼에 붙일 수 없습니다. 그리고 동일한 칼럼명이라도 다른 데이터 유형이 저장되었다면 제대로 동작되지 않게 됩니다. 반드시 데이터 유형이 같은지 확인을 해야합니다.
  • 해당 JOIN은 SQL Server 에서는 지원하지 않는 기능입니다.

예시

SELECT depno, empno, ename, dname
FROM emp NATURAL JOIN dept;

실습
군인 이름, 부대 이름, 계급을 Soldier, Unit 테이블의 NATURAL JOIN을 이용하여 출력해보세요.

SELECT first_name, unit_name, rank
FROM Soldier NATURAL JOIN Unit;

3. USING 조건절

  • FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있습니다.
  • 결과 집합이 INNER JOIN을 수행한 결과와 동일합니다.
  • NATURAL JOIN 절에서 USING 절을 이용하려면 NATURAL JOIN을 명시하지 않은 상태로 USING절을 사용해야 합니다.
  • USING 조건절도 NATURAL JOIN과 마찬가지로 JOIN 칼럼에 대해서 ALIAS 나 테이블 이름과 같은 접두사를 붙일 수 없습니다. 다만, JOIN 칼럼이 아닌 다른 칼럼은 ALIAS 사용이 가능합니다.
  • SQL Server에서는 지원하지 않습니다.

예시

SELECT empno,  -- JOIN 컬럼에 대해 Alias 사용 불가
       emp.ename,
       dept.loc,
       dept.dname
FROM emp JOIN dept
	USING (deptno); -- 괄호 없으면 에러 발생, 괄호 필수!

실습
Soldier와 Unit 테이블을 USING절을 이용하여 부대 ID, 군인이름, 부대이름, 계급을 출력해보세요.

SELECT unit_id,
			 Soldier.first_name,
       Unit.unit_name,
			 Soldier.rank
FROM Soldier JOIN Unit
USING (unit_id);

4. ON 조건절

  • USING 조건절은 같은 칼럼명일 때 JOIN 하려는 칼럼을 선택하는 것이라면 ON 조건절은 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있습니다.
  • NATURAL JOIN에서 임의의 JOIN 조건을 지정하거나 이름이 다른 칼럼명을 JOIN 조건으로 사용하고 싶을 때 혹은 JOIN 칼럼을 명시하기 위해서는 ON 조건절을 사용합니다.
  • ON 조건절을 사용하는 JOIN의 경우는 ALIAS나 테이블명을 접두사로 붙여도 상관없으며 오히려 논리적으로 명확하게 지정해 주는것이 좋습니다. 만약 동일한 칼럼명을 기재할 경우 반드시 ALIAS를 통한 구분이 필요합니다.

ON 조건절에 활용법
1. WHERE 절과의 혼용
ON 조건절은 WHERE 절과 충돌 없이 사용할 수 있습니다.

  • 예시
SELECT e.ename, e.deptno, d.deptno, d.dname
FROM emp e JOIN dept d 
	ON e.deptno = d.deptno
WHERE e.deptno = 30;
  1. ON 조건절 + 데이터 검증 조건 추가
    ON 조건절에서 JOIN 조건 외에도 데이터를 검색하기 위한 조건을 추가할 수 있습니다. 검색 조건 목적인 경우에는 WHERE 절을 사용하는 것을 권장합니다.
  • 예시
SELECT e.ename, e.deptno, d.deptno, d.dname
FROM emp e JOIN dept d 
	ON e.deptno = d.deptno AND e.mgr = 7698; -- WHERE 사용 권장
  1. ON 조건절 예제
    같은 의미이지만 다른 이름의 칼럼을 사용할 때는 USING 조건절을 사용할 수 없으며 이때는 ON 조건절을 사용해야 합니다.
  • 예시
SELECT team_name, team_id, stadium_name
FROM team JOIN stadium 
	ON team.team_id = stadium.hometeam_id
ORDER BY team_id;
  1. 다중 테이블 JOIN
    세 개 이상의 테이블을 JOIN을 하려고 할 때는 ON 조건절 다음에 JOIN 을 중첩적으로 명시해 주면 됩니다. 만약 3개 이상의 테이블을 JOIN 하려고 한다면 조건은 최소 2개가 필요합니다. 최소 JOIN 조건의 수 = JOIN 테이블의 수 - 1

5. CROSS JOIN

  • 일반 집합 연산자의 PRODUCT 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 결과로 나타냅니다. 두 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT 와 같은 표현으로도 불리며 결과는 M*N 건의 데이터 조합이 발생하게 됩니다.

  • CROSS JOIN 은 WHERE 절에 JOIN 조건을 추가하여 사용할 수 있습니다. 다만 INNER JOIN과 같은 결과를 얻기 때문에 CROSS JOIN 을 사용하는 의미가 없어지게 됩니다.

  • 평소에는 CROSS JOIN이 필요한 경우는 많지 않지만, 간혹 튜닝이나 리포트를 작성하기 위해 의도적으로 사용하는 경우가 있습니다.

  • 예시
    부대 테이블과 봉사활동 테이블을 CROSS JOIN을 활용하여 부대 이름, 주스 종류, 판매수량을 판매수량 내림차순으로 하여 출력해 보세요.

SELECT unit_name, juice_type, quantity_sold
FROM Unit CROSS JOIN VolunteerActivity
ORDER BY quantity_sold DESC;

6. OUTER JOIN

  • OUTER JOIN은 "OUTER 집합을 기준으로 JOIN"한다는 의미입니다. OUTER 테이블은 모두 출력되고 INNER 테이블은 매칭되는 데이터만 출력한다는 의미를 갖습니다.
  • OUTER JOIN은 JOIN 조건에서 동일한 값이 없는 데이터도 반환할 때 사용할 수 있으며 INNER JOIN과 대비되는 JOIN 입니다. OUTER JOIN도 INNER JOIN과 마찬가지로 JOIN 조건을 FROM 절에서 정의하는 것이기 때문에 USING 조건절이나 ON 조건절을 반드시 사용해야 합니다.
  • OUTER JOIN은 기준이 되는 테이블에 따라 LEFT OUTER JOIN / RIGHT OUTER JOIN 으로 나뉘며 기준이 되는 테이블이 조인 수행시 무조건 드라이빙 테이블이 됩니다.
  • 과거 OUTER JOIN을 위해 Oracle은 JOIN 칼럼 뒤에 (+)를 붙여서 표시하였습니다.

드라이빙 테이블(Driving Table)이란?
드라이빙 테이블은 데이터 행이 먼저 선택되는 테이블입니다.

  1. FULL OUTER JOIN
    FULL OUTER JOIN은 좌측 테이블과 우측 테이블이 동시에 기준이 되는 JOIN 방법으로 LEFT OUTER JOIN과 RIGHT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일합니다.
  • 예시 : deptno 칼럼을 기준으로 dept와 dept_temp 테이블을 FULL JOIN 하는 방법
SELECT * 
FROM dept FULL [OUTER] JOIN dept_temp
	ON dept.deptno = dept_temp.deptno;

INNER vs OUTER vs CROSS JOIN 비교

JOIN 종류 정리

연습문제

1번

Q. 문제합집합을 만들 때 정렬을 유발하는 것은?
A. (1)EXCEPT
A. (2)MINUS
A. (3)UNION ALL
A. (4)UNION

답
UNION 연산은 2개의 테이블을 하나로 합치며 중복 데이터를 제거한다. 이에 UNION은 정렬 과정을 발생시킨다

2번

Q. 문제Oracle 데이터베이스에서 차집합을 구하는 것은?
A. (1)INTERSECTION
A. (2)UNION ALL
A. (3)MINUS
A. (4)DIVIDE

답
MINUS

3번

Q. 문제데이터의 행 수가 가장 많이 조회되는 것은?
A. (1)LEFT OUTER JOIN
A. (2)RIGHT OUTER JOIN
A. (3)CORSS JOIN
A. (4)INNER JOIN

답
CROSS JOIN은 카테시안 곱이 발생하여 많은 행이 조회된다

profile
Hyunjun Kim
Data Analytics Engineer 가 되
이전 포스트

[SQLD] 8강. SQL 기본 문법 1

다음 포스트

[SQLD] 10. SQL의 활용 1

0개의 댓글