SQL *= 문법 =* 문법 넌 누구냐? 외부쿼리? SQL-92 외부 조인 구문을 따르라



SQL Server 2005에서는 기본적으로 SQL-92 외부 조인 구문만 지원하므로, 앞으로 쿼리를 새롭게 작성하거나 수정할 때는 SQL-92 외부 조인 구문 형태로 작성하시기 바랍니다.

SQL Server 2000에는 SQL-92 외부 조인 구문과 WHERE 절에서 *= 또는 =* 연산자를 사용하여 외부 조인을 지정하는 레거시 구문을 모두 지원했습니다. 그렇지만 SQL Server 2000 온라인 설명서에 "SQL-92 구문은 레거시 Transact-SQL 외부 조인에서 발생할 수 있는 모호성이 없으므로 SQL-92 구문을 사용하는 것이 좋습니다." 라고 기술되어 있었고 마이크로소프트에서는 SQL-92 표준 외부 조인 구문의 사용을 권장해 왔습니다. 이 문구를 보면 레거시 T-SQL 외부 조인 구문은 모호한 경우가 있다는 건데, 그에 대해서는 다음에 나오는 스크립트를 테스트해 보시면 이해가 되실 겁니다.

그런데 SQL Server 2005에서는 데이터베이스 호환성 수준이 90인 상태에서는 아예 레거시 외부 조인 구문을 사용할 수 없게 변경되었습니다. 만일 90 호환성 모드에서 레거시 외부 조인 구문을 실행하면 다음과 같은 오류가 발생합니다.

메시지 4147, 수준 15, 상태 1, 줄 5
쿼리에서 ANSI 형식이 아닌 외부 조인 연산자("*=" 또는 "=*")를 사용합니다. 이 쿼리를 수정하지 않고 실행하려면 저장 프로시저 sp_dbcmptlevel을 사용하여 현재 데이터베이스의 호환성 수준을 80 이하로 설정하십시오. 가장 좋은 방법은 ANSI 외부 조인 연산자(LEFT OUTER JOIN, RIGHT OUTER JOIN)를 사용하여 쿼리를 다시 작성하는 것입니다. SQL Server의 다음 버전에서는 ANSI 형식이 아닌 조인 연산자는 역호환성 모드에서도 지원되지 않습니다.

 

그러면 레거시 T-SQL 외부 조인 구문에는 어떤 모호성이 있는지, 레거시 T-SQL 외부 조인 구문과 SQL-92 표준 구문 사이에는 실행 결과에 있어서 어떤 차이가 있는지는 다음 스크립트를 테스트해 보시면 쉽게 이해가 되실 겁니다.

CREATE DATABASE OUTERJOINTEST;
GO
USE OUTERJOINTEST
GO
CREATE TABLE OuterJoinTest1 (c1 int identity, c2 int, c3 int)
GO
CREATE TABLE OuterJoinTest2 (c1 int identity, c2 int, c3 int)
GO
SET NOCOUNT ON;
GO
INSERT OuterJoinTest1 (c2, c3) VALUES (1, 1);
INSERT OuterJoinTest1 (c2, c3) VALUES (2, 2);
INSERT OuterJoinTest1 (c2, c3) VALUES (3, 3);
GO
INSERT OuterJoinTest2 (c2, c3) VALUES (1, 1);
GO
-- SQL-92 외부 조인 구문
SELECT T1.c1, T1.c2, T1.c3 AS T1_c3, T2.c3 AS T2_c3
FROM OuterJoinTest1 T1 LEFT OUTER JOIN OuterJoinTest2 T2
ON T1.c1 = T2.c1
WHERE T2.c1 = 3
GO
/*
c1          c2            T1_c3         T2_c3         
------   --------  ---------  -----------
(0 ROW(S) AFFECTED)
*/
EXEC sp_dbcmptlevel OUTERJOINTEST, 80;
GO
-- 레거시 외부 조인
SELECT T1.c1, T1.c2, T1.c3 AS T1_c3, T2.c3 AS T2_c3
FROM OuterJoinTest1 T1, OuterJoinTest2 T2
WHERE T1.c1 *= T2.c1
AND T2.c1 = 3
GO
/*
c1          c2            T1_c3         T2_c3         
------   --------  ---------  -----------
1            1               1               NULL
2            2               2               NULL
3            3               3               NULL

(3 ROW(S) AFFECTED)
*/
 
USE master;
GO
DROP DATABASE OUTERJOINTEST;
GO


결론은 SQL-92 외부 조인 구문을 사용하라는 말이다. MS에서 SQL-92 외부 조인 구문을 권장했고 레거시 T-SQL은 모호하기 때문에 권장을 하는 것이다.
SQL Server 2005에서는 데이터베이스 호환성 수준이 90인 상태에서는 아예 레거시 외부 조인 구문을 사용할 수 없게 변경되었기 때문에 권장을 따르도록한다.
 

외부 조인 사용

내부 조인은 양쪽 테이블에서 조인 조건과 일치하는 행이 하나 이상 있을 때만 행을 반환하며 다른 테이블의 행과 일치하지 않는 행은 없앱니다. 그러나 외부 조인은 FROM 절에 지정된 하나 이상의 테이블이나 뷰에서 WHERE 또는 HAVING 검색 조건을 만족시키는 모든 행을 반환합니다. 왼쪽 외부 조인으로 참조되는 왼쪽 테이블에서 모든 행이 검색되고 오른쪽 외부 조인으로 참조되는 오른쪽 테이블에서 모든 행이 검색됩니다. 완전 외부 조인에서는 양쪽 테이블의 모든 행이 반환됩니다.

Microsoft® SQL Server™ 2000은 FROM 절에 지정된 외부 조인에 대해 다음 SQL-92 키워드를 사용합니다.

  • LEFT OUTER JOIN 또는 LEFT JOIN

  • RIGHT OUTER JOIN 또는 RIGHT JOIN

  • FULL OUTER JOIN 또는 FULL JOIN

SQL Server는 SQL-92 외부 조인 구문과 WHERE 절에서 *= 및 =* 연산자 사용에 따라 외부 조인을 지정하는 레거시 구문을 모두 지원합니다. SQL-92 구문은 레거시 Transact-SQL 외부 조인에서 발생할 수 있는 모호성이 없으므로 SQL-92 구문을 사용하는 것이 좋습니다.

왼쪽 외부 조인 사용

city 열을 기준으로 authors 테이블과 publishers 테이블을 조인할 경우를 생각해 봅시다. 결과에는 출판사가 있는 도시에 거주하는 저자(Abraham Bennet과 Cheryl Carson)만 포함됩니다.

같은 도시에 출판사가 존재하는지에 관계 없이 결과에 모든 저자가 포함되게 하려면 SQL-92 왼쪽 외부 조인을 사용합니다. 다음은 Transact-SQL 왼쪽 외부 조인의 쿼리와 결과입니다.

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
   ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

결과 집합은 다음과 같습니다.

au_fname             au_lname                       pub_name          
-------------------- ------------------------------ ----------------- 
Reginald             Blotchet-Halls                 NULL
Michel               DeFrance                       NULL
Innes                del Castillo                   NULL
Ann                  Dull                           NULL
Marjorie             Green                          NULL
Morningstar          Greene                         NULL
Burt                 Gringlesby                     NULL
Sheryl               Hunter                         NULL
Livia                Karsen                         NULL
Charlene             Locksley                       NULL
Stearns              MacFeather                     NULL
Heather              McBadden                       NULL
Michael              O'Leary                        NULL
Sylvia               Panteley                       NULL
Albert               Ringer                         NULL
Anne                 Ringer                         NULL
Meander              Smith                          NULL
Dean                 Straight                       NULL
Dirk                 Stringer                       NULL
Johnson              White                          NULL
Akiko                Yokomoto                       NULL
Abraham              Bennet                         Algodata Infosystems
Cheryl               Carson                         Algodata Infosystems

(23  적용됨)

LEFT OUTER JOIN은 publishers 테이블의 city 열에 일치하는 값이 있는지에 관계 없이 authors 테이블의 모든 행이 결과에 포함됩니다. 위의 결과에 나열된 저자들은 대부분 일치하는 데이터가 없으므로 pub_name 열에 null 값이 포함됩니다.

오른쪽 외부 조인 사용

city 열을 기준으로 authors 테이블과 publishers 테이블을 조인할 경우를 생각해 봅시다. 결과에는 출판사가 있는 도시에 거주하는 저자(Abraham Bennet과 Cheryl Carson)만 포함됩니다. SQL-92 오른쪽 외부 조인 연산자인 RIGHT OUTER JOIN은 첫 번째 테이블에 일치하는 데이터가 있는지에 관계 없이 두 번째 테이블의 모든 행이 결과에 포함되도록 합니다.

같은 도시에 출판사가 존재하는지에 관계 없이 결과에 모든 출판사가 포함되게 하려면 SQL-92 오른쪽 외부 조인을 사용합니다. 다음은 Transact-SQL 쿼리와 오른쪽 외부 조인의 결과입니다.

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
   ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

결과 집합은 다음과 같습니다.

au_fname             au_lname                 pub_name             
-------------------- ------------------------ -------------------- 
Abraham              Bennet                   Algodata Infosystems
Cheryl               Carson                   Algodata Infosystems
NULL                 NULL                     Binnet & Hardley
NULL                 NULL                     Five Lakes Publishing
NULL                 NULL                     GGG&G
NULL                 NULL                     Lucerne Publishing
NULL                 NULL                     New Moon Books
NULL                 NULL                     Ramona Publishers
NULL                 NULL                     Scootney Books

(9  적용됨)

조건자를 사용하여 외부 조인을 더 자세히 제한할 수 있습니다(예: 조인을 상수에 비교). 다음 예제에는 동일한 오른쪽 외부 조인이 포함되었지만 50부보다 적게 팔린 서적이 모두 제외됩니다.

USE pubs
SELECT s.stor_id, s.qty, t.title
FROM sales s RIGHT OUTER JOIN titles t
   ON s.title_id = t.title_id
   AND s.qty > 50
ORDER BY s.stor_id ASC

결과 집합은 다음과 같습니다.

stor_id qty    title                                                     
------- ------ --------------------------------------------------------- 
(null) (null) But Is It User Friendly?                                   
(null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior 
            Variations                  
(null) (null) Cooking with Computers: Surreptitious Balance Sheets       
(null) (null) Emotional Security: A New Algorithm                        
(null) (null) Fifty Years in Buckingham Palace Kitchens                  
7066   75     Is Anger the Enemy?                                        
(null) (null) Life Without Fear                                          
(null) (null) Net Etiquette                                              
(null) (null) Onions, Leeks, and Garlic: Cooking Secrets of the 
            Mediterranean                  
(null) (null) Prolonged Data Deprivation: Four Case Studies              
(null) (null) Secrets of Silicon Valley                                  
(null) (null) Silicon Valley Gastronomic Treats                          
(null) (null) Straight Talk About Computers                              
(null) (null) Sushi, Anyone?                                             
(null) (null) The Busy Executive's Database Guide                        
(null) (null) The Gourmet Microwave                                      
(null) (null) The Psychology of Computer Cooking                         
(null) (null) You Can Combat Computer Stress!                            

(18  적용됨)

조건자에 대한 자세한 내용은 WHERE를 참조하십시오.

완전 외부 조인 사용

조인 결과에 일치하지 않는 행을 포함하여 일치하지 않는 정보를 보유하려면 완전 외부 조인을 사용합니다. Microsoft® SQL Server™ 2000은 다른 테이블에 일치하는 값이 있는지에 관계 없이 양쪽 테이블의 모든 행을 포함하는 완전 외부 조인 연산자인 FULL OUTER JOIN을 제공합니다.

city 열을 기준으로 authors 테이블과 publishers 테이블을 조인할 경우를 생각해 봅시다. 결과에는 출판사가 있는 도시에 거주하는 저자만 포함됩니다(즉, Abraham Bennet과 Cheryl Carson). SQL-92 FULL OUTER JOIN 연산자는 테이블에 일치하는 데이터가 있는지에 관계 없이 양쪽 테이블의 모든 행이 결과에 포함되도록 합니다.

같은 도시에 출판사가 존재하는지에 관계 없이 결과에 모든 출판사와 저자가 포함되게 하려면 완전 외부 조인을 사용합니다. 다음은 Transact-SQL 완전 외부 조인의 쿼리와 결과입니다.

USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a FULL OUTER JOIN publishers p
   ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

결과 집합은 다음과 같습니다.

au_fname             au_lname                     pub_name             
-------------------- ---------------------------- -------------------- 
Reginald             Blotchet-Halls               NULL
Michel               DeFrance                     NULL
Innes                del Castillo                 NULL
Ann                  Dull                         NULL
Marjorie             Green                        NULL
Morningstar          Greene                       NULL
Burt                 Gringlesby                   NULL
Sheryl               Hunter                       NULL
Livia                Karsen                       NULL
Charlene             Locksley                     NULL
Stearns              MacFeather                   NULL
Heather              McBadden                     NULL
Michael              O'Leary                      NULL
Sylvia               Panteley                     NULL
Albert               Ringer                       NULL
Anne                 Ringer                       NULL
Meander              Smith                        NULL
Dean                 Straight                     NULL
Dirk                 Stringer                     NULL
Johnson              White                        NULL
Akiko                Yokomoto                     NULL
Abraham              Bennet                       Algodata Infosystems
Cheryl               Carson                       Algodata Infosystems
NULL                 NULL                         Binnet & Hardley
NULL                 NULL                         Five Lakes Publishing
NULL                 NULL                         GGG&G
NULL                 NULL                         Lucerne Publishing
NULL                 NULL                         New Moon Books
NULL                 NULL                         Ramona Publishers
NULL                 NULL                         Scootney Books

(30  적용됨)
 

T-SQL JOIN 과 ANIS-SQL JOIN의 결과가 다른 이유는?

 

문서로 남김. :



신고
  1. Favicon of http://diarix.tistory.com BlogIcon 외계인 마틴비밀방문자
    2010.05.19 23:31 신고 edit/del reply

    휴~ 조금이라도 이해하려고 노력하던 때가 있었었는데요.
    지금은 거의 외계어로 보입니다.
    전 이걸 이해하고 더군다나 이렇게 포스트로 발행하는 분들을 보면 저절로 존경심이 우러납니다.