[ SQL ] Oracle 확장 sql문 #2 _ 조인

[ 조인 ]

 1. 조인의 개요 

• 조인(join)은 2 개 이상의 집합들을 연결하여 각 집합의 데이터들을 같이 다룰 수 있도록 하는 것이다.
• 집합에는 테이블, 뷰 형태가 올 수 있다.
• 집합을 연결하는 방법에 따라 기본적인 SQL 구성은 다음과 같이 분류할 수 있다.

 

 (1) inner join  

• 연결하려는 컬럼값이 일치하는 레코드만 가져온다.

 

 

EX)

FROM TABLEA A INNER JOIN TABLEB B ON A.KEY=B.KEY

 

 

 (2) left join 

• A 테이블이 조회 집합의 기준이 되고 A 테이블의 모든 데이터를 가져온다.
• A 테이블의 A 키값과 일치하는 B 테이블의 B 키값이 있으며 B 테이블의 데이터도 같이 가져온다.
• where 문의 조건으로 null 을 확인하고 B 테이블의 B 키값이 null 이면 A 테이블의 데이터만 가져온다.

 

EX)

FROM TABLEA A LEFT JOIN TABLEB B ON A.KEY=B.KEY

 

FROM TABLEA A LEFT JOIN TABLEB B ON A.KEY=B.KEY WHERE B.KEY IS NULL

 

 

 (3) right join

• B 테이블이 조회 집합의 기준이 되고 B 테이블의 모든 데이터를 가져온다.
• B 테이블의 B 키값과 일치하는 A 테이블의 A 키값이 있으며 A 테이블의 데이터도 같이 가져온다.
• where 문의 조건으로 null 을 확인하고 A 테이블의 A 키값이 null 이면 B 테이블의 데이터만 가져온다.

EX)

FROM TABLEA A RIGHT JOIN TABLEB B ON A.KEY=B.KEY

 

FROM TABLEA A RIGHT JOIN TABLEB B ON A.KEY=B.KEY WHERE  A.KEY IS NULL

 

 

 (4) full outer join

• 양쪽 테이블 모두가 조회 집합의 기준이 되며 양쪽 테이블의 데이터를 모두 가져온다.
• 연결 컬럼값이 일치하면 같은 레코드 상에 가져온다.
• 연결 컬럼값에 일치하는 값이 없으면 각각 다른 레코드 위치에 존재하면서 가져온다.
• where 문의 조건으로 null 을 확인하고 A 테이블의 A 키값이 null 이거나 B 테이블의 B 키값이 null 이면 연결하려는 컬럼값이 일치하는 레코드를 제외하고 가져온다.

 

EX)

FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.KEY=B.KEY

 

FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.KEY=B.KEY WHERE A.KEY IS NULL OR B.KEY IS NULL

 

 

 

 

 2. 오라클의 조인 

• 조인은 일반적으로 관계형 데이터베이스에서는 하나 이상의 테이블 간에 관계를 설정하여 데이터를 조회한다.
• 일반적으로 기본키(Primary Key)와 외래키(Foreign Key)를 사용하여 조인하는 경우가 대부분이지만 때로는 논리적인 값들의 연관으로 조인할 수 있다.
• 관계형 데이터베이스에서는 테이블 간의 관계가 중요하기 때문에 하나 이상의 테이블이 빈번히 결합하여 사용되므로 한 개 이상의 테이블에서 데이터를 조회하기 위해서 조인을 사용한다.
• 오라클은 관계형 데이터베이스이므로 모든 정보가 하나의 테이블에 몰려 있는 것이 아니라 여러 테이블에 정규화되어 분산되어 있으며 각 테이블끼리는 서로 관계가 있도록 설계되어 있다.
• 오라클의 데이터는 여러 곳에 흩어져 있으므로 사용자가 원하는 데이터를 찾으려면 여러 테이블을 다 조회해야 한다.
• 조인은 여러 테이블에 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들어서 결과를 보여준다.
• 사원 테이블에서 이름을 가져오고 부서 테이블에서 부서명만을 가져와서 새로운 결과를 도출하는 것이 조인이다.

 

 

 (1) 크로스 조인  (cross join)

크로스 조인(Cross Join)은 특별한 컬럼명 없이 테이블을 ,(콤마)로 연결해서 사용한다.

select * from table1, table2;

• select 명령어 :표시할 컬럼이나 명령어를 지정한다.
• *(애스터리스크) 기호 : 모든 컬럼을 선택한다.
• from table1, table2; : table1 과 table2 를 ,(콤마)로 연결해서 조인하고 ;(세미콜론)으로 종료한다.

 ex )  emp 테이블과 dept 테이블을 크로스 조인을 한다.

▼ SELECT * FROM EMP;

▼ SELECT * FROM DEPT;

SELECT * FROM EMP, DEPT;

 

 

 (2) 이퀴 조인

• 이퀴 조인(Equi Join)은 같은 컬럼을 기준으로 조인한다.
• 이퀴 조인은 가장 많이 사용하는 조인 방법으로서 조인 대상이 되는 두 테이블에서 공통으로 존재하는 컬럼값이 일치되는 컬럼을 연결하여 결과를 생성하는 조인 방법이다.
• 이퀴 조인은 조인 조건이 정확히 일치하는 경우 사용한다.
• 이퀴 조인은 조인 조건에서 =(이퀄) 연산자를 사용하여 컬럼값이 정확하게 일치하는 경우에 사용한다.
• 이퀴 조인은 다른 말로 단순조인 또는 내부조인이라고 하며 기본키와 외래키의 관계를 이용하여 조인한다.
• 테이블의 테이블명 사용
  * 양쪽 테이블에 공통으로 존재하는 컬럼명은 모호함을 피하고자 컬럼명 앞에 반드시 테이블명을 기술한다.
  * 서로 다른 테이블에 있는 같은 컬럼명에 접근하려고 하면 컬럼명이 중복되어 구별이 어려워 애매하다는 에러가 발생한다.

select table1.컬럼명, table2.컬럼명
from table1, table2
where table1.공통 컬럼명 = table2.공통 컬럼명;

 

• select table1.컬럼명, table2.컬럼명: 테이블을 통해서 컬럼에 접근하여 조인한다.
• 테이블을 조인할 때 명확성을 위하여 컬럼명 앞에 테이블명을 붙이는데 두 테이블에 같은 컬럼명을 사용하면 어느 테이블 소속인지 불분명하기에 오류가 발생한다.
• 같은 컬럼명 존재하는 테이블의 경우에는 반드시 컬럼명 앞에 테이블명을 붙인다.
• from table1, table2: 조인할 테이블인 table1 과 table2 를 지정한다.
• where table1.공통 컬럼명 = table2.공통 컬럼명; : =(이퀄) 연산자로 조인할 테이블의 컬럼명으로 조회할 조건을 지정하며 조건을 충족하는 컬럼값으로 SQL 문을 제한하고 ;(세미콜론)으로 종료한다.

 ex )  사원들의 부서번호(DEPTNO)를 조회한다.

SELECT EMPNO, ENAME, SAL, DEPT.DEPTNO FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

 

 

 

 

 (3) 테이블의 별칭 사용

• 두 개 이상의 테이블을 조인할 때 컬럼명 앞에 테이블명을 기술하는 일이 잦아진다.
• 테이블에서 테이블명을 사용하여 여러 테이블에 있는 컬럼명을 한정하여 사용한다.
• 테이블명이 길면 테이블명을 매번 붙이기가 번거로워지므로 이런 번거로움을 제거하기 위해 테이블명 대신에 별칭(alias)을 사용한다.
• 테이블명에 간단한 별칭을 부여한 후에 컬럼명 앞에 테이블명 대신 간단한 별칭을 붙이면 문이 간단해 보인다.
• 테이블명의 별칭을 사용하여 이름은 같지만 서로 다른 테이블에 상주하는 컬럼을 구분한다.
• 테이블명의 별칭은 30 자까지 사용할 수 있지만 길이는 짧을수록 좋다.

select 별칭 1.컬럼명, 별칭 2.컬럼명
from table1 별칭 1, table2 별칭 2
where 별칭 1.공통 컬럼명 = 별칭 2.공통 컬럼명;

• select 별칭 1.컬럼명, 별칭 2.컬럼명
  별칭을 통해서 컬럼에 접근하여 조인한다.
• from table1 별칭 1, table2 별칭 2
  테이블의 별칭을 생성한다. 테이블명에 별칭을 붙이는 방법은 from 문 다음에 테이블명을 명시하고 공백 다음에 별칭을 설정하면 된다.
• where 별칭 1.공통 컬럼명 = 별칭 2.공통 컬럼명;
   =(이퀄) 연산자로 조인할 테이블의 컬럼명으로 조회할 조건을 지정하며 조건을 충족하는 컬럼값으로 SQL 문을 제한하고 ;(세미콜론)으로 종료한다.

 ex 1 )  사원들의 부서번호와 부서명를 조회한다.

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

 

** EMP 테이블의 별칭 : E

** DEPT 테이블의 별칭 :  D

 

DEPTNO : 부서번호

DNAME : 부서명

 

 

 ex 2 )  부서번호가 30 번인 사원번호, 이름, 급여, 부서명을 조회한다

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = 30;

 

EMPNO : 사원번호

ENAME : 이름

SAL : 급여

DNAME : 부서명

 

 

 ex 3 )  직급이 SALESMAN 사원의 사원번호, 이름, 급여, 부서명을 조회한다.

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.JOB='SALESMAN';

 

 ex 4 )  커미션을 받는 사원의 이름, 직급, 부서 위치를 조회한다.

SELECT E.ENAME, E.JOB, D.DEPTNO, D.DNAME , D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.COMM IS NOT NULL;

커미션을 받는 :  COMM 내용이 NULL이 아닌, 즉, COMM IS NOT NULL

COMM 컬럼은 EMP 테이블에 있으므로 : E.COMM ( E 별칭 사용 )

 

LOC : 부서위치

 

 

 ex 4 )  이름에 ‘A’ 문자가 들어있는 사원의 이름, 직급, 부서 위치를 조회한다

SELECT  E.ENAME, E.JOB, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.ENAME LIKE '%A%';

 

 (4) 논 이퀴 조인

• 논 이퀴 조인(Non-Equi Join)은 같은 컬럼이 없이 다른 조건을 사용하여 조인한다.
• 논 이퀴 조인은 등급, 학점 등과 같이 조인 조건이 정확히 일치하지 않았을 때 사용한다.
• 논 이퀴 조인은 조인 조건에서 between A and B 연산자를 사용하여 컬럼값이 정확히 일치하지 않을 때 사용한다.
• 논 이퀴 조인은 기본키와 외래키의 관계를 이용하지 않고 조인한다.
• 기본키와 외래키는 조인을 위한 제약조건이 아니라 데이터 무결성을 위한 제약조건이다.
• 무결성이 필요한 대용량 데이터가 아닌 경우에는 기본키와 외래키를 사용하여 조인하는 것은 데이터 조작어인 SQL 문을 잘못 작성했을 때 수행에 문제가 발생할 수도 있다.
• 특별히 무결성을 확인할 필요가 없는 컬럼 간의 조인은 논 이퀴 조인으로 조인을 하여 실효성을 높이는 것이 더 효율적이다.

select 별칭 1.컬럼명, 별칭 2.컬럼명
from table1 별칭 1, table2 별칭 2
where 별칭 1.컬럼명 between 별칭 2.컬럼명 and 별칭 2.컬럼명;

• select 별칭 1.컬럼명, 별칭 2.컬럼명
   별칭을 통해서 컬럼에 접근하여 조인한다.
• from table1 별칭 1, table2 별칭 2
   테이블의 별칭을 생성한다. 테이블명에 별칭을 붙이는 방법은 from 문 다음에 테이블명을 명시하고 공백 다음에 별칭을 설정하면 된다.
• where 별칭 1.컬럼명 between 별칭 2.컬럼명 and 별칭 2.컬럼명; 
   between A and B 연산자로 조인할 테이블의 컬럼명으로 조회할 조건을 지정하며 조건을 충족하는 컬럼값으로 SQL 문을 제한하고 ;(세미콜론)으로 종료한다.

 

 

 ex )  부서번호가 10 번인 부서에 대하여 사원번호, 이름, 업무, 급여, 급여의 등급을 조회한다

▼ SELECT * FROM SALGRADE; --등급 테이블

SELECT E. EMPNO, E.ENAME, E.JOB, E.SAL, S.GRADE FROM SALGRADE S, EMP E WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.DEPTNO = 10;

 

 (5) 셀프 조인 

• 셀프 조인(Self Join)은 한 테이블 내에서 조인한다.
• 셀프 조인은 다른 테이블이 아닌 자기 자신과 조인을 한다.
• 셀프 조인은 별칭을 사용해서 마치 2 개의 서로 다른 테이블처럼 조인할 수 있다.
• 셀프 조인은 같은 테이블에 대해 두 개의 별칭을 작성하여 테이블을 구분함으로 from 절에 두개의 테이블을 사용하는 것과 같다.
• 셀프 조인을 할 때는 컬럼에 대해서도 어떤 테이블에서 왔는지 별칭을 기술해야 한다.
• 셀프 조인은 테이블 하나를 두 개 또는 그 이상으로 조인할 수 있다.
• 셀프 조인은 하나의 테이블에서 컬럼을 조인하고자 할 때 사용한다.
• 셀프 조인은 하나의 테이블 내에서 조인해야만 원하는 자료를 얻을 때 사용한다.

 

select 별칭 1.컬럼명, 별칭 2.컬럼명
from table1 별칭 1, table1 별칭 2
where 별칭 1.컬럼명 = 별칭 2.컬럼명;

• select 별칭 1.컬럼명, 별칭 2.컬럼명
   별칭을 통해서 컬럼에 접근하여 조인한다.
• from table1 별칭 1, table1 별칭 2
   같은 테이블에서 서로 다른 별칭을 생성한다. 
   테이블명에 별칭을 붙이는 방법은 from 문 다음에 테이블명을 명시하고 공백 다음에 별칭을 설정하면 된다.
• where 별칭 1.컬럼명 = 별칭 2.컬럼명;
   =(이퀄) 연산자로 조인할 테이블의 컬럼명으로 조회할 조건을 지정하며 조건을 충족하는 컬럼값으로 SQL 문을 제한하고 ;(세미콜론)으로 종료한다

 

 ex )  사원명과 사원을 담당하는 관리자명을 조회한다.

SELECT EMPLOYEE.ENAME AS 사원명, MANAGER.ENAME AS 관리자명 FROM EMP EMPLOYEE, EMP MANAGER WHERE EMPLOYEE.MGR = MANAGER.EMPNO;

별칭1 : EMPLOYEE

별칭2 : MANAGER

조인할 컬럼명 : MGR

조회할 조건 지정

 

 

 

 (6) 아우터 조인 

• 아우터 조인(Outer Join)은 조인 조건에 만족하지 않는 행도 나타낸다.
• 아우터 조인은 조인 조건이 정확히 일치하지 않을 때도 모든 행을 조회한다.
• 아우터 조인은 조인 조건에 만족하지 않아도 해당 행을 출력하고 싶을 때 사용할 수 있다.
• 아우터 조인은 조인할 데이터가 없는 쪽에 ( )(퍼렌씨시스) 안에 +(플러스) 연산자를 사용한다.
• 아우터 조인에 (+) 연산자를 조인 조건에 사용하면 조인 조건을 만족하지 않는 행들도 조회한다.
• (+) 연산자는 한 개 이상의 null 행을 생성하고 정보가 충분한 테이블에 한 개 이상의 행들이 null 행에 조인된다.
• (+) 연산자는 표현식의 한 편에만 올 수 있다.

 

select 별칭 1.컬럼명, 별칭 2.컬럼명
from table1 별칭 1, table1 별칭 2
where 별칭 1.컬럼명 = 별칭 2.컬럼명(+);

• select 별칭 1.컬럼명, 별칭 2.컬럼명
   별칭을 통해서 컬럼에 접근하여 조인한다.
• from table1 별칭 1, table1 별칭 2
   같은 테이블에서 서로 다른 별칭을 생성한다. 
   테이블명에 별칭을 붙이는 방법은 from 문 다음에 테이블명을 명시하고 공백 다음에 별칭을 설정하면 된다.
• where 별칭 1.컬럼명 = 별칭 2.컬럼명(+); 
   =(이퀄) 연산자로 조인할 테이블의 컬럼명으로 조회할 조건을 지정하며 조건을 충족하는 컬럼값으로 SQL 문을 제한하고 ;(세미콜론)으로 종료한다.
  별칭 2.컬럼명(+) 문에서 (+) 연산자로 조인 조건에 만족하지 않아도 해당 행을 출력한다.

 

 

 ex )  사원명과 사원을 담당하는 관리자명을 조회하고 조인 조건에 만족하지 않는 행도 모두 조회한다.

(관리자가 없는 사원명도 출력)

★방법1

SELECT EMPLOYEE.ENAME AS 사원명, MANAGER.ENAME AS 관리자명 FROM EMP EMPLOYEE, EMP MANAGER WHERE EMPLOYEE.MGR = MANAGER.EMPNO(+);

 

 

 

★방법2

SELECT  EMP.ENAME AS "사원명", EMP.EMPNO AS "EMP2",  EMP.MGR AS "MGR2", MANAGER.ENAME AS "관리자명" FROM EMP  LEFT OUTER JOIN EMP MANAGER ON EMP.MGR = MANAGER.EMPNO;