숫자를 LIKE 컬럼처럼 > db

숫자를 LIKE컬럼처럼.

안녕하세요. 유일환입니다.

온르은 개발하면서 사용할 수 있는 간단한 팁하나 적어볼까 합니다.

프로시저의 매개변수에는 문자형, 숫자형등이 올 수 있습니다.
대부분의 사용자들의 요구사항에 맞추어 개발을 하다보면, 요구사항에 따라 SQL이 하나둘씩 늘어나게 됩니다.
예를 들어 Northwind의 Orders테이블에 대한 조회를 생각해 보지요.
사용자가 처음에는 CustomerID에 대한 조회만을 요청을 했습니다.
그렇다면 다음과 같이 SQL을 구현할 수 있습니다.

 DECLARE @CustomerID  nchar(5)
 SET @CustomerID = 'VINET'

 SELECT *
 FROM Orders
 WHERE CustomerID = @CustomerID

여기에 하나의 요구사항이 추가가 되어서 CustomerID를 입력하지 않은 경우는 모든 데이터를 보여주세요,
그리고 V만 입력하면 V로 시작하는 모든 주문을 보여주세요.
이와 같은 요구사항은 간단하게 LIKE로 처리가 가능합니다.

 DECLARE @CustomerID  nchar(5)
 SET @CustomerID = 'V'

 SELECT *
 FROM Orders
 WHERE CustomerID LIKE RTRIM(@CustomerID) + '%'

이런식으로 계속해서 조회 조건이 추가가 되어 가겠죠.
이번에는 Employee에 대한 조회 조건이 추가가 되었습니다.
사용자는 화면에서 Employee이름을 선택을 하지만 내부적으로는 EmployeeID를 조회 조건으로 받게 됩니다.
그러므로 SQL은 다음과 같이 변경이 될 것입니다.

 DECLARE @CustomerID  nchar(5)
 DECLARE @EmployeeID int
 SET @CustomerID = 'V'
 SET @EmployeeID = 1

 SELECT *
 FROM Orders
 WHERE CustomerID LIKE RTRIM(@CustomerID) + '%'
 AND EmployeeID = @EmployeeID

여기까지는 문제가 될것이 없습니다.
하지만, EmployeeID를 선택하지 않은 경우는 CustomerID 조건에 맞는 데이터만 보여주세요.
라는 요구조건이 어떻게 변경을 할 것인지 생각을 해보셔야 합니다.
왜냐하면 EmplyoeeID는 숫자형이기 때문에 LIKE조건을 사용할 수 없기 때문입니다.
즉 외부에서 선택된 직원이 없을때, 0이나 NULL을 넘기게 될텐데 그것에 맞게 SQL을 변경해야 합니다.
가장 쉬운 방법은 SQL을 두 개로 분리하는 것입니다.

 DECLARE @CustomerID  nchar(5)
 DECLARE @EmployeeID int
 SET @CustomerID = 'V'
 SET @EmployeeID = 0

 IF @EmployeeID = 0
 BEGIN
  SELECT *
  FROM Orders
  WHERE CustomerID LIKE RTRIM(@CustomerID) + '%'
 END
 ELSE
 BEGIN
  SELECT *
  FROM Orders
  WHERE CustomerID LIKE RTRIM(@CustomerID) + '%'
  AND EmployeeID = @EmployeeID
 END

위와 같이 구현을 하면 성능은 최적입니다. 하지만 거의 유사한 SQL이 하나더 만들어져야 한다는 번거로움이 있습니다.
위와 같이 간단한 SQL의 경우는 문제가 되지 않습니다. 하지만 SQL이 굉장히 복잡하다면 동일한 SQL을 하나 더 만들어서 조건만
다르게 준다는 것이 굉장히 골치아픈 일이란 걸 아실겁니다.
그렇다면 위 문장을 UNION ALL을 이용해서 하나의 문장으로 만들어 볼 수 도 있을 겁니다.
 DECLARE @CustomerID  nchar(5)
 DECLARE @EmployeeID int
 SET @CustomerID = 'V'
 SET @EmployeeID = 0

 SELECT *
 FROM Orders
 WHERE CustomerID LIKE RTRIM(@CustomerID) + '%'
 AND @EmployeeID = 0
 UNION ALL
 SELECT *
 FROM Orders
 WHERE CustomerID LIKE RTRIM(@CustomerID) + '%'
 AND EmployeeID = @EmployeeID
 AND @EmployeeID <> 0
이와 같이 하면 SQL문장은 하나지만 이전 방법과 마찬가지로 동일한 2개의 SQL을 UNION ALL로 묶은것밖에 별다른 차이가 없습니다.
이것 역시 성능은 최적에 가깝습니다.
이 방법 보다 SQL을 간단하게 변경할 수 있는 방법은 전체를 선택한 경우 NULL을 넘겨주고 조건에서 ISNULL을 사용하는 것입니다.
 DECLARE @CustomerID  nchar(5)
 DECLARE @EmployeeID int
 SET @CustomerID = 'V'
 SET @EmployeeID = NULL

 SELECT *
 FROM Orders
 WHERE CustomerID LIKE RTRIM(@CustomerID) + '%'
 AND EmployeeID = ISNULL(@EmployeeID, EmployeeID)

이 SQL은 NULL의 특성과 ISNULL함수를 사용해서 두 개의 SQL을 사용하지 않고 구현이 가능하도록 처리가 되어습니다.
하지만, 이 SQL의 치명적인 약점은 EmployeeID를 자신의 EmployeeID와 계속해서 비교를 한다는 것입니다.
이 상황이 성능에 안 좋은 결과를 초래할 수 있습니다. 그러므로 이 방법은 추천 방법은 아닙니다.

마지막으로 제가 자주 사용하는 방법입니다.
 DECLARE @CustomerID  nchar(5)
 DECLARE @EmployeeID int
 SET @CustomerID = 'V'
 SET @EmployeeID = 0

 SELECT *
 FROM Orders
 WHERE CustomerID LIKE RTRIM(@CustomerID) + '%'
 AND EmployeeID >= CASE WHEN @EmployeeID = 0 THEN 0 ELSE @EmployeeID END
 AND EmployeeID <= CASE WHEN @EmployeeID = 0 THEN 999999 ELSE @EmployeeID END
이 방법은 @EmployeeID의 값이 0일 경우는 CASE문을 사용해서 최소값을 0으로 최대값을 999999로 치환해서 EmployeeID에 구간범위 조건을 주는 방법입니다.
이 방법 동일한 SQL을 두번 사용하는 것을 피해 갈 수 있습니다.
하지만, 이 문장 역시 성능에 있어서 2개의 SQL을 사용한 경우보다 상대적으로 좋은 성능을 내기는 어렵습니다.
물론, 인덱스의 설정상태에 따라 틀리겠지만, EmployeeID에 인덱스가 있다면, EmployeeID의 인덱스를 제대로 사용하기가 쉽지 않다는 단점이 있습니다.
(전체 조회일 경우는 어차피 사용하지 않아도 되지만요.)

이와 같은 4가지 정도의 방법이 있습니다.
어떤 방법을 선택해서 사용해야 할지는 그때 그때 상황에 따라 틀립니다.
3번째나 4번째 방법을 선택해서 사용했을 때는 복잡한 SQL을 두 번 사용하는 것을 피할 수 있으나 성능이 최적이 아니라는 것을 감소해야 합니다.
그러므로 이 방법을 사용했을 때 성능에 문제가 없는지를 먼저 고려해 봐야 겠지요.

1번째나 2번째 방법을 사용할 때는 각각 상황에 맞도록 SQL의 실행계획이 만들어지므로 성능은 최적화 되어 있으나,
굉장히 복잡한 SQL의 경우, 새로운 요구사항이 생길때마다 2개의 SQL을 동시에 수정해야 하므로 유지보수가 어려워지겠죠.
이와 같이 처리한 경우는 사용자들의 요구사항이 추가될 때마다. 동일한 SQL이 증가가 되어야 겠지요.

이 방법 외에 마지막 방법은 동적 SQL을 만들어서 사용을 하는 방법입니다.
조건이 무엇인가에 따라 WHERE 절의 조건을 문자열로 만들어서 나중에 조건을 추가하거나 빼서 최종 SQL문자열을 만들어서 실행하는 방법입니다.
하지만 이와 같은 방법 역시 복잡한 SQL의 경우 유지보수가 어렵습니다.
문자열로 찢어져 있는 SQL들을 결합해서 원문의 SQL을 만들어야만 SQL의 내용을 이해하고 수정할 수 있기 때문이죠.

어떤 것을 선택하든, 그 상황에 알맞게 최적의 방법을 선택하기를 바랍니다.
읽어주셔서 감사합니다. 수고하세요.^^


출처 : https://www.dbguide.net/dbqa/dbqa120001.jsp?mode=view&divcateno=244&divcateno_=244&pg=2&idx=808