JOIN 문제 해결

앞서 살펴본 tables 간의 관계로 인한 Join 문제가 있었다.

1:N의 data가 생기면 어떤 data를 filtering해야 원하는 data가 나올까...

 

자! 이제 그 해결방법을 알아보자.

 

먼저 본인이 고민하고 고민해서 작성한 query는 아래와 같았다.

SELECT	SV.ServerName
	,SV.ServerIP
	,SV.AccountInfo = CASE
				WHEN EXISTS(SELECT * FROM dbo.ServersPermissions AS SVP INNER JOIN dbo.Users AS US ON (SVP.PermitTo = US.UserNo)
								WHERE SV.VMName = SVP.VMName AND US.PersonNo = @LoggedUser)
				THEN 'ID: ' + SV.AccountID + CHAR(10) + 'Password: ' + SV.Password
				ELSE 'No Permission'
			END
FROM	dbo.Servers AS SV;

 

위의 구문을 보면, SELECT하는 value 하나를 alias를 이용해서 case 문을 씌운 것이다.

case 문을 잘 보면, Servers의 Alias인 SV가 들어간 것을 볼 수 있다. 이게 좀 특이한 점이라고 생각된다. sub query 안에 main query의 alias가 들어가다니.. 여튼 무엇을 하려는지 유의깊게 봐보자.

 

ServersPermissions table과 Users table을 합쳐서 로그인한 유저가 권한이 있는지 없는지 확인한다.

권한 유무에 따라서, case로 나눠서 SV.AccountInfo에 다른 값을 넣어준다. 이것을 마지막으로 Servers list와 함께 꺼낸다.

 

그냥 보면, 음... 그냥 결과가 나오긴 하네. 라고 생각된다. 하지만 문제가 있다.

지금은 accountInfo 하나의 value 에 대해서만 sub query를 진행해서 크게 문제가 안 될 것처럼 보이지만.

accountInfo 말고도, permissions와 연관된 값을 더 꺼내야한다면... 매 value에다가 저 sub query를 해줘야하기 때문에

resource 낭비가 이만저만이 아닐 것이다.

 

그럼 어떻게 해야할까?

sub query에 쓰인 join을 한번만 사용할 순 없을까? 생각해보자.

SELECT  SV.ServerName
	,SV.ServerIP
	,SV.AccountInfo = CASE
			WHEN PUS.PermitTo IS NOT NULL THEN 'ID: ' + SV.AccountID + CHAR(10) + 'Password: ' + SV.Password
			ELSE 'No Permission'
			END
  	,PUS.PermitTo
FROM  dbo.Servers AS SV
  LEFT OUTER JOIN (
    SELECT  SVP.ServerName,
    		SVP.PermitTo
    FROM  dbo.ServersPermissions AS SVP
      INNER JOIN dbo.Users AS US ON (SVP.PermitTo = US.UserNo)
    WHERE  US.PersonNo = @LoggedUser
  ) AS PUS ON (SV.ServerName = PUS.ServerName)

뭔가 엄청나게 복잡해보인다. 하지만, 잘 뜯어보면 이해가 될 것이다.

처음 나오는 SELECT문은 위에서 봤던 sql과 같으니, 그 밑에 JOIN 부분을 봐보자.

Servers List를 모두 보여줘야하니까 LEFT OUTER JOIN을 한 것을 이해할 수 있을 것이다.

왼쪽에 있는 Table은 모두 보여주고, 오른쪽 Table은 있고 없고에 따라 NULL로 표기한다는 의미이다.

 

그럼 LEFT OUTER JOIN 안쪽의 SELECT문을 봐보자.

먼저, Permissions table과 user table을 join해서 현재 logged user가 어떤 server에 대해 permissions가 있는지 확인했다. 이 결과로 나온 table이 있는데, 이 table과 servers table을 left outer join 한 것이다.

 

이 구문의 장점은, 첫 번째 해결책과 달리 어떠한 column을 더 뽑더라도 이를 위한 query 수행이 더 반복되지 않는다는 것이다.

따라서, 이게 더 개선된 해결책이라고 할 수 있다.

---

추가로, 2번째 해결책에서 잠깐 table을 하나 만들었다고 언급했었다. 이를 잘 봐야한다.

다른 해결책으로, 임시 table을 만들어서 logged user와 server별 permissions이 뭐가 있는지 먼저 list up하고

그 다음 servers table과 left outer join을 하면 된다.

 

사실. 이건 다른 해결책이라기 보단, 2번째 해결책을 좀더 보기 쉽게 풀었다고 생각할 수 있다.

 

여튼 이번 문제는 이렇게 해결을 했다. 더 좋은 방법이 있으면 comments 남겨주시고, 저도 다른 방법이 생각나면 추가로 쓰겠습니다.