DATOR


1.표준 SQL 개요 및 Standard JOIN ANSI 표준SQL


1.  STANDARD SQL 개요

 1970 Dr E, F, Codd 관계형 DBMS(Relational DB) 논문 발표

 1974 : IBM SQL 개발

 1979 Oracle 상용 DBMS 발표

 1980 Sybase SQL Server 발표 (이후 Sybase ASE로 개명)

 1983 IBM DB2 발표

 1986 : ANS/ISO SQL 표준 최초 제정 (SQL-86, SQL1)

 1992 : ANS/ISO SQL 표준 개정 (SQL- 92, SQL2)

 1993 1 MS SQL Server 발표 (Windows OS, Sybase Code 활용)

 1999 : ANSI/ISO SQL 표준 개정 (SQL-99, SQL3)

 2003 : ANSI/ISO SQL 표준 개정 (SQL-2003)

 2008 : ANSI/ISO SQL 표준 개정 (SQL-2008)

 

 초창기 SQL의 기본 기능을 정리했던 최초의 SQL-86표준과 관계형 DBMS의 폭발적인 정성기를 주도했던 ANSI/ISO SQL2(이하 SQL2) 세대를 지나면서 많은 기술적인 발전이 있었으나, SQL2의 경우 표준 SQL에 대한 명세가 부족한 부분이 있었고, DBMS벤더 별로 문법이나 사용되는 용어의 차이가 너무 커져서 상호 호환성이나 SQL학습 효율이 많이 부족한 문제가 발생, 이에 향후 SQL에서 필요한 기능을 정리하고 호환 가능한 여러 기준을 제정한 것이 1999년에 정해진 ANSI/SQL3(이하 SQL3)이다. 이후 가장 먼저 SQL3의 기능을 시현한 것이 Oracle 8i/9i 버전이라 할 수 있다. (현재 오라클 사의 공인 SQL교육은 ANSI 표준 SQL로 실시)

 이 후 2003 ANSI/ISO SQL기준이 소폭 추가 개정되었고, 현재 사용되는 데이터베이스는 대부분 SQL-2003표준을 기준으로 하고 있다.

 아직 벤더별로 일부 기능의 개발이 진행 중인 경우도 있고, 벤더 별 특이한 기술 용어는 여전히 호환이 안 되고 있지만, ANSI/ISO SQL표준을 통해 STANDARD JOIN을 포함한 많은 기능이 상호 벤치마킹하고 발전하면서 DBMS간에 평준화를 이루어 가고 있다고 볼 수 있다.

 대표적인 ANSI/ISO 표준 SQL의 기능은 다음 내용을 포함한다.

 

 - STANARD JOIN 기능 추가 (INNER, NATURAL, CROSS, OUTER )

 - SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들

 - ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능

 - WINDOW FUNCTION 같은 새로운 개념의 분석 함수 추가

 

2. STANDARD JOIN

 

ANSI/ISO SQL에서 규정한 STANDARD JOIN문법에서 기존 WHERE절을 사용하던 JOIN 방식과 차이점은 사용자가 기존 WHERE절의 검색 조건과 테이블 간의 JOIN 조건을 구분 없이 사용하던 방식을 그대로 사용할 수 있으면서, 추가된 선택 기능으로 테이블 간의 JOIN 조건을 FROM절에서 명시적으로 정의 할 수 있게 되었다.

 

 STANDARD JOIN을 이용하면 아래와 같은 장점을 지닐 수 있다.

 

 - JOIN조건(ON, USING) CHECK조건(WHERE)의 분리

 - 상세한 JOIN 방법 기술(INNER, NATURAL, CROSS, OUTER)

 - 이로 인한 가독 성 증가

 

(1) INNER JOIN

 INNER JOIN OUTER JOIN과 대비하여 내부 JOIN이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환한다. INNER JOIN 표시는 USING조건 절이나 ON 조건 절을 필수 적으로 사용해야 한다.

 USING조건 절과 ON조건 절은 아래와 같이 사용 가능하다.

 

EQUI-INNSER JOIN

 

SELECT

   employees.employee_id

   , employees.last_name

   , employees.department_id AS DID1

   ,departments.department_id AS DID2

   , departments.department_name

FROM employees INNER JOIN departments

ON (employees.department_id = departments.department_id)

order by 3 ;

 

EMPLOYEE_ID

LAST_NAME

DID1

DID2

DEPARTMANE_NAME

200

Whalen

10

10

Administration

201

Hartstein

20

20

Marketing

202

Fay

20

20

Marketing

114

Raphaely

30

30

Purchasing

119

Colmenares

30

30

Purchasing

  ..

106 rows selected.

 

*  ON 절 표시된 조인-조건에 따라 EMPLOYEES 테이블의 DEPARTMENT_ID 컬럼의 데이터와 DEPARTMENTS 테이블의 DEPARTMENT_ID 컬럼의 데이터가 같은 두 테이블의 행들을 합친 후(, EMPLOYEES 테이블의 DEPARTMENT_ID 컬럼의 데이터가 10인 행과 DEPARTMENTS 테이블의 DEPARTMENT_ID 컬럼의 데이터가 10인 행을 합친 후), SELECT 절에 명시된 컬럼들을 두 테이블로 부터 추출하여 레코드를 구성한다. 이 때 두 테이블의 행을 합치기(Joining) 위하여 조인-조건에 = 연산자가 사용되었고, 조인-조건을 만족하는 경우에만 두 테이블의 행을 합치기 때문에 EQUI-INNER JOIN이라고 한다. , 조인-조건을 만족하지 않는 행들은 합쳐 지지 않으며 결과에도 표시되지 않는다.

 

 아래의 EMPLOYEES 테이블에서 EMPLOYEE_ID 178 행은 DEPARTMENT_ID 컬럼에 값이 없기 때문에(NULL이기 때문에) 조인-조건에 의해 합칠 수가 없고, DEPARTMENTS 테이블에서 DEPARTMENT_ID 120 부터 270번까지 행들은 EMPLOYEES 테이블에 해당 값이 없기 때문에 조인-조건을 만족할 없어서 레코드로 표시되지 않는다.

 

EMPLOYEES 테이블 데이터

DEPARTMENT 테이블 데이터

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID

--------------------- ----------------- -------------------------

         200  Whalen                  10

         201  Hartstein                 20

         202  Fay                      20

         114  Raphaely                 30

        119  Colmenares               30

        115  Khoo                     30

        116  Baida                     30

        117  Tobias                    30

        118  Himuro                    30

...

        101  Kochhar                   90

        100  King                      90

        102  De Haan                  90

        206  Gietz                    110

        205  Higgins                  110

        178  Grant

 

107 rows selected.

DEPARTMENT_ID  DEPARTMENT_NAME

-------------------------- ---------------------------------

               10  Administration

               20  Marketing

               30  Purchasing

               90  Executive

           110  Accounting

           120  Treasury

           130  Corporate Tax

           140  Control And Credit

           150  Shareholder Services

           160  Benefits

...

           230  IT Helpdesk

           240  Government Sales

           250  Retail Sales

           260  Recruiting

           270  Payroll

 

27 rows selected.

 

 두개의 테이블을 조인할 시 아래와 같이 같은 이름의 컬럼명을 지정하지 않으면 에러가 발생하게 된다.

 

SELECT employee_id, employees.last_name, department_id

FROM employees INNER JOIN departments

ON (employees.department_id = departments.department_id) ;

SELECT employee_id, employees.last_name, department_id

*

ERROR at line 1:

ORA-00918: column ambiguously defined

 

테이블 ALIAS 사용

컬럼명 앞에 테이블 이름을 접두어를 사용하면, 구문이 길어 지는 , 아래의 실습 문장처럼 FROM 절에 명시되는 테이블 이름 옆에 [테이블 alias] 선언하고, 이를 테이블 이름 대신 사용하여 문장을 기술하면, 보다 간결한 구문 작성이 가능해 진다.

 

SELECT e.employee_id, e.last_name, e.department_id, d.department_name

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

order by 3 ;

 

EMPLOYEE_ID

LAST_NAME

DID1

DEPARTMANE_NAME

200

Whalen

10

Administration

201

Hartstein

20

Marketing

202

Fay

20

Marketing

. . .

106 rows selected.

 

 

3 개 이상 테이블의 조인

3개 이상의 조인을 수행할 경우 아래와 같이 조인을 순서대로 기술한다.

 

SELECT e.last_name, l.city

FROM hr.employees e JOIN hr.departments d ON (e.department_id = d.department_id)

JOIN hr.locations l ON (d.location_id = l.location_id)

WHERE e.employee_id IN (100,150,200);

 

LAST_NAME

CITY

King

Seattle

Tucker

Oxford

Whalen

Seattle

 

(주의) 3개 이상의 테이블을 조인할 시 아래와 같이 뒤에 기술한 조인 조건을 먼저 ON절에 기술할 시 아래와 같은 에러가 발생한다.

 

SELECT e.employee_id, e.last_name, l.city, d.department_name

FROM hr.employees e JOIN hr.locations l ON (d.location_id =l.location_id)

JOIN hr.departments d ON (e.department_id = d.department_id)

WHERE e.employee_id IN (100,150,200);

 

FROM hr.employees e JOIN hr.locations l ON (d.location_id =l.location_id)

ERROR at line 2:

ORA-00904: "D"."LOCATION_ID": invalid identifier

 

 

    (1-2) NON-EQUI-INNER JOIN

NON-EQUI-INNER JOIN

/* 1) 테이블 생성 */

CREATE TABLE hr.job_grades

(grade_level VARCHAR2(2) PRIMARY KEY

,lowest_sal NUMBER(10)

,highest_sal NUMBER(10)

);

/* 2) 데이터 입력 */

INSERT INTO hr.job_grades VALUES ('A', 1000, 2999) ;

INSERT INTO hr.job_grades VALUES ('B', 3000, 5999) ;

INSERT INTO hr.job_grades VALUES ('C', 6000, 9999) ;

INSERT INTO hr.job_grades VALUES ('D', 10000, 14999) ;

INSERT INTO hr.job_grades VALUES ('E', 15000, 24999) ;

INSERT INTO hr.job_grades VALUES ('F', 25000, 40000) ;

commit ;

 

아래 구문처럼 INNER JOIN 구문의 [ON] 조건에 "= 이외의 연산자" 사용된 경우에 이를 NONEQUI-Inner-Join이라고 한다.

 

SELECT e.last_name, e.salary, j.grade_level

FROM hr.employees e INNER JOIN hr.job_grades j

ON (e.salary between j.lowest_sal and j.highest_sal) ;

 

LAST_NAME

SALARY

GRADE

Olson

2100

A

Philtanker

2200

A

………………………

Feeney

3000

B

Cabrio

3000

B

………………………

Fay

6000

C

Ernst

6000

C

………………………

Baer

10000

D

Bloom

10000

D

………………………

King

24000

E

 

107 rows selected.

 

 

EMPLOYEES 테이블 데이터

JOB_GRADES 테이블 데이터

EMPLOYEE_ID  LAST_NAME  SALARY

---------------------- -------------------- --------------

          132       Olson    2100

          136    Philtanker   2200

          128      Markle    2200

...

          168       Ozer    11500

          147    Errazuriz   12000

          108   Greenberg   12000

          205     Higgins    12000

          201    Hartstein    13000

          146    Partners    13500

          145     Russell    14000

          102    De Haan    17000

          101     Kochhar   17000

          100        King    24000

107 rows selected.

 

 

GRADE_LEVEL LOWEST_SAL HIGHEST_SAL

----------- ---------- -----------

         A      1000        2999

         B      3000        5999

         C      6000        9999

         D     10000       14999

         E     15000       24999

         F     25000       40000

 

 

 

 (2) OUTER JOIN

  INNER JOIN과 대비하여 OUTER(외부) JOIN이라고 불리며, JOIN 조건에서 동일한 값이 없는 기준 집합의 행도 반환할 때 사용한다.

 아래 그림과 같이 INNER JOIN은 양쪽 테이블 모두 조인 되는 결과에 대해서만 결과를 보여준다.

 

 반면 OUTER JOIN은 조인에 성공한 결과는 물론, 조인에 성공하지 못한 결과에 대해서는 기준 집합의 데이터를 그대로 가져온다.

 STANDARD JOIN에서 OUTER JOINLEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 3가지 방법으로 OUTER JOIN을 사용할 수 있다.

 

(2-1) LEFT OUTER JOIN

 LEFT OUTER JOIN은 좌측에 선언된 테이블을 기준 집합으로 하여 OUTER JOIN을 수행한다.

 

LEFT OUTER JOIN

LEFT OUTER JOIN JOIN절에 LEFT OUTER JOIN이라는 명령어를 기술한다.

 

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.department_name

FROM hr.employees e LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

 

EMPLOYEE_ID

LAST_NAME

department_id

department_id

department_name

200

Whalen

10

10

Administration

202

Fay

20

20

Marketing

110

Chen

100

100

Finance

………………………..

108

Greenberg

100

100

Finance

206

Gietz

110

110

Accounting

205

Higgins

110

110

Accounting

178

Grant

 

 

 

 

...

107 rows selected.

 

 결과를 보면 조인에 성공한 데이터 외에도 Grant사원은 아직 부서가 정해지지 않은 사원이지만, 결과셋에 나오고 있다. 이 처럼 OUTER JOIN은 조인에 성공하지 않은 데이터도 데이터를 보여준다.

 LEFT OUTER JOIN은 조인 시 좌측에 기술 된, 즉 먼저 기술 된 데이터를 기준집합으로 하여 조인을 수행한다.

 

  (2-2) RIGHT OUTER JOIN

 

RIGHT OUTER JOIN

RIGHT OUTER JOIN JOIN절에 LEFT OUTER JOIN이라는 명령어를 기술한다.

 

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.department_name

FROM hr.employees e RIGHT OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

 

EMPLOYEE_ID

LAST_NAME

department_id

department_id

department_name

200

Whalen

10

10

Administration

201

Hartstein

20

20

Marketing

……………………………………..

 

 

 

120

Treasury

 

 

 

130

Corporate Tax

 

 

 

140

Control And Credit

 

 

 

150

Shareholder Services

 

 

 

160

Benefits

 

 

 

170

Manufacturing

 

 

 

180

Construction

 

 

 

190

Contracting

 

 

 

200

Operations

 

 

 

210

IT Support

 

 

 

220

NOC

 

 

 

230

IT Helpdesk

 

 

 

240

Government Sales

 

 

 

250

Retail Sales

 

 

 

260

Recruiting

 

 

 

270

Payroll

122 rows selected.

 

 결과를 보면 사원이 하나도 없는 120~270번까지의 부서 정보에 대한 정보를 보전하여 나왔다.

 RIGHT OUTER JOIN은 위의 결과와 같이 우측, 나중에 기술 된 테이블을 기준집합으로 하여 조인을 수행한다.

(2-3) FULL OUTER JOIN

 

FULL OUTER JOIN

FULL OUTER JOIN JOIN절에 FULL OUTER JOIN이라는 명령어를 기술한다.

 

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.department_name

FROM hr.employees e FULL OUTER JOIN departments d

ON (e.department_id = d.department_id) ;

 

EMPLOYEE_ID

LAST_NAME

department_id

department_id

department_name

198

OConnell

50

50

Shipping

199

Grant

50

50

Shipping

……………………………

178

Grant

 

 

 

……………………………

 

 

 

220

NOC

 

 

 

170

Manufacturing

 

 

 

240

Government Sales

 

 

 

210

IT Support

 

 

 

160

Benefits

 

 

 

150

Shareholder Services

 

 

 

250

Retail Sales

 

 

 

140

Control And Credit

 

 

 

260

Recruiting

 

 

 

200

Operations

 

 

 

120

Treasury

 

 

 

270

Payroll

 

 

 

130

Corporate Tax

 

 

 

180

Construction

 

 

 

190

Contracting

 

 

 

230

IT Helpdesk

122 rows selected.

 

 결과를 보면 LEFT OUTER JOIN에만 나왔던 Grant사원과, RIGHT OUTER JOIN에만 나왔던 사원이 하나도 없는 120~270번까지의 부서 정보에 대한 정보를 보전하여 나왔다.

 FULL OUTER JOIN은 위의 결과와 같이 양측 모두의 데이터를 보존하여 결과를 보여준다.

 

 (3) CROSS JOIN : 모든 행과 모든 행이 조인되는 곱집합 (CATESIAN PRODUCT)

 CROSS JON은 일반 집합 연산자의 PRODUCT의 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다.

 두 개의 테이블에 대한 CARTESIAN PRODUCT, 또는 CROSS PODUCT와 같은 표연으로 결과는 양 쪽 집합의 총 건수를 곱한 만큼 데이터만큼의 결과가 나오 게 된다.

 

CROSS JOIN

CROSS JOIN JOIN절에 CROSS JOIN이라는 명령어를 기술한다.

 

SELECT e.last_name, d.department_name

FROM hr.employees e CROSS JOIN departments d

order by 1,2 ;

 

 

LAST_NAME

DEPARTMENT_NAME

Abel

Accounting

Abel

Administration

Abel

Benefits

Abel

Construction

Abel

Contracting

Abel

Control And Credit

Abel

Corporate Tax

Abel

Executive

Abel

Finance

Abel

Government Sales

Abel

Human Resources

Abel

IT

Abel

IT Helpdesk

Abel

IT Support

Abel

Manufacturing

Abel

Marketing

Abel

NOC

Abel

Operations

Abel

Payroll

Abel

Public Relations

Abel

Purchasing

Abel

Recruiting

Abel

Retail Sales

Abel

Sales

Abel

Shareholder Services

Abel

Shipping

Abel

Treasury

Ande

Accounting

Ande

Administration

Ande

Benefits

Ande

Construction

Ande

Contracting

Ande

Control And Credit

Ande

Corporate Tax

Ande

Executive

Ande

Finance

Ande

Government Sales

Ande

Human Resources

Ande

IT

Ande

IT Helpdesk

Ande

IT Support

Ande

Manufacturing

Ande

Marketing

Ande

NOC

Ande

Operations

Ande

Payroll

Ande

Public Relations

Ande

Purchasing

Ande

Recruiting

Ande

Retail Sales

Ande

Sales

Ande

Shareholder Services

Ande

Shipping

Ande

Treasury

…………

 

 실행 결과에서 모든 사원 정보가 각각 DEPT의 모든 부서명과 연결이 되어서 4번의 조합이 출력이 되었음을 알 수 있다.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 (4) NATURAL JOIN : 키 값이 이름을 자동으로 정해 EQUI 조인

 NATURAL JOIN두 테이블의 컬럼의 이름이 동일하고 데이터 형식이 일치되는 모든 컬럼을 무조건 EQUI-INNER JOIN으로 수행한다.

 

NATURAL JOIN

NATURAL JOIN JOIN절에 NATURAL JOIN이라는 명령어를 기술한다.

 

SELECT e.employee_id, e.last_name, department_id, manager_id, d.department_name

FROM hr.employees e NATURAL JOIN departments d

order by 3,4 ;

 

EMPLOYEE_ID

LAST_NAME

DEPARTMENT_ID

MANAGER_ID

DEPARTMENT_NAME

202

Fay

20

201

Marketing

119

Colmenares

30

114

Purchasing

118

Himuro

30

114

Purchasing

……………..

109

Faviet

100

108

Finance

206

Gietz

110

205

Accounting

 

NATURAL JOIN은 컬럼이름이 같더라도 데이터 형식이 다르면 에러가 발생되며, 컬러명이 같고 각 컬럼의 데이터 형식이 같은 컬럼이 둘 이상이 존재할 경우, 특정 컬럼 하나만을 조인하는 것은 사용이 불가능하다.

 

 

Tag :

Leave Comments