[SQL] 4. JOIN

2016. 9. 12. 03:33

JOIN

정규화를 수행하면 하나의 테이블이 여러 개의 테이블들로 나누어져서 저장되고, 이때 여러 테이블에 흩어져 있는 데이터들을 조합해서 가져오는 기술.

Join 문법은Oracle 용 문법이 있고 모든 제품들에서 공통적으로 사용 가능한 표준 (ANSI ) join 문법이 있음.

ORACLE 문법

SQL> SELECT a.col1, b.col1         // 테이블별칭.컬럼명
2  FROM   table1 a, table2 b     // table1의 별칭 = a
3  WHERE a.col2 = b.col2;

ANSI 문법(표준)

SQL> SELECT a.col1, b.col1
2  FROM   table1 a [INNER] JOIN table2 b
3  ON a.col2 = b.col2;

INNER : default. 조인에 참여하는 모든 테이블에 존재하는 데이터만 출력(equi join, non-equi join)
OUTER : 한쪽 테이블에는 데이터가 있고 한쪽 테이블에 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 출력(outer join)

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

먼저 읽는 테이블을 선행 테이블 (driving table 또는 Inner table) 이라고 하고 뒤에 읽는 테이블을 후행 테이블 (driven table 또는 Outer table) 이라고 함. 그리고 선행 테이블은 조회할 데이터가 적은 테이블로 선택해야 속도 면에서 유리.

Cartesian Product (카티션 곱)

join 쿼리 중에 WHERE 절에 기술하는 join 조건이 잘못 되었거나 지정하지 않을경우 해당 테이블에 대한 모든 데이터를 전부 가져오게됨. (ANSI SQL 에서는 CROSS JOIN 이라고도 함)

예제

step1. 테스트용 테이블생성 및 데이터입력

SQL>CREATE TABLE cat_a (no NUMBER , name VARCHAR2(1));
Table created.
SQL>INSERT INTO cat_a VALUES (1,'A');
1 row created.
SQL>INSERT INTO cat_a VALUES (2,'B');
1 row created.

SQL>CREATE TABLE cat_b (no NUMBER, name VARCHAR2(1));
Table created.
SQL>INSERT INTO cat_b VALUES (1,'C');
1 row created.
SQL>INSERT INTO cat_b VALUES (2,'D');
1 row created.

SQL>CREATE TABLE cat_c (no NUMBER, name VARCHAR2(1));
Table created.
SQL>INSERT INTO cat_c VALUES (1,'E');
1 row created.
SQL>INSERT INTO cat_c VALUES (2,'F');
1 row created.

SQL>commit;
Commit complete.

step2. 생성된 테이블확인

SQL>SELECT * FROM cat_a;

        NO NAME
---------- -----
         1 A
         2 B

SQL>SELECT * FROM cat_b;

        NO NAME
---------- -----
         1 C
         2 D

SQL>SELECT * FROM cat_c;

        NO NAME
---------- -----
         1 E
         2 F

step3. 2개의 테이블로 정상적인 조인 수행

SQL>SELECT a.name, b.name
  2  FROM cat_a a, cat_b b
  3  WHERE a.no = b.no;

NAME  NAME
----- -----
A     C
B     D

step4. 2개의 테이블로 카티션 곱 생

SQL>SELECT a.name, b.name
  2  FROM cat_a a, cat_b b;         // where 조건 빠짐

NAME  NAME
----- -----
A     C
A     D
B     C
B     D

step5. 3개의 테이블로 정상적인 조인 수행

SQL>SELECT a.name, b.name, c.name
  2  FROM cat_a a, cat_b b, cat_c c
  3  WHERE a.no = b.no
  4  AND a.no = c.no;

NAME  NAME  NAME
----- ----- -----
A     C     E
B     D     F

step6. 3개의 테이블을 조회하되 조인 조건절은 2개 테이블에만 줘서 카티션 곱 생성

SQL>SELECT a.name, b.name, c.name
  2  FROM cat_a a, cat_b b, cat_c c
  3  WHERE a.no = b.no;              // cat_c 테이블 조건이 빠짐

NAME  NAME  NAME
----- ----- -----
A     C     E
A     C     F
B     D     E
B     D     F

테스트용 테이블에 1만 건 정도 insert 한 후 카티션 곱을 사용하면 1만 X 1만 = 1억 건의 데이터가 순식간에 생기게 되는데 이처럼 현업에서 쿼리 성능을 테스트할때 의도적으로 대량의 테이블을 생성해 테스트 하는 경우도 있음.

EQUI Join (등가 Join)

가장 많이 사용되는 조인으로 선행 테이블에서 데이터를 가져 온 후 조인 조건절을 검사해서 동일한 조건을 가진 데이터를 후행 테이블에서 꺼내 오는 방법. (조건절에서 Equal 연산자 (=) 를 사용해서 EQUI Join)

ex1) emp 테이블과 dept 테이블 조회하여 이름과 부서명 출력하기

Oracle Join

    SQL>SELECT e.empno, e.ename, d.dname
    2  FROM emp e, dept d
    3  WHERE e.deptno = d.deptno;

     EMPNO ENAME      DNAME
---------- ---------- --------------
      7782 CLARK      ACCOUNTING
      7839 KING       ACCOUNTING
      7566 JONES      RESEARCH
      7369 SMITH      RESEARCH
      7654 MARTIN     SALES
      7844 TURNER     SALES

ANSI Join

    SQL>SELECT e.empno, e.ename, d.dname
    2  FROM emp e JOIN dept d
    3  ON e.deptno = d.deptno;

컬럼 이름이 하나의 테이블에만 있을 경우에는 테이블 이름을 생략해도 자동으로 테이블 이름을 찾아서 실행 하기도 함. 그러나 양쪽 테이블에 모두 있는 컬럼일 경우는 반드시 테이블 이름을 적어야 함.

ex2) 학생 테이블(student)과 교수 테이블(professor)을 join 하여 학생의 이름과 지도교수 이름 출력하기

Oracle Join

SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno;

STU_NAME                       PROF_NAME
------------------------------ --------------------
James Seo                      Audie Murphy
Billy Crystal                  Angela Bassett
Richard Dreyfus                Angela Bassett
Tim Robbins                    Winona Ryder
Rene Russo                     Winona Ryder
Nicholas Cage                  Michelle Pfeiffer
Sandra Bullock                 Julia Roberts
Demi Moore                     Meryl Streep
Macaulay Culkin                Meryl Streep
Wesley Snipes                  Susan Sarandon
Danny Glover                   Nicole Kidman

ANSI Join

SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s JOIN professor p
  3  ON s.profno = p.profno;

Equi join 의 특성:
양쪽 테이블에 모두 데이터가 존재해야 결과가 나오는데 위의예제에서 5명의 학생은 지도 교수가 결정이 안된 상태라서 결과에서 빠졌음. 만약 이렇게 한쪽테이블에 없는 데이터까지 모두 출력하고 싶다면 Outer Join 을 사용하면 됨.

ex3) 학생 테이블(student)과 학과 테이블(department) , 교수 테이블(professor) 을 Join하여 학생의 이름과 학생의 학과이름, 학생의 지도교수 이름 출력하기

Oracle Join

SQL>SELECT s.name "STU_NAME", d.dname "DEPT_NAME", p.name "PROF_NAME"
  2  FROM student s, department d, professor p
  3  WHERE s.deptno1 = d.deptno
  4  AND s.profno = p.profno;

STU_NAME             DEPT_NAME                           PROF_NAME
-------------------- ----------------------------------- --------------------
James Seo            Computer Engineering                Audie Murphy
Billy Crystal        Computer Engineering                Angela Bassett
Richard Dreyfus      Computer Engineering                Angela Bassett
Tim Robbins          Multimedia Engineering              Winona Ryder
Rene Russo           Multimedia Engineering              Winona Ryder
Nicholas Cage        Multimedia Engineering              Michelle Pfeiffer
Sandra Bullock       Software Engineering                Julia Roberts
Macaulay Culkin      Electronic Engineering              Meryl Streep
Demi Moore           Electronic Engineering              Meryl Streep
Wesley Snipes        Electronic Engineering              Susan Sarandon
Micheal Keaton       Mechanical Engineering              Nicole Kidman
Danny Glover         Mechanical Engineering              Nicole Kidman

ANSI Join

SQL>SELECT s.name "STU_NAME", d.dname "DEPT_NAME", p.name "PROF_NAME"
  2  FROM student s JOIN department d
  3  ON s.deptno1 = d.deptno
  4  JOIN professor p
  5  ON s.profno = p.profno5

ANSI Join 의 경우는 쿼리의 2번과 3번 행의 조건으로 먼저 Join 을 수행 후 나온 결과 값을 가지고 4번과 5번 행의 조건으로 Join 을 수행. Join 테이블이 더 늘어날 경우 계속 JOIN~ON 조건을 추가 하면 됨.

ex4) student 테이블을 조회하여 1전공(deptno1)이 101번인 학생들의 이름과 각 학생들의 지도교수 이름 출력하기.

Oracle Join

SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno
  4  AND s.deptno1 = 101;

STU_NAME             PROF_NAME
-------------------- --------------------
James Seo            Audie Murphy
Richard Dreyfus      Angela Bassett
Billy Crystal        Angela Bassett

ANSI Join

SQL>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s JOIN professor p
  3  ON s.profno = p.profno
  4  AND s.deptno1 = 101;

3번 줄은 Join 을 수행하기 위한 조건이라서 Join 조건이라 부르고 4번 줄은 검색을 하는 용도로 사용되기 때문에 검색 조건이라고 부름. 위와 같이 Join조건과 검색조건이 동시에 있을 경우 당연히 검색 조건을 먼저 검색해서 데이터의 검색 범위를 줄여 놓고 Join 작업을 수행하게 된다. 검색 조건을 잘 활용해야 Join 작업의 속도가 빨라짐.

Non-Equi Join (비등가 Join)

같은 조건이 아닌 크거나 작거나 하는 경우의 조건으로 조회를 해야 할 경우.

ex1) Customer 테이블과 gift 테이블을 Join하여 고객별로 마일리지 포인트를 조회한 후 해당 마일리지 점수로 받을 수 있는 상품을 조회하여 고객의 이름과 받을 수 있는 상품 명 출력하기

Oracle Join

SQL> SELECT c.gname "C_NAME", TO_CHAR(c.point, '999,999') "POINT",
  2         g.gname "G_NAME"
  3  FROM customer c, gift g
  4  WHERE c.point BETWEEN g.g_start AND g.g_end;      // 비트윈보다 비교연산자가 더 성능좋음

C_NAME                         POINT    G_NAME
------------------------------ -------- ------------------------------
Bill Pullman                     65,000 Tuna Set
Mel Gibson                       73,000 Tuna Set
Brad Pitt                       110,000 Shampoo Set
Samuel Jackson                  153,000 Shampoo Set
Arnold Scharz                   265,000 Car wash Set
Ahnjihye                        273,000 Car wash Set
Jim Carrey                      315,000 Kitchen Supplies Set

ANSI Join

SQL> SELECT c.gname "C_NAME", TO_CHAR(c.point, '999,999') "POINT",
  2         g.gname "G_NAME"
  3  FROM customer c JOIN gift g
  4  ON c.point >= g.g_start
  5  AND c.point <= g.g_end;           //    비교연산자 사용

ex2) Student 테이블과 score 테이블 , hakjum 테이블을 조회하여 학생들의 이름과 점수와 학점 출력하기

Oracle Join

SQL> SELECT s.name "STU_NAME", r.total "SCORE", h.grade "GRADE"
  2  FROM student s, score r, hakjum h
  3  WHERE s.studno = r.studno
  4  AND r.total >= h.min_point
  5  AND r.total <= h.max_point;

STU_NAME                            SCORE GRA
------------------------------ ---------- ---
James Seo                              97 A+
Macaulay Culkin                        95 A0
Richard Dreyfus                        89 B+
Anthony Hopkins                        84 B0
Sandra Bullock                         83 B0
Bill Murray                            79 C+
Rene Russo                             78 C+
Demi Moore                             62 D

ANSI Join

SQL> SELECT s.name "STU_NAME", r.total "SCORE", h.grade "GRADE"
  2  FROM student s JOIN score r
  3  ON s.studno = r.studno
  4  JOIN hakjum h
  4  ON r.total >= h.min_point
  5  AND r.total <= h.max_point;

OUTER Join

Inner Join(등가,비등가조인)과는 반대로 한쪽 테이블에는 데이터가 있고 한쪽 테이블에 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 출력하게 하는 방법. (Oracle Join 과 ANSI Join 구문이 차이가 있음.)

ex1) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름을 출력. 단, 지도교수가 결정되지 않은 학생의 명단도 함께 출력하기

Oracle Join

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno(+);      //데이터가 없는쪽에 (+)표시 추가

STU_NAME                       PROF_NAME
------------------------------ --------------------
James Seo                      Audie Murphy
Richard Dreyfus                Angela Bassett
Billy Crystal                  Angela Bassett
Tim Robbins                    Winona Ryder
Rene Russo                     Winona Ryder
Nicholas Cage                  Michelle Pfeiffer
Sandra Bullock                 Julia Roberts
Macaulay Culkin                Meryl Streep
Demi Moore                     Meryl Streep
Wesley Snipes                  Susan Sarandon
Steve Martin                   Nicole Kidman
Micheal Keaton                 Nicole Kidman
Danny Glover                   Nicole Kidman
Daniel Day-Lewis               Jodie Foster
Bill Murray                    Jodie Foster
Anthony Hopkins
Charlie Sheen
Christian Slater
Sean Connery
Danny Devito

ANSI Join

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s LEFT OUTER JOIN professor p
  3  ON s.profno = p.profno;

Oracle Outer Join 의 경우는 데이터가 없는 쪽에 (+) 표시를 하지만 ANSI Outer Join 은 데이터가 존재하는 쪽에 표시를 함. ON 조건절중에 = 기호를 기준으로 왼쪽인 학생 데이터는 존재하고 교수는 존재하지 않으니까 LEFT.

ex2) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름 출력하기. 단 지도학생이 결정되지 않은 교수의 명단도 함께 출력

Oracle Join

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno(+) = p.profno
  4  ORDER BY s.name;

STU_NAME                       PROF_NAME
------------------------------ --------------------
Bill Murray                    Jodie Foster
Billy Crystal                  Angela Bassett
Daniel Day-Lewis               Jodie Foster
Danny Glover                   Nicole Kidman
Demi Moore                     Meryl Streep
James Seo                      Audie Murphy
Macaulay Culkin                Meryl Streep
Micheal Keaton                 Nicole Kidman
Nicholas Cage                  Michelle Pfeiffer
Rene Russo                     Winona Ryder
Richard Dreyfus                Angela Bassett
Sandra Bullock                 Julia Roberts
Steve Martin                   Nicole Kidman
Tim Robbins                    Winona Ryder
Wesley Snipes                  Susan Sarandon
                               Jessica Lange
                               Andie Macdowell
                               Meg Ryan
                               Holly Hunter
                               Sharon Stone
                               Whoopi Goldberg
                               Emma Thompson

ANSI Join

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s RIGHT OUTER JOIN professor p
  3  ON s.profno = p.profno;

ex3) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름 출력하기. 단 지도학생이 결정 안 된 교수 명단과 지도 교수가 결정 안된 학생 명단을 한꺼번에 출력

Oracle에서는 양쪽 데이터를 모두 출력하려면 두 Outer Join 을 각 각 수행 한 후 Union 을 사용하여 결과를 인위적으로 합쳐서 출력을 시키는 방법을 많이 사용함.(ANSI 에서는 FULL OUTER JOIN 사용하면됨)

Oracle Join

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno = p.profno(+)
  4  UNION
  5  SELECT s.name "STU_NAME", p.name "PROF_NAME"
  6  FROM student s, professor p
  7  WHERE s.profno(+) = p.profno;

ANSI Join

SQL> SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s FULL OUTER JOIN professor p
  3  ON s.profno = p.profno;

STU_NAME                       PROF_NAME
------------------------------ --------------------
James Seo                      Audie Murphy
Rene Russo                     Winona Ryder
Sandra Bullock                 Julia Roberts
Demi Moore                     Meryl Streep
Danny Glover                   Nicole Kidman
Billy Crystal                  Angela Bassett
Nicholas Cage                  Michelle Pfeiffer
Micheal Keaton                 Nicole Kidman
Bill Murray                    Jodie Foster
Macaulay Culkin                Meryl Streep
Richard Dreyfus                Angela Bassett
Tim Robbins                    Winona Ryder
Wesley Snipes                  Susan Sarandon
Steve Martin                   Nicole Kidman
Daniel Day-Lewis               Jodie Foster
Danny Devito
Sean Connery
Christian Slater
Charlie Sheen
Anthony Hopkins
                               Emma Thompson
                               Andie Macdowell
                               Whoopi Goldberg
                               Meg Ryan
                               Sharon Stone
                               Jessica Lange
                               Holly Hunter

OUTER Join을 조심해야 하는 이유
모든 데이터를 다 출력할 수 있기 때문에 좋을 것 같지만 이 Join방식은 DB 성능에 아주 나쁜 영향을 줄 수 있다. 왜냐면 예를들어, A 테이블과 B 테이블을 Outer Join 을 수행해서 A 테이블에 있는 데이터를 다 검색하는 경우 만약 A 테이블에 인덱스가 있어도 인덱스를 쓰지 않고 Full Scan 을 하기 때문. 그리고 아우터 조인이 발생할 경우 튜닝에서 아주 중요하게 생각하는 조인 순서가 고정되어 사용자의 뜻대로 변경할 수 없기 때문에 심각한 문제를 유발 할 수 있다.

작성한 SQL 이 속도가 늦을 경우 실행계획을 확인하는 방법을 예시를 통해 알아보자.

step1 : sys 계정으로 로그인해 plustrace 라는 권한을 생성

SCOTT> conn / as sysdba ;
SYS>@?/sqlplus/admin/plustrce.sql
SYS>
SYS>drop role plustrace;
drop role plustrace
          *
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SYS>create role plustrace;
Role created.

SYS>grant select on v_$sesstat to plustrace;
Grant succeeded.

SYS>grant select on v_$statname to plustrace;
Grant succeeded.

SYS>grant select on v_$mystat to plustrace;
Grant succeeded.

SYS>grant plustrace to dba with admin option;
Grant succeeded.

SYS>
SYS>set echo off

step2 : plustrace 라는 권한을 scott 에게 할당

SYS>GRANT plustrace TO scott;
Grant succeeded.

step3 : scott 으로 로그인 한 후 실행계획을 저장할 plan table 을 생성

SYS>CONN scott/tiger
Connected.
SCOTT>
SCOTT>@?/rdbms/admin/utlxplan.sql
Table created.

실행계획 확인할 준비 끝! 이제 쿼리를 수행하기 전에 실행계획을 사용하라고 설정을 한 후 쿼리를 수행하면 된다.

SCOTT>SET autot traceonly;
SCOTT>SELECT s.name "STU_NAME", p.name "PROF_NAME"
  2  FROM student s, professor p
  3  WHERE s.profno(+) = p.profno
  4  AND p.profno > 4004;

Execution Plan
----------------------------------------------------------
Plan hash value: 427587263

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    36 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN OUTER             |              |     1 |    36 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PROFESSOR    |     1 |    18 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0011128 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | STUDENT      |     2 |    36 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("S"."PROFNO"(+)="P"."PROFNO")
   3 - access("P"."PROFNO">4004)
   4 - filter("S"."PROFNO"(+)>4004)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        608  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

실행계획 해석하는 방법 :
먼저 윗줄과 아랫줄을 비교해서 오른쪽으로 들여쓰기 된 줄을 먼저 실행한다. 그리고 만약 윗줄과 아랫줄이 들여쓰기가 같다면 윗 줄부터 실행한다.
위 실행계획(Execution Plan)에서 id 가 0 번줄과 1번줄을 비교하면 1번줄을 먼저 해야 하고 1번 줄과 2번 줄을 비교하면 2번줄을 먼저 수행해야 하는데 2번 밑에 3번줄이 있기 때문에 2번줄을 수행하기 전에 3번 줄과 한번 더 비교한다. 2번 줄과 3번 줄은 들여쓰기가 같으므로 위 실행계획에서 가장 먼저 실행되는 것은 id 가 2 번인 줄이며 TABLE ACCESS FULL 은 테이블 전체를 다 읽었다라는 뜻이다.
실행계획의 Name 컬럼을 보면 PROFESSOR 테이블을 먼저 읽었고 Rows 컬럼을 보면 3건을 가져왔다는 것을 알 수 있다.(즉 Professor 테이블을 전부 읽어서 데이터를 3 건 찾아오고 student 테이블도 전부 읽어서 2 건의 데이터를 가져왔다는 의미.)
현재 professor 테이블의 profno 컬럼에 인덱스가 있는 상황에서 일부 교수들만 찾으라고 쿼리를 작성했지만 위 화면에서 아래에서 두 번째 줄을 보면 TABLE ACCESS FULL PROFESSOR 부분에서 테이블을 모두 읽었다고 알려주고 있다. (예를 들어 교수 테이블에 100 만 건 데이터가 있고 그 중에서 1 건만 읽으라고 시켜도 OUTER Join 은 필요 없는 100 만 건 데이터를 모두 다 읽고 그 중에서 1 건을 선택을 한다는 의미)

만약 아우터 조인이 아닌 일반 조인으로 작업을 했을 경우에 해당 테이블에 인덱스가 있을 경우 아래와 같이 인덱스를 사용하는 방법을 선택. 일반적으로 이 방법이 더 빠른 방법.

동일한 테이블에 동일한 조건으로 쿼리를 수행했을 때 OUTER Join 이 아닐경우는 속도가 빠른 방법을 선택하지만 OUTER Join 을 사용할 경우 속도가 늦어 진다는 것을 확인.

결론: OUTER JOIN은 쓰지말자.

SELF Join

원하는 데이터가 하나의 테이블에 다 들어 있을 경우(데이터를 가지고 있는 하나의 테이블을 메모리에서 별명을 두 개로 사용해서 호출하는 방법으로 2개의 테이블로 만든 후 일반적인 Join 작업을 수행함.)

ex) 사원들의 상사이름 출력하기

Oracle Join

SQL> SELECT e1.ename "ENAME", e2.ename "MGR_ENAME"
  2  FROM emp e1, emp e2
  3  WHERE e1.mgr = e2.empno;

ENAME      MGR_ENAME
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
WARD       BLAKE
MARTIN     BLAKE
ALLEN      BLAKE
MILLER     CLARK
CLARK      KING
JONES      KING
BLAKE      KING
SMITH      FORD

ANSI Join

SQL> SELECT e1.ename "ENAME", e2.ename "MGR_ENAME"
  2  FROM emp e1 JOIN emp e2
  3  ON e1.mgr = e2.empno;

연습문제

1) Emp2 테이블과 p_grade 테이블을 조회하여 사원들의 이름과 나이, 현재 직급 , 예상 직급 을 출력하시오. 예상 직급은 나이로 계산하며 해당 나이가 받아야 하는 직급을 의미함. 나이는 오늘(sysdate)을 기준으로 하되 trunc 로 소수점 이하는 절삭해서 계산.

ANSI


  SQL> SELECT e.name "NAME",
  2         TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 "AGE",
  3         e.position "CURR_POSITION",
  4         p.position "BE_POSITION"
  5  FROM emp2 e RIGHT OUTER JOIN p_grade p
  6  ON TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 BETWEEN s_age AND e_age;

ORACLE


  SQL> SELECT e.name "NAME",
  2         TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 "AGE",
  3         e.position "CURR_POSITION",
  4         p.position "BE_POSITION"
  5  FROM emp2 e, p_grade p
  6  WHERE TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 >= s_age 
  7  AND TRUNC(TO_CHAR(sysdate, 'YYYY')-TO_CHAR(e.birthday, 'YYYY'))+1 <= e_age;

2) customer 테이블과 gift 테이블을 Join하여 고객이 자기 포인트보다 낮은 포인트의 상품 중 한가지를 선택할 수 있다고 할 때 Notebook 을 선택할 수 있는 고객명과 포인트, 상품명을 출력하시오.

ANSI


  SQL> SELECT c.gname "CUST_NAME", c.point "POINT",
  2         g.gname "GIFT_NAME"
  3  FROM customer c JOIN gift g
  4  ON g.gname = 'Notebook'
  5  AND   c.point <= g.g_end
  6  AND   c.point >= g.g_start ;

ORACLE


  SQL> SELECT c.gname "CUST_NAME", c.point "POINT",
  2         g.gname "GIFT_NAME"
  3  FROM customer c, gift g
  4  WHERE g.gname = 'Notebook'
  5  AND   c.point <= g.g_end
  6  AND   c.point >= g.g_start ;

3) professor 테이블에서 교수번호, 교수이름, 입사일, 자신보다 입사일 빠른 사람 인원수를 출력하시오. 단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력.


  SQL> SELECT p.profno, p.name, p.hiredate,
  2         COUNT(p2.hiredate) "COUNT"
  3  FROM professor p, professor p2
  4  WHERE p2.hiredate < p.hiredate
  5  GROUP BY p.profno, p.name, p.hiredate
  6  ORDER BY 4;

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저

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

'Oracle > sql' 카테고리의 다른 글

[SQL] 6. DML (783)	2016.09.16
[SQL] 5. DDL과 Data Dictionary (773)	2016.09.15
[SQL] 3. 복수행함수(그룹함수) (153)	2016.09.05
[SQL] 2. 단일행함수(정규식표현) (315)	2016.09.05
[SQL] 2. 단일행함수(일반함수) (11)	2016.09.02

justdoit

[SQL] 4. JOIN

JOIN

Cartesian Product (카티션 곱)

예제

EQUI Join (등가 Join)

ex1) emp 테이블과 dept 테이블 조회하여 이름과 부서명 출력하기

ex2) 학생 테이블(student)과 교수 테이블(professor)을 join 하여 학생의 이름과 지도교수 이름 출력하기

ex3) 학생 테이블(student)과 학과 테이블(department) , 교수 테이블(professor) 을 Join하여 학생의 이름과 학생의 학과이름, 학생의 지도교수 이름 출력하기

ex4) student 테이블을 조회하여 1전공(deptno1)이 101번인 학생들의 이름과 각 학생들의 지도교수 이름 출력하기.

Non-Equi Join (비등가 Join)

ex1) Customer 테이블과 gift 테이블을 Join하여 고객별로 마일리지 포인트를 조회한 후 해당 마일리지 점수로 받을 수 있는 상품을 조회하여 고객의 이름과 받을 수 있는 상품 명 출력하기

ex2) Student 테이블과 score 테이블 , hakjum 테이블을 조회하여 학생들의 이름과 점수와 학점 출력하기

OUTER Join

ex1) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름을 출력. 단, 지도교수가 결정되지 않은 학생의 명단도 함께 출력하기

ex2) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름 출력하기. 단 지도학생이 결정되지 않은 교수의 명단도 함께 출력

ex3) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름 출력하기. 단 지도학생이 결정 안 된 교수 명단과 지도 교수가 결정 안된 학생 명단을 한꺼번에 출력

SELF Join

ex) 사원들의 상사이름 출력하기

연습문제

'Oracle > sql' 카테고리의 다른 글

티스토리툴바