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의 결과가 다른 이유는?
문서로 남김. :
SQL-92 외부조인권장.docx