조인 JOIN : 내부조인(동일조인, 자연조인, 교차조인) / 외부조인(+) : LEFT, RIGHT

조인 JOIN

여러개로 흩어져있는 테이블을 조합해서 데이터를 조회하는 방법으로 내부조인, 외부조인이 있다.

 

 

1. 내부조인

공통된 컬럼(속성) 조건이 있는 경우 정확하게 일치하는 데이터만 조회

동일(동등)조인, 자연조인, 교차조인

 

1) 동등조인 / 동일조인 / 이퀴(Equi) 조인

조인의 가장 일반적인 활용 형식으로 둘 이상의 테이블에 존재하는 공통 컬럼(속성)의 동등 비교만을 사용하며 동일한 조건을 가진 데이터를 꺼내온다. 연산자 = 을 이용하고, 부등호 조인은 동등 조인에 포함되지 않는다.

컬럼을 직접 선택해서 비교하는 것이다.

 

2) 자연조인

동일조인과 거의 동일하지만, 조인 대상 테이블의 모든 컬럼을 비교하여 같은 컬럼명을 가진 컬럼으로 조인을 수행한다. 같은 이름의 컬럼은 한 번만 출력된다. 

3) 교차조인 (CROSS JOIN)

카티션 프로덕트(Cartesian Product), 조인에 참여한 모든 테이블에 데이터를 조합한 모든 조합을 추출하는 것이다.

 

 

2. 내부조인 수행

조인을 예시로 보면서 확인하기 위해 공통 컬럼과 데이터를 가지고 있는 테이블 A,B를 만들었다.

TABLE_A

 

TABLE_B

두 테이블의 CODE컬럼의 데이터 A,B,C가 동일하다.

 

 

1) 교차조인

SELECT * FROM 테이블A, 테이블B...

SELECT * FROM 테이블A CROSS JOIN B

select a.code, a.num, b.code, b.val
from table_a a , table_b b;

테이블을 각각 A, B로 별칭을 만들고 테이블명.컬럼명의 나열로 SELECT 하였다.

모든 데이터의 모든 경우의 수의 조합을 가져오는 교차조인의 결과이다.

 

select * from table_a, table_b;

select * from table_a cross join table_b;

교차조인은 간단하게 이러한 SQL문으로 실행할 수도 있다.

FROM 에서 테이블이 2개이상이면 조인이다.

 

 

2) 동일조인

SELECT A테이블.컬럼1, A테이블.컬럼2..., B테이블.컬럼1, B테이블.컬럼2...

FROM 테이블A, 테이블B...

WHERE A테이블.컬럼=B테이블.컬럼

select a.code, a.num, b.code, b.val
from table_a a , table_b b
where b.code=a.code;

위 교차조인에서 WHERE 조건만 추가했다. B.CODE=A.CODE 동일 조건을 추가하면서 동일조인을 수행할 수 있다.

데이터가 정확히 일치했던 A,B,C 에 대한 데이터만 가져오게 된다.

 

 

3) 자연조인

SELECT * FROM 테이블A NATURAL JOIN 테이블B

자연조인은 NATURAL JOIN으로 테이블을 연결한다.

select * from table_a natural join table_b;

위에서 했던 설명처럼 동일조인과 거의 비슷한 결과를 확인할 수 있다. 대신 동일한 컬럼인 CODE가 자연조인에서는 한번만 출력된다. (같은 이름을 가진 컬럼은 한 번만 추출된다.)

 

 

 

4. 외부조인 (OUTER JOIN)

한쪽테이블에 데이터가 있고 다른 테이블에는 데이터가 없는 경우에 매칭이 되지 않아도 데이터를 조회할 수 있게 하는 조인 (동일이나 자연조인의 경우에 한쪽에 데이터가 없으면 조회가 되지 않는다)

기준 테이블의 데이터는 매칭과 관계없이 모두 조회되고, 대상 테이블에 매칭되는 데이터가 있을 경우 해당 컬럼의 데이터를 가져온다. 

-> 기준 테이블의 데이터가 누락되지 않는다.

A와 B 테이블에서 A 테이블에는 데이터가 있지만 B 테이블에는 데이터가 없는 데이터를 출력한다. 누락된 데이터를 찾을 때 많이 이용한다.

불일치 쿼리를 조회하는 방법은 외부조인 NULL, 서브쿼리 IN 을 이용한다.

 

 

 

5. 외부조인 수행

기준 테이블에 아우터 조인을 할 대상 테이블의 컬럼에 "(+)" 기호를 붙여서 조인한다.

select a.*, b.*
from table_a a, table_b b
where b.code(+) = a.code;

(+) : 대상 테이블

(+)가 붙지 않은 A테이블이 기준이 되고, 테이블A의 모든 내용을 꺼내겠다는 뜻이 된다.

A 테이블의 CODE가 B테이블의 CODE에 데이터가 있을 경우에는 데이터를 가져오고, 없을 경우에는 NULL을 가져온다.

TABLE_A의 모든 데이터 A,B,C,F가 출력되었고 F에는 매칭되는 테이블B 데이터가 없기 때문에 NULL로 뜨게 된다.

 

select a.*, b.*
from table_a a, table_b b
where b.code(+) = a.code and b.code is null;

b.code is null 조건을 추가해서 누락 데이터를 찾을 수 있다. (B에 맞는 데이터가 없는 경우만 출력하도록)

 

 

select a.*, b.*
from table_a a, table_b b
where b.code = a.code(+);

(+)표시가 없는 B테이블의 CODE 컬럼이 모두 표시된다. 대응되는 A테이블의 데이터가 없으면 NULL로 뜨게 된다.

 

select a.*, b.*
from table_a a, table_b b
where b.code = a.code(+) and a.code is null;

NULL  조건을 추가하여 대응하지 않는 데이터만 출력할 수 있다.

 

 

 

 

학생 테이블과 전공 테이블사이에 겹치는 데이터 학과번호(MAJOR_NO)이 존재한다. 

EX. 학생 정보 조회시 학번, 이름, 학과명, 평점 조회

SELECT s.sno , s.sname, m.major_name, s.score
FROM student s, major m
WHERE s.major_no=m.major_no;

WHERE 조건인 MAJOR_NO가 동일한 데이터만 출력하는 동일조인이다.

 

EX. 학생정보는 무조건 출력, 학과번호가 일치하는 번호가 없어도 조회 (외부조인)

SELECT s.sno , s.sname, m.major_name, s.score
FROM student s, major m
WHERE s.major_no=m.major_no(+);

M테이블에 (+)를 추가하여 S테이블이 모두 출력되는 외부조인이 수행된다. S 테이블의 데이터가 모두 출력되고 M테이블에 동일한 MAJOR_NO 가 없는 데이터는 NULL로 표시된다. 

 

SELECT s.sno , s.sname, NVL(m.major_name,'학과번호오류') as major_name, s.score
FROM student s, major m
WHERE s.major_no=m.major_no(+);

NVL 함수를 이용해서 NULL 대신 원하는 문자열이 출력될 수 있도록 수정할 수 있다. 일치하는 학과번호가 없는 데이터에는 NULL 대신에 '학과번호오류'라는 문자열이 출력 된다.

 

 

 

6. JOIN + GROUP BY

테이블 내에서 칼럼을 기준으로 GROUP BY하여 그룹함수를 사용했었는데, JOIN을 이용하면 다른 테이블의 컬럼을 그룹화 할 수 있고, 그룹함수를 사용할 수 있다.

 

6-1. 테이블 2개 JOIN, 그룹화

EX. 학과별 평점의 평균, 인원수 조회 (단, 학과번호가 일치하지 않는 학과명은 학과코드 오류로 조회)

select m.major_name,trunc(avg(s.score),2) as avg_score,count(*)
from student s, major m
where s.major_no = m.major_no(+)
group by m.major_name
order by avg_score desc;

student 테이블에는 major_name이 없는상태에서 major 테이블과 student 테이블을 join하여 major_name으로 그룹화 할 것이다. 학생정보를 모두 나타내기 위해서 student 테이블이 아닌 major에 (+)를 붙여 외부조인을 수행한다. 그룹화, 정렬까지 할 수 있다.

 

 

EX. 학과별 제적 대상자의 인원수 조회 (점수가 1.5미만 제적)

select m.major_name, count(*)
from student s, major m
where s.major_no=m.major_no(+) and s.score<1.5
group by major_name;

조건을 추가할 경우에는 where에 이어준다.

 

 

 

6-2. 테이블 3개 JOIN, 그룹화

EX. 학과별 장학금을 못받는 인원수를 조회

select m.major_name, count(*)
from student s, major m, student_scholarship ss
where s.major_no=m.major_no(+) and s.sno = ss.student_no(+) and ss.student_no is null
group by major_name;

3개의 테이블을 연결해주려고 JOIN을 추가 하였다. 장학금을 못받는 학생을 조회하기 위해 is null로 불일치쿼리를 작성한 뒤에 그룹화 해주면 원하는 결과를 출력할 수 있다.

 

 

 

6. RIGHT JOIN, LEFT JOIN

왼쪽 외부 조인(LEFT OUTER JOIN)

좌측 테이블의 모든 데이터를 포함하고, 우측 테이블에 조인할 컬럼의 값이 없는 경우 사용

 

 

오른쪽 외부 조인(RIGHT OUTER JOIN)

우측 테이블의 모든 데이터를 포함하는 결과를 출력한다. 오른쪽 외부 조인은 좌측 테이블에 조인할 컬럼의 값이 없는 경우 사용

 

RIGHT JOIN

SELECT *
FROM TABLE_A
RIGHT OUTER JOIN TABLE_B
ON TABLE_A.CODE = TABLE_B.CODE;

B에 JOIN되고, B의 모든 데이터가 출력된다. A에서 B에 없는 값은 NULL로 출력된다.

 

LEFT JOIN

SELECT *
FROM TABLE_A
LEFT OUTER JOIN TABLE_B
ON TABLE_A.CODE = TABLE_B.CODE;

기준테이블 A. A에 조인되고, A의 모든 데이터가 출력된다. B에서 A에 없는 값은 NULL로 출력된다.

 

FULL JOIN

SELECT *
FROM TABLE_A
FULL OUTER JOIN TABLE_B
ON TABLE_A.CODE = TABLE_B.CODE;

A,B 모든 내용 보여주고 없는 값은 NULL 로 보여준다.