조인 조건자 없음

성능 튜닝을 하면서 처음보는 그래픽 실행계획을 봤습니다. NL조인 실행계획에 X표시가 떠있었습니다. 

" 조인 조건자 없음" 이라는 설명과 함께 이건 뭐지? 라는 생각이 이승연(전 MSSQL MVP)님에게 물어봤더니 좋은 답변을 얻어서 정리하였습니다.

해당 내용에 도움을 주신 이승연님 대단히 감사드립니다.

우선 해당 실행계획을 보면 다음과 같습니다.

처음에 튜닝 할 당시에 이게 뭐하는 실행계획인지 잘 몰랐습니다. 조인 시에 Miss? / 인덱스 오류? 이런 저런 생각을 하였는데 정말 이유는 간단했습니다. 

조인의 조건자가 정말 없습니다. 그런데 상식적으로 조인을 걸고 ON 절을 해주지 않으면 조인이 안되지 않나? 이런 생각을 했는데... 잘 됩니다. 음...없다기 보다는

같은걸 2번 쓰거나 엉뚱한(?)것을 입력 했을때 쿼리가 잘 실행됩니다. 

그렇다면 뭘로 조인을 할까요? 그래서 테스트를 해봤습니다. 

준비한 테이블은 골키퍼 축구 선수들의 트레이닝 강의에 관한 내용입니다. 발 설계 및 발 쿼리니 이 자료는 참고만 부탁드립니다. 

테이블은 간단합니다. 4개의 테이블이 존재하며, 각각 선수정보(info) / 트레이닝항목(Class) / 수강현황(Register_info) / 코치(Teacher) 

개인적으로 수강현황은 N:N의 내용을 해소하기 위한 테이블로 만들었습니다. 한 트레이닝을 여러명이 들을수 있으며, 여러명이 여러개의 트레이닝을 받을 수 있기에...

Step 1 - 테이블 만들기

- 인덱스를 보고 이건 뭐야?라는 분들이 계실텐데 실행계획에서 NL조인에 X표기를 보기위한 것이니 너무 신경 쓰지 않으시길 바랍니다. ^^....

- 테이블은 위에 언급처럼 총 4개를 만듭니다. 선수정보/트레이닝항목/수강현황/코치 입니다. 

USE StudyDB
GO

create table info
(
 User_key			int identity(1,1)
,ID					varchar(10)
,Name				varchar(8)
,Hp				char(11)
constraint PK_info	primary key clustered (User_key)
)
go

create table Class
(
 Class_key		int identity(1,1)
,Class_name		varchar(20)
,Class_code		int
,teacher_key		int
constraint PK_Class	primary key clustered (Class_key)
)
go

create table Register_info
(
 register_key			int identity(1,1)
,User_key				int
,Class_key				int
,register_regdate	datetime
,flag					char(1)
constraint PK_Register_info primary key clustered (register_key)
)
go
create nonclustered index NC_1	on Register_info(User_key)
create nonclustered index NC_2	on Register_info(Class_key)
go

create table Teacher
(
 teacher_key		int
,teacher_name	varchar(8)
,Hp				char(11)
,teacher_class	varchar(30)
constraint PK_Teacher	primary key clustered (teacher_key)
)
go

Step 2 - 데이터 입력

- 이제 몇건의 데이터를 넣어보겠습니다.

insert into [dbo].[info]( ID, Name, Hp)  values ('AAAA','데헤아','01012341234')
insert into [dbo].[info]( ID, Name, Hp)  values ('BBBB','노이어','01023143532')
insert into [dbo].[info]( ID, Name, Hp)  values ('CCCC','카시야스','01033313643')
insert into [dbo].[info]( ID, Name, Hp)  values ('DDDD','체흐','01012346643')
insert into [dbo].[info]( ID, Name, Hp)  values ('EEEE','부폰','01023535036')
go
select * from [info]
go

insert into [dbo].[Class](Class_name, Class_code, teacher_key) values ('피지컬',1001,1)
insert into [dbo].[Class](Class_name, Class_code, teacher_key) values ('골키퍼',1002,2)
go
select * from [Class]
go

insert into [dbo].[Teacher] ([teacher_key],teacher_name, Hp, teacher_class) values(1,'무리뉴','01035530455','골키퍼 전문코치')
insert into [dbo].[Teacher] ([teacher_key],teacher_name, Hp, teacher_class) values(2,'클롭','01035340455','체력 전문코치')
go
select * from [Teacher]
go

insert into [dbo].[Register_info] ( User_key, Class_key, register_regdate, flag) values (1,1,getdate(),'Y')
insert into [dbo].[Register_info] ( User_key, Class_key, register_regdate, flag) values (1,2,getdate(),'Y')
insert into [dbo].[Register_info] ( User_key, Class_key, register_regdate, flag) values (2,1,getdate(),'Y')
insert into [dbo].[Register_info] ( User_key, Class_key, register_regdate, flag) values (3,2,getdate(),'Y')
go
select * from [Register_info]
go

Step 3 - 정상적인 조인

- 여기서 우리는 Select를 통해서 어떤 선수가 어떠한 트레이닝을 받고 있는지 확인 하고자 합니다.

select I.User_key,I.Name,C.Class_name
from info as I
inner join Register_info as R
on I.User_key = R.User_key
inner join Class as C
on R.Class_key = C.Class_key
order by User_key asc

- 실행계획에 아무런 지장이 없습니다. 

- 왜? on절도 정확히 명시되어있고, 정확한 Key로 묶었습니다.

- 그럼 선수가 받는 트레이닝은 무엇이며, 그 트레이닝의 담당자는 누구인지도 확인해 보겠습니다.

select I.User_key,I.Name,C.Class_name,T.teacher_name
from info as I
inner join Register_info as R
on I.User_key = R.User_key
inner join Class as C
on R.Class_key = C.Class_key
inner join Teacher as T
on C.teacher_key = T.teacher_key
order by User_key asc

- 역시 별다른 특이점이 없습니다. 왜? ON절을 정확히 명시했으니... 

- 그럼이제 NL 조인 부분에 X를 나타나게 해보겠습니다.

Step 4 - 조인 조건자 없음 

- 아까랑 똑같이 하돼 약간의 ON의 변화를 주겠습니다.

- 쿼리에서 표시한 부분의 ON절을 보면 2번째 Inner Join부분과 ON절이 똑같습니다. 그럼 Teacher 테이블은 어디갔어? ON절에 없잖아?

  뭐랑 조인하지? 하겠지만 조인이 됩니다.

select I.User_key,I.Name,C.Class_name,T.teacher_name
from info as I
inner join Register_info as R
on I.User_key = R.User_key
inner join Class as C
on R.Class_key = C.Class_key
inner join Teacher as T
on R.Class_key = C.Class_key -- 문제가 되는 부분
order by User_key asc

- NL 조인 부분에 X표시가 생겼습니다. 

  이 경고는 말그대로 조건자가 없음을 말합니다. 그렇다면 이것이 왜 문제가 되는가? 이것이 핵심입니다.

  바로 데이터가 틀리게 나오기 때문입니다. 우리가 바로 위에서 했던 정상 쿼리문과 결과를 비교해보겠습니다.

- 비교된 결과를 보면 다음과 같습니다.

- 그림을 통해서 확실히 보면 다음과 같습니다.

- 결과를 보면 이상한 점을 볼 수 있습니다. 

  각각의 데이터가 2배로 들어났습니다. 데이터도 이상합니다. 데헤아는 골키퍼,피지컬 1번씩 듣는데 2번씩 듣도록 되어있고, 노이어 / 카시야스는 수업이 총 1회씩인데

  골키퍼 2번 / 피지컬 2번씩 되어 있습니다. 이것은 무엇을 의미 할 까요?

- 3번째 Inner 조인까지 정확히 되었으나, 4번째 Teacher 테이블을 조인 시 Cross 조인을 하는 것임을 알 수 있습니다.

- 결과적으로 이것은 잘못된 데이터죠. 

  그렇다면 언제 이러한 실수를 많이 저지를 까요?

Step 5 - 우리는 언제 이러한 실수를 많이 하는가?

- 에이 누가 쿼리를 그렇게 짜요? 할 수 있습니다. 그런데 제가 현재 회사에서 튜닝을 하면 20건 중에 1건 정도는 이러한 결과를 보여줍니다. 왜 일까요?

- 개발자 분들은 주로 Where 조건을 이용해서 Join을 합니다. ON절을 잘 안쓰더군요.(물론 캐바캐입니다.)

  Where 조건에 넣음으로써 쿼리에서 결과가 맞다고 판단하고 그냥 넘어가는 것이죠. Where 조건으로 위의 내용을 재현하면 다음과 같습니다.

select I.User_key,I.Name,C.Class_name
from info as I
	,  Register_info as R
	,  Class as C
	,  Teacher as T
where  I.User_key = R.User_key
	and R.Class_key = C.Class_key
	and R.Class_key = C.Class_key
order by User_key asc

흔히들 Where 조건에 기재하면서 Where 조건에 조건을 잘못 입력하는 경우가 있습니다. 

위의 조건 처럼 사용하면 실수를 많이 범합니다. 하지만... 조인이 잘되니 아 ! 이상이 없구나 하고 넘기는 경우가 많죠.

이러한 실수를 방지하기 위해서 꼭 ON절을 이용한 조인을 해야합니다. 뭐.. 꼭은 아니지만 권고하는 것이죠.

감사합니다.