본문 바로가기
Oracle SQL Developer

SQL FROM, JOIN

by wanttosleep1111 2023. 1. 24.

SQL FROM, JOIN

 

※ 연습 데이터

makeData.sql
0.01MB

 

1. 테이블과 스키마

 

  • 스키마 : 테이블, 뷰, 인덱스 등 데이터 객체(=오브젝트)의 집합
  • 오라클에서는 계정 이름이 스키마 이름과 동일
  • 서로 다른 스키마(=사용자)에 대해서도 권한이 부여된다면 조회가 가능

 

 

※ C##SERVICE 계정으로 접속하면 기본적으로 테이블을 가져올 때 C##SERVICE 계정의 스키마에서 가져온다.

컬럼도 스키마.테이블.컬럼 형태로 가져와야 하지만 오라클에서 생략 가능함

 

 

  • FROM 뒤에 여러 개의 테이블이 나열 가능하며, 조인 조건이 없다면 카티션 조인 실행
    (각 테이블 행 수만큼 곱해서 출력)

예시) 직원테이블(10건), 직원주소테이블(5건), 직원연락처테이블(13건) 카티션 조인 → 650행 출력

SELECT *
  FROM 직원
     , 직원주소
     , 직원연락처;

 

 

  • 테이블에 대해서도 ALIAS(별칭) 가능

예시) 직원테이블, 직원주소테이블, 직원연락처테이블에 AS를 주고, 각 테이블의 직원ID를 출력

SELECT A.직원ID
     , B.직원ID
     , C.직원ID
  FROM 직원 A
     , 직원주소 B
     , 직원연락처 C;

 

 

2. JOIN

 

※ 조인이 필요한 이유

예시) 직원ID가 'A0006'인 직원의 이름, 휴대폰번호, 집주소를 출력

 

① 조인이 없을 경우 각각의 테이블에서 데이터를 뽑아야 한다.

 

② 조인을 이용하면 쿼리 한번에 데이터 뽑기 가능

 

 

  • 두 개 이상의 테이블을 특정 기준(컬럼 값)으로 합치는 것
    (기준이 없으면 카티션 조인)
  • 특정 기준으로 "=" 조인이면 동등 조인, 그 외는 비동등 조인 (>, <=, != 등)

※ 테스트 데이터 생성

/* 테스트 데이터 */ 
CREATE TABLE 고객 ( 
고객번호 VARCHAR2(5) PRIMARY KEY , 
고객명 VARCHAR2(50) NOT NULL 
) ; 
CREATE TABLE 고객전화번호 ( 
고객번호 VARCHAR2(5) ,
전화구분코드 VARCHAR2(10) ,
전화번호 VARCHAR(15) NOT NULL 
) ; 
ALTER TABLE 고객전화번호 ADD CONSTRAINT PK_고객전화번호 PRIMARY KEY(고객번호 , 전화구분코드) ; 
INSERT INTO 고객 VALUES ( '0001' , '동동일' ) ; 
INSERT INTO 고객 VALUES ( '0002' , '동동이' ) ; 
INSERT INTO 고객 VALUES ( '0003' , '동동삼' ) ; 
INSERT INTO 고객전화번호 VALUES ( '0001' , '집전화' , '062-111-1111') ; 
INSERT INTO 고객전화번호 VALUES ( '0001' , '휴대폰' , '010-1111-1111') ; 
INSERT INTO 고객전화번호 VALUES ( '0002' , '휴대폰' , '010-2222-2222') ; 
INSERT INTO 고객전화번호 VALUES ( '0004' , '휴대폰' , '010-4444-4444') ; 
COMMIT;

 

 

① 동등 조인 : 조인 조건이 모두 '='

예시)

SELECT *
  FROM 고객 A
     , 고객전화번호 B
 WHERE A.고객번호 = B.고객번호 ;

 

② 비동등 조인

예시)

SELECT *
  FROM 고객 A
     , 고객전화번호 B
 WHERE A.고객번호 <= B.고객번호 ;

 

 

  • 조인할 때 값이 NULL이면 조인되지 않음
    (NULL = NULL은 잘못된 결과)

 ※ 데이터 추가

ALTER TABLE 고객전화번호 DROP CONSTRAINT PK_고객전화번호 ;
INSERT INTO 고객전화번호 VALUES ( NULL , '휴대폰' , '010-9999-9999');
COMMIT;
SELECT A.*
     , B.*
  FROM 고객 A
     , 고객전화번호 B
 WHERE A.고객번호 = B.고객번호 ;

NULL은 산술, 비교연산이 불가능

값을 그냥 버림, OUTER JOIN 필요

 

 

  • 조인에 성공한 대상만 추출하면 INNER JOIN, 조인에 실패해도 기준 테이블 값을 추가 출력하면 OUTER JOIN

① INNER JOIN : 조인 조건에 부합하는 대상만 출력

SELECT A.고객번호     AS 고객번호
     , A.고객명       AS 고객명
     , B.전화구분코드  AS 전화구분코드
     , B.전화번호     AS 전화번호
  FROM 고객 A
     , 고객전화번호 B
 WHERE A.고객번호 = B.고객번호 ;

 

② OUTER JOIN : (+), 조인 조건에 부합하는 대상을 출력하고, 기준 테이블에서 조인에 실패한 행도 모두 출력
※ (+)가 없는 쪽이 기준 테이블

※ 조인조건에 (+) 기호가 보인다면 오라클 방식으로 OUTER JOIN, 다른 DBMS에서 사용 불가능할 수 있음

SELECT A.고객번호     AS 고객번호
     , A.고객명       AS 고객명
     , B.전화구분코드  AS 전화구분코드
     , B.전화번호     AS 전화번호
  FROM 고객 A
     , 고객전화번호 B
 WHERE A.고객번호 = B.고객번호(+) ;

 

SELECT A.고객번호     AS 고객번호
     , A.고객명       AS 고객명
     , B.전화구분코드  AS 전화구분코드
     , B.전화번호     AS 전화번호
  FROM 고객 A
     , 고객전화번호 B
 WHERE A.고객번호(+) = B.고객번호 ;

 

 

  • ANSI 조인 : 표준화된 조인 문법으로 모든 DBSM에서 사용 가능
    FROM에서 바로 조인 조건을 입력
    INNER JOIN 혹은 LEFT/RIGHT OUTER JOIN
    ON (조인 조건)

① INNER JOIN

 

② OUTER JOIN

 

 

※ 오라클 방식 조인 : WHERE에 조인 조건, 일반 조건을 모두 입력

ANSI 방식 조인 : FROM에 조인 조건, WHERE에 일반 조건 입력

 

③ FULL OUTER JOIN

오라클 방식에서 테이블 양쪽에 (+)를 두면 오류가 나지만, ANSI 조인의 FULL OUTER JOIN을 사용하면 가능하다

 

 

▶ 실습

 

① 직원 테이블과 직원 연락처 테이블을 직원ID 컬럼을 기준으로 조인

INNER JOIN / 동등(EQUAL) 조인 / 오라클 방식의 조인문법 사용

출력 컬럼 : 직원테이블 => [직원ID , 성별 , 나이] ,직원연락처테이블 => [연락처구분코드 , 연락처] 

SELECT A.직원ID
     , A.성별
     , A.나이
     , B.연락처구분코드
     , B.연락처
 FROM 직원 A
    , 직원연락처 B
 WHERE A.직원ID = B.직원ID ;

 

② 1번 문제에서 직원 ID가 'A0001'인 대상만 출력되도록 일반 조건 추가

SELECT A.직원ID
     , A.성별
     , A.나이
     , B.연락처구분코드
     , B.연락처
 FROM 직원 A
    , 직원연락처 B
 WHERE A.직원ID = B.직원ID
   AND A.직원ID = 'A0001';

 

③ 직원 테이블과 직원연락처 테이블을 조인, 직원연락처정보가 없는 직원의 정보도 출력

OUTER JOIN / 동등(EQUAL) 조인 / 오라클 방식의 조인 문법 사용

출력 컬럼 : 직원테이블 => [직원ID , 이름 , 연봉 , 부서ID ] , 직원연락처테이블 => [연락처구분코드 , 연락처]

SELECT A.직원ID
     , A.이름
     , A.연봉
     , A.부서ID
     , B.연락처구분코드
     , B.연락처
  FROM 직원 A
     , 직원연락처 B
 WHERE A.직원ID = B.직원ID(+);

 

④ 3번 문제를 ANSI 조인 사용해서 출력

SELECT A.직원ID
     , A.이름
     , A.연봉
     , A.부서ID
     , B.연락처구분코드
     , B.연락처
  FROM 직원 A LEFT OUTER JOIN 직원연락처 B
    ON A.직원ID = B.직원ID ;

 

⑤ 3번 문제에서 연락처가 없는 직원 정보만 출력

SELECT A.직원ID
     , A.이름
     , A.연봉
     , A.부서ID
     , B.연락처구분코드
     , B.연락처
  FROM 직원 A LEFT OUTER JOIN 직원연락처 B
    ON A.직원ID = B.직원ID
 WHERE B.연락처 IS NULL ;

 

⑥ 직원 , 직원연락처 , 직원주소 테이블을 모두 직원ID 라는 컬럼을 기준으로 조인

INNER JOIN / 동등(EQUAL) 조인 / 오라클 문법 방식 사용

3개의 테이블을 조인하고 나서 직원ID 가 'A0006' 인 직원의 정보만 출력되도록 추가 조건을 입력

출력할 컬럼 :  
직원 테이블 => [직원ID , 이름 , 입사일시 , 부서ID ] 
직원연락처 테이블 => [연락처구분코드 , 연락처 ] 
직원주소 테이블 => [ 주소구분코드 , 주소 ]

SELECT A.직원ID
     , A.이름
     , A.입사일시
     , A.부서ID
     , B.연락처구분코드
     , B.연락처
     , C.주소구분코드
     , C.주소
  FROM 직원 A
     , 직원연락처 B
     , 직원주소 C
 WHERE A.직원ID = B.직원ID
   AND B.직원ID = C.직원ID
   AND A.직원ID = 'A0006';

 

⑦ 6번 문제에 부서 테이블을 추가하여 조인하기

추가 컬럼 : 부서테이블 => [부서명]

SELECT A.직원ID
     , A.이름
     , A.입사일시
     , A.부서ID
     , B.연락처구분코드
     , B.연락처
     , C.주소구분코드
     , C.주소
     , D.부서명
  FROM 직원 A
     , 직원연락처 B
     , 직원주소 C
     , 부서 D
 WHERE A.직원ID = B.직원ID
   AND B.직원ID = C.직원ID
   AND A.부서ID = D.부서ID
   AND A.직원ID = 'A0006';

 

⑧ 직원 테이블과 부서 테이블을 조인하여 배정 받은 직원이 없는 부서를 출력

ANSI 조인 이용

SELECT A.부서ID
     , B.부서ID
     , B.부서명
  FROM 직원 A RIGHT OUTER JOIN 부서 B
    ON A.부서ID = B.부서ID
 WHERE A.부서ID IS NULL ;

 

⑨ 직원 중에 2020년 01월 01일 이후로 입사를 한 직원들의 휴대폰 정보를 출력

오라클 방식 조인 사용

출력 컬럼 :

직원 테이블 => [ 직원ID , 패스워드 , 이름 , 성별 , 입사일시 ]

직원연락처 테이블 => [연락처구분코드 , 연락처 ] 

SELECT A.직원ID
     , A.패스워드
     , A.이름
     , A.성별
     , A.입사일시
     , B.연락처구분코드
     , B.연락처
  FROM 직원 A
     , 직원연락처 B
 WHERE A.직원ID = B.직원ID
   AND TO_CHAR(A.입사일시, 'YYYY') >= 2020
   AND B.연락처구분코드 = '휴대폰' ;

 

⑩ 9번 문제를 ANSI 조인으로 사용

SELECT A.직원ID
     , A.패스워드
     , A.이름
     , A.성별
     , A.입사일시
     , B.연락처구분코드
     , B.연락처
  FROM 직원 A INNER JOIN 직원연락처 B
    ON A.직원ID = B.직원ID
 WHERE TO_CHAR(A.입사일시, 'YYYY') >= 2020
   AND B.연락처구분코드 = '휴대폰' ;

 

⑪ 직원 테이블과 직원주소 테이블의 조인을 이용해 주소를 가지고 있지 않은 직원을 출력

ANSI 조인 이용

출력 컬럼 : 직원 테이블 =>[ 직원ID, 이름 ]

SELECT A.직원ID
     , A.이름
  FROM 직원 A LEFT OUTER JOIN 직원주소 B
    ON A.직원ID = B.직원ID
 WHERE B.주소구분코드 IS NULL ;

 

⑫ 부서가 인사부인 직원들의 정보를 출력

오라클 조인 이용

출력 컬럼 : 직원 테이블 => [직원ID , 이름 , 입사일시 , 연봉 ], 부서 테이블 => [부서명] 

SELECT A.직원ID
     , A.이름
     , A.입사일시
     , A.연봉
     , B.부서명
  FROM 직원 A
     , 부서 B
 WHERE A.부서ID = B.부서ID
   AND B.부서명 = '인사부' ;

 

저작자표시 비영리 변경금지

'Oracle SQL Developer' 카테고리의 다른 글

SQL GROUP BY, HAVING, ORDER BY  (0) 2023.01.24
SQL WHERE  (1) 2023.01.23
SQL SELECT 연산, 내장형 함수  (0) 2023.01.09
SQL 기본  (0) 2023.01.08
DB 데이터 모델링(Data Modeling)  (0) 2023.01.05

관련글

댓글

티스토리툴바