JOIN

🔯  JOIN

테이블에서 데이터를 조회하기 위해서 사용되는 것이 Join이다

 

⦁  Join이 수행될 때는 두 개 이상의 테이블이 사용된다 → 이때 둘 중 하나의 테이블을 먼저 읽고 →  Join조건 절을 확인 → 나머지 테이블에가서 데이터를 가져오게 된다.

⦁  WHERE절에 써있는 조건은 ➨ FROM절에 (Table을 묶는)Join조건이 된다.

종 류	설 명
▶ Cross Join	테이블에 존재하는 모든 데이터가 검색 결과로 나타난다.
▶ Equi Join	동일 칼럼을 Join
▶ Non-Equi Join	동일 칼럼이 없이 다른 조건을 사용하여 Join
▶ Outer Join	Join 조건에 만족하지 않는 행도 조회.
▶ Self Join	한 테이블 내에서 Join
▶ ANSI Join	⦁ Cross Join - 테이블 명을 구별할 때 쉼표대신 Cross JOIN ⦁ Inner Join - 테이블 명을 구별할 때 INNER JOIN, 조건문을 쓸때는 ON ⦁ Outer Join - 테이블 명을 구별할 때 (Left | Full | Right) Outer JOIN, 조건문을 쓸때는 ON

​※기본적으로 조인은 다음과 같은 규칙을 준수해야 한다.

① Primary Key와 Foreign Key 컬럼을 통한 다른 테이블의 행과 연결된다

② 연결 Key 사용으로 테이블과 테이블이 결합한다

③ WHERE절에서 Join조건을 사용한다

④ 명확성을 위해 칼럼 이름 앞에 테이블명 또는 테이블 별칭을 붙인다

 

 

 

▶ Cross Join

Cross Join이란 2개 이상의 테이블이 Join될 때 Where(조건)절이 없어 테이블에 존재하는 모든 데이터가 조회 결과로 나타난다.

 

Cross Join으로 Select문의 From절에 EMPLOYEES, DEPARTMENTS 2개의 테이블 동시에 기술하여 테이블의 모든 행을 출력한다. (테이블의 조건이 없어 모든 데이터 출력)

SELECT * 
FROM EMPLOYEES, DEPARTMENTS;
--WHERE 생략

 

 

 

▶ Equi Join

Equi join은 가장 많이 사용하는 Join 방법으로, Join 대상이 되는 두 테이블에서 공통적으로 존재하는 행(Column)의 값이 일치되는 행(데이터)을 연결하여 조회를 한다.

 

EMPLOYEES, DEPARTMENTS 2개의 테이블의 공통 칼럼인 DEPARTMENT_ID의 값이 일치(=)되는 조건을 WHERE절에 서술한다 ➨ 두 테이블을 join하려면 일치되는 공통 칼럼을 사용해야 한다. 칼럼 명이 같게 되면 혼동이 오기 때문에 칼럼명 앞에 "테이블명." 을 붙여준다.

 

공통되는 컬럼 조건 없이 묶기

-- 공통되는 Column name

SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID
FROM EMPLOYEES;

SELECT DEPARTMENT_ID, DEPARTMENT_NAME
FROM DEPARTMENTS;

-- 공통되는 Column name을 Join으로 묶는다

SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, E.DEPARTMENT_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;

 

조인 조건으로 AND연산자 추가

이름이 Susan인 직원의 정보만을 출력하기 위해서는 WHERE 절에서 AND 연산자를 추가한다. 

SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID AND E.FIRST_NAME='Susan';

 

 

▶ Non-Equi Join

1. 두 개의 테이블 사이에 칼럼 값들이 직접적으로 서로 일치하지 않는 경우에 사용한다.

2. 연산자 '='는 사용불가며 (between,>,>=<,<=)등의 연산자를 사용한다

 

1-2)급여 등급 테이블(SALARYGRADE)을 생성하여 레코드를 입력한다.

--테이블 생성
CREATE TABLE SALARYGRADE (
  GRADE NUMBER,
  MINSALARY NUMBER,
  MAXSALARY NUMBER
);

INSERT INTO SALARYGRADE (GRADE, MINSALARY, MAXSALARY) VALUES(1, 2000, 3000);
INSERT INTO SALARYGRADE (GRADE, MINSALARY, MAXSALARY) VALUES(2, 3001, 4500);
INSERT INTO SALARYGRADE (GRADE, MINSALARY, MAXSALARY) VALUES(3, 4501, 6000);
INSERT INTO SALARYGRADE (GRADE, MINSALARY, MAXSALARY) VALUES(4, 6001, 8000);
INSERT INTO SALARYGRADE (GRADE, MINSALARY, MAXSALARY) VALUES(5, 8001, 10000);

--모든 작업을 정상적으로 처리하겠다고 확정하는 명령어이다
commit;   

--출력
SELECT * FROM SALARYGRADE;

급여 등급이 8개로 나눠진 SALARYGRADE 테이블에서 정보를 얻어 와서 각 사원의 급여 등급을 지정한다. 이를 위해서는 EMPLOYEES 테이블과 SALARYGRADE 테이블을 조인한다.

 

1-1) EMPLOYEES, SALARYGRADE을 조인해 EMPLOYEES 테이블에서 급여가 최소 급여부터 최대급여까지의 범위에 속한 사원의 이름,월급,성적을 출력

SELECT E.FIRST_NAME, E.SALARY, S.GRADE
FROM EMPLOYEES E, SALARYGRADE S
WHERE E.SALARY BETWEEN S.MINSALARY AND S.MAXSALARY;

 

 

▶ Outer Join

행이 Join 조건에 만족하지 않을 경우 그 행은 결과에 나타나지 않는다.

하지만 Outer Join을 사용하면 Join 조건에 만족하지 않아도 행이 결과에 나타나게된다.

사용법은 WHERE 정보가 부족한 Column명뒤에 (+)를 붙이면 된다.

 

1) 10번부터 110번 부서번호와 이름,ID를 출력하자

① 사원 테이블과 부서 테이블을 Join하고

② 사원 이름과 부서번호와 부서명을 출력한다.

SELECT E.FIRST_NAME, D.DEPARTMENT_ID , D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID  
ORDER BY D.DEPARTMENT_ID;   --결과 행의 순서를 정렬할때 쓴다(오름차순)

SELECT * FROM DEPARTMENTS;

DEPARTMENTS 테이블를 조회하면 부서테이블의 부서번호와 부서는 110번 이상이 존재한다. 하지만, 조인 결과를 보면 10번부터 110번 부서번호만 출력되고 120번부터는 출력되지 않는다. 이는 EMPLOYEES테이블의 부서번호에는 110번 보다 큰 번호가 존재하지 않기 때문이다.

 

​

부서 테이블의 120번 부서와 조인할 직원 테이블의 부서ID가 없지만, 120번이상의 부서도 출력을 하자

Outer Join을 사용해야 한다. Outer Join을 하기 위해서 사용하는 기호는 (+)이며 조인 조건에서 정보가 부족한 칼럼명 뒤에 위치하게 하면 된다. 

SELECT E.FIRST_NAME, D.DEPARTMENT_ID , D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID;

 

2) 2007년도 상반기에 입사한 사원을 구해보자.

SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, DEPARTMENT_ID
FROM EMPLOYEES
WHERE HIRE_DATE >= '2007/01/01' AND HIRE_DATE <= '2007/06/30';

 

3) Outer Join - Example (+)

2007년도 상반기에 입사한 사원번호, 사원명, 입사일, 부서명을 구한다.

SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, D.DEPARTMENT_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
AND HIRE_DATE >= '2007/01/01' AND HIRE_DATE <= '2007/06/30';

 

 

▶ Self Join

Self Join이란 말 그대로 자기 자신과 Join을 맺는 것을 말한다.

FROM절 다음에 동일한 테이블명을 2번 기술하고 WHERE절에도 조인 조건을 주어야 하는데 이때 서로 다른 테이블인 것처럼 인식할 수 있도록 하기 위해서 별칭을 사용한다

 

1) Self Join Example

특정 사원을 담당하는 매니저인 사원의 이름을 출력하자

SELECT EMPLOYEE_ID, FIRST_NAME, MANAGER_ID 
FROM EMPLOYEES;

 

EMPLOYEES 테이블에 별칭을 사용하여 하나의 테이블을 두 개의 테이블인 것처럼 사용하려면 WORK(사원 테이블)과 MANAGER(매니저 테이블)로 별칭을 부여한다.

SELECT WORK.FIRST_NAME 사원명, MANAGER.FIRST_NAME 매니저명 
FROM EMPLOYEES WORK, EMPLOYEES MANAGER
WHERE WORK.MANAGER_ID = MANAGER.EMPLOYEE_ID;

 

2) Self Join Example

사원의 이름과 그의 매니저 이름을 출력하는 쿼리문

SELECT RPAD(WORK.FIRST_NAME, 11, ' ') || '의 매니저는 ' || MANAGER.FIRST_NAME || '이다.' AS "그 사원의 매니저" 
FROM EMPLOYEES WORK, EMPLOYEES MANAGER
WHERE WORK.MANAGER_ID = MANAGER.EMPLOYEE_ID;

 

 

▶ ANSI Join

1. ANSI (미국 표준 연구소) SQL은 대부분의 상용 데이터베이스 시스템에서 표준 언어이다.

2. 다른 DBMS와의 호환성을 위해서는 ANSI조인을 사용하는 것이 좋다.

3. ANSI 표준 SQL Join 구문은 몇가지 새로운 키워드와 절을 제공하여, SELECT문의 FROM절에서 조인을 완벽하게 지정할 수 있다.

4. 테이블 명을 구별할 때 쉼표대신 INNER JOIN을 쓰고 WHERE대신 ON을 쓴다

종 류	설 명
ASIN Cross Join	테이블 명을 구별할 때 쉼표대신 Cross Join을 쓴다
ANSI Inner Join	조인이 되는 키값을 기준으로 교집합 결과셋을 출력하는 조인 방법으로  각테이블의 NULL값을 포함한다 테이블 명을 구별할 때 쉼표대신 INNER JOIN을 쓰고 조건문을 쓸때는 ON을 쓴다 두 테이블 간의 조인 조건에 사용되는 칼럼이 같다면 USING을 사용할 수 있다
ANSI Outer Join	Outer Join - 테이블 명을 구별할 때 쉼표대신 (Left | Full | Right) Outer Join을 쓰고 조건문을 쓸때는 ON을 쓴다

 

 

ANSI Cross Join

테이블 명을 구별할 때 쉼표대신 Cross Join을 쓴다

SELECT * 
FROM EMPLOYEES CROSS JOIN DEPARTMENTS;

 

 

ANSI Inner Join

Inner Join

조인이 되는 키값을 기준으로 교집합 결과셋을 출력하는 조인 방법으로 
각 테이블의 NULL값을 포함한다

앞서 배운 Join 구문은 공통 칼럼을 '='(equal)연산자를 통해 같은 값을 가지는 로우를 연결하는 방식이였으나

ANSI Inner Join은 다음과 같은 형식으로 작성한다.

SELECT * 
FROM table1 INNER JOIN table2
ON table1.column1 = table2.column2

INNER은 기본값이라 생략이 가능 JOIN ~ ON

SELECT FIRST_NAME, DEPARTMENT_NAME 
FROM EMPLOYEES INNER JOIN DEPARTMENTS 
ON EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID;

SELECT FIRST_NAME, DEPARTMENT_NAME 
FROM EMPLOYEES INNER JOIN DEPARTMENTS 
ON EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID
WHERE FIRST_NAME='Susan';

※ USING을 이용한 Join 조건 지정

두 테이블 간의 Join조건에 사용되는 칼럼이 같다면 ON대신 USING을 사용할 수 있다.

SELECT * 
FROM table1 INNER JOIN table2
USING (공통칼럼)

 

 

ANSI Outer Join

Left Outer Join                                           Full Outer Join                                           Right Outer Join

1. ANSI구문의 Outer Join에서는 전체 데이터를 가져올 테이블 기준으로 LEFT, Full, RIGHT를 설정해 주면 된다.

2. 그리고 이전에 지원하지 않았던 FULL까지 지원한다.

3. Outer Join - 테이블 명을 구별할 때 쉼표대신 (Left | Full | Right) Outer Join을 쓰고 WHERE대신 ON을 쓴다

SELECT * 
FROM table1 [LEFT | RIGHT | FULL] OUTER JOIN table2
ON table1.column1 = table2.column2

SELECT E.FIRST_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D 
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID  
WHERE HIRE_DATE >= '2007/01/01' AND HIRE_DATE <= '2007/06/30';