4장 데이터 필터링과 검색

하나 이상의 테이블에서 데이터를 검색해 유용한 정보로 변환할 때 SQL로 할 수 있는 가장 중요한 작업은 원하는 데이터를 찾거나 원하지 않는 데이터를 걸러 내는 필터링일 것이다.

 

BETTER WAY 22 관계 대수와 이를 SQL로 구현하는 방법을 이해하자

관계형 모델(관계, 튜플, 속성)로 수행할 수 있는 관계 대수 연산에는 선택(제한), 추출, 조인, 교집합, 카티전 곱, 합집합, 나누기, 차집합의 8가지가 있다.

 

1. 선택(제한)

선택(제한)은 로우를 선택한 후 필터링해서 원하는 데이터 집합을 얻는 것이다. FROM 절에서 원하는 데이터 집합의 원천을 정의한 후 WHERE나 HAVING 절을 사용해 반환되는 로우를 걸러 낸다.

선택 연산 수행 결과

2. 추출

추출(Project)은 DBMS가 반환하는 컬럼과 표현식을 선택하는 연산을 의미한다. 반환하는 컬럼을 정의하기 위해 집계 함수, GROUP BY 절을 포함한 SELECT 절로 구현한다. 선택된 집합이 일련의 컬럼과 로우하고 할 때, 추출 연산은 컬럼을 반환한다. 추출 연산 후 선택되지 않은 컬럼 값을 사용해서 반환될 로우를 걸러 내는 것은 선택 작업의 몫이다.

추출 연산 수행 결과

3. 조인

조인은 키 값으로 연결된 데이터 집합이나 관련된 테이블을 연결하는 것이다. 모든 관계(테이블)는 반드시 유일한 식별자(기본키)를 가져야 하며 관계가 형성된 테이블에는 유일한 식별자의 복사본(외래키)을 가져야 한다. FROM 절에 원하는 JOIN 키워드(INNER JOIN, OUTER JOIN, ...)를 사용한다.

4. 교집합

교집합은 컬럼이 동일한 두 집합에서 수행된다. 교집합의 결과는 이런 각 컬럼 값과 일치하는 모든 로우를 반환한다. 한 데이터 집합에서 선택과 추출 작업을 수행한 후 첫 번째 데이터 집합과 두 번째 집합에서 INTERSECT를 명시하면 된다. 교집합 연산을 지원하지 않은 경우 INNER JOIN을 통해 동일한 결과를 얻을 수 있다. 

-- 교집합 연산 쿼리
SELECT C.CustFirstName, C.CustLastName
FROM Customers AS C
WHERE C.CustomerID IN
  (...)
INTERSECT
SELECT C2.CustFirstName, C2.CustLastName
FROM Customers AS C2
WHERE C2.CustomerID IN
  (...);

 

5. 카티전 곱

카티전 곱(Catesian Product)은 한 데이터 집합에 있는 모든 로우와 두 번째 데이터 집합에 있는 모든 로우를 결합한 결과를 반환한다. 반환 결과의 로우 개수 = 첫 번째 집합의 전체 로우 * 두 번째 집합의 전체 로우라서 '곱'이라고 표현한다. CROSS JOIN으로 명시해야 한다.

 

6. 합집합

합집합 연산은 컬럼의 유형이 동일한 두 데이터 집합을 병합하는 것으로 UNION 키워드를 사용한다. 한 데이터 집합에서 선택과 추출을 한 후 UNION 키워드를 추가하고 두 번째 데이터 집합에서 선택과 추출을 한다.

UNION ALL을 사용하면 DBMS가 두 집합에서 발견된 중복 로우를 제거하지 않기 때문에 중복된 로우를 찾아낼 수 있다.

 

7. 나누기

한 데이터 집합을 다른 집합으로 나누면 제수(나누는 수) 데이터 집합의 모든 멤버를 포함하는 피제수(나눠지는 수) 데이터 집합에 있는 로우들이 반환된다. 예를 들어, 모든 지원자 집합을 자격 요건 집합으로 나눈 결과는 자갹 요건을 만족하는 지원자 집합이 된다.

 

8. 차집합

차집합 역시 컬럼이 동일하거나 유사한 두 집합을 대상으로 작업해야 한다. EXCEPT 키워드로 사용이 가능하다.(오라클은 MINUS, MySQL은 OUTER JOIN으로 유사하게 구현 가능)

-- 차집합 연산 쿼리
SELECT C.CustFirstName, C.CustLastName
FROM Customers AS C
WHERE C.CustomerID IN
  (...)
EXCEPT
SELECT C2.CustFirstName, C2.CustLastName
FROM Customers AS C2
WHERE C2.CustomerID IN
  (...)

 

주요 DBMS 엔진은 SQL 쿼리를 최적화하는 데 관계 대수를 사용하기 때문에 관계 대수를 잘 이해하면 DBMS 엔진이 쿼리를 실행 계획으로 변환하는 방법을 이해하는 데 도움이 될 것이다.

 

 

BETTER WAY 23 일치하지 않거나 누락된 레코드를 찾아내자

종종 데이터베이스에 없는 데이터를 조회하는 일이 있다. 예를 들어 특정 제품이 얼마나 잘 팔리는지 파악하기 위해 판매 주문 데이터를 추출하는데, 어떤 제품은 주문 내역조차 없을 수 있다.

 

이를 해결하기 위해서는 판매된 제품 목록을 뽑은 후, NOT IN 연산자를 사용해 여기에 포함되지 않은 제품을 조회하면 될 것이다. 하지만 NOT IN 연산자는 일반적으로 효율적인 접근 방법은 아니다.

NOT IN 보다는 NOT EXISTS 연산자가 더 빠르다.

SELECT P.ProductNumber, P.ProductName
FROM Products AS P
WHERE NOT EXISTS 
  (SELECT * 
   FROM Order_Details AS OD 
   WHERE OD.ProductNumber = P.ProductNumber);

또 다른 방법은 '좌절성 조인'이라고 하는, LEFT JOIN을 사용하고 WHERE 절에서 널 값을 찾는 것이다. 

-- 좌절성 조인
SELECT P.ProductNumber, P.ProductName
FROM Products AS P LEFT JOIN Order_Details AS OD
  ON P.ProductNumber = OD.ProductNumber
WHERE OD.ProductNumber IS NULL;

DBMS 엔진마다 선호하는 방식이 다르고(SQL Server는 EXISTS 선호), 각자 처한 상황이 다르기 때문에 DBMS 쿼리 분석기를 사용하여 적절한 방법을 선택하자.

 

 

BETTER WAY 24 CASE로 문제를 해결해야 할 때를 파악하자

SELECT 절, WHERE 절, HAVING 절 등 값 표현식을 사용하는 곳이라면 어디에나 CASE를 사용할 수 있다. 

CASE 문은 단순형과 검색형 두 가지 형태로 사용된다.

 

단순형 CASE 문은 값 표현식이 다른 값 표현식과 같은지 비교해 값 표현식 하나를 반환한다.

-- 단순형 CASE 표현식 예시
CASE Students.Gender
  WHEN 'M' THEN 'Male'
  WHEN 'F' THEN 'Female'
  ELSE 'Unknown' END
  
CASE Readings.Measure WHEN 'C' 
  THEN (Temperature * 9 / 5) + 32 
  ELSE Temperature
END

 

동등 검사 이외의 작업이나 표현식 두 개 이상을 검사하고 싶다면 검색형 CASE를 사용한다. CASE 키워드 다음에 하나 이상의 검색 조건이 있는 WHERE 절을 사용하면 된다. 

-- 1
CASE WHEN Students.Gender = 'M' THEN 'Mr.'
  WHEN Students.MaritalStatus = 'S' THEN 'Ms.'
  ELSE 'Mrs.' END

-- 2
SELECT Products.ProductNumber, Products.ProductName, 
    CASE WHEN (SELECT SUM(QuantityOrdered)
                FROM Order_Details 
                WHERE (Order_Details.ProductNumber = Products.ProductNumber)) <= 200 
            THEN 'Poor' 
        WHEN (SELECT SUM(QuantityOrdered)
                FROM Order_Details 
                WHERE (Order_Details.ProductNumber = Products.ProductNumber)) <= 500 
            THEN 'Average' 
        WHEN (SELECT SUM(QuantityOrdered)
                FROM Order_Details 
                WHERE (Order_Details.ProductNumber = Products.ProductNumber)) <= 1000 
            THEN 'Good' 
        ELSE 'Excellent' END
FROM Products;

-- 3
CASE Staff.Title 
    WHEN 'Instructor' THEN ROUND(Salary * 1.05, 0) 
    WHEN 'Associate Professor' THEN ROUND(Salary * 1.04, 0) 
    WHEN 'Professor' THEN ROUND(Salary * 1.035, 0) 
    ELSE Salary END

 

 

BETTER WAY 25 다중 조건 문제를 해결하는 기법을 파악하자

관계를 맺은 테이블에 복합 조건을 적용해 문제를 해결하는 것은 꽤 복잡하다. 예를 들어 '스케이트보드를 주문한 고객과 헬멧, 무릎 보호대, 장갑까지 주문한 고객'을 찾으라는 문제를 해결하려면 Orders, Order_Details 테이블에 조건을 적용하고 동시에 Customers 테이블에서 고객 정보를 추출해야 한다.

 

👇🏻 스케이트보드를 주문한 고객과 헬멧, 무릎 보호대, 장갑까지 주문한 고객을 찾는 쿼리

SELECT C.CustomerID, C.CustFirstName, C.CustLastName
FROM Customers AS C 
WHERE EXISTS
  (SELECT Orders.CustomerID
  FROM Orders INNER JOIN Order_Details
  ON Orders.OrderNumber = Order_Details.OrderNumber
  INNER JOIN Products 
  ON Products.ProductNumber = Order_Details.ProductNumber
  WHERE Products.ProductName = 'Skateboard'
  AND Orders.CustomerID = C.CustomerID)
AND EXISTS
  (SELECT Orders.CustomerID
  FROM Orders INNER JOIN Order_Details
  ON Orders.OrderNumber = Order_Details.OrderNumber
  INNER JOIN Products 
  ON Products.ProductNumber = Order_Details.ProductNumber
  WHERE Products.ProductName = 'Helmet'
  AND Orders.CustomerID = C.CustomerID)
AND EXISTS
  (SELECT Orders.CustomerID
  FROM Orders INNER JOIN Order_Details
  ON Orders.OrderNumber = Order_Details.OrderNumber
  INNER JOIN Products 
  ON Products.ProductNumber = Order_Details.ProductNumber
  WHERE Products.ProductName = 'Knee Pads'
  AND Orders.CustomerID = C.CustomerID)
AND EXISTS
  (SELECT Orders.CustomerID
  FROM Orders INNER JOIN Order_Details
  ON Orders.OrderNumber = Order_Details.OrderNumber
  INNER JOIN Products 
  ON Products.ProductNumber = Order_Details.ProductNumber
  WHERE Products.ProductName = 'Gloves'
  AND Orders.CustomerID = C.CustomerID);

 

이러한 다중 조건 문제를 해결하기 위해 사용할 수 있는 방법은 다음과 같다.

• IS NULL 조건과 함께 INNER JOIN이나 OUTER JOIN 사용
• 서브쿼리와 IN이나 NOT IN 사용
• 서브쿼리와 함께 EXISTS나 NOT EXISTS 사용

 

BETTER WAY 28 데이터베이스 엔진이 인덱스를 사용하도록 사거블 쿼리를 작성하자

쿼리 성능을 향상하려면 적절한 인덱스를 만들어야 한다. 하지만 DBMS 엔진이 인덱스를 잘 활용하려면 쿼리의 서술 논리절(WHERE, ORDER BY, GROUP BY, HAVING 절)이 인덱스를 사용해야 하는데, 이를 사거블(Search ARGument ABLE, Sargable)이라고 말한다. 

-- 비교하려는 값에 따라 사거블 쿼리를 만드는 연산자
=
>
<
>=
<=
BETWEEN
LIKE -- 검색 문자열 앞에 %를 붙이지 않을 때
IS [NOT] NULL


-- 사거블이지만 성능 향상 목적으로는 사용하지 않는 연산자
<>
IN
OR
NOT IN
NOT EXISTS
NOT LIKE

 

인덱스를 사용하지 못하는 경우

• WHERE 절 조건에서 한 개 이상의 필드에 대해 연산하는 함수를 사용하는 쿼리 (각 로우에서 함수가 연산을 수행하므로 인덱스 자체에 동일한 함수가 포함된게 아니라면 쿼리 옵티마이저는 인덱스를 사용하지 않음)
• WHERE 절에서 필드에 대해 수치 연산을 하는 경우
• LIKE '%something%'처럼 %를 사용하는 경우

 

👇🏻  사거블 쿼리와 넌사거블 쿼리 예시

-- 특정 글자로 시작하는 데이터를 조회하는 넌사거블 쿼리
SELECT EmployeeID, EmpFirstName, EmpLastName
  FROM Employees
 WHERE Left(EmpLastName, 1) = 'S';

-- 값을 계산해 결과를 찾는 넌사거블 쿼리
SELECT EmployeeID, EmpFirstName, EmpLastName
  FROM Employees
 WHERE EmpSalary*1.10 > 100000;
 
-- NULL 허용 컬럼에서 특정 이름을 찾는 넌사거블 쿼리
SELECT EmployeeID, EmpFirstName, EmpLastName
  FROM Employees
 WHERE IsNull(EmpLastName, 'Viescas') = 'Viescas';


-- 특정 연도 데이터를 조회하는 사거블 쿼리
SELECT EmployeeID, EmpFirstName, EmpLastName
  FROM Employees
 WHERE EmpDOB >= Cast('1950-01-01' AS Date)
   AND EmpDOB <  Cast('1951-01-01' AS Date);
   
-- 특정 문자로 시작되는 데이터를 조회하는 사거블 쿼리
SELECT EmployeeID, EmpFirstName, EmpLastName
  FROM Employees
 WHERE EmpLastName LIKE 'S%';
 
 -- 계산 값을 찾는 사거블 쿼리 
SELECT EmployeeID, EmpFirstName, EmpLastName
  FROM Employees
 WHERE EmpSalary > 100000/1.10;
 
 -- NULL 허용 컬럼에서 특정 이름을 찾는 사거블 쿼리
SELECT EmployeeID, EmpFirstName, EmpLastName
  FROM Employees
 WHERE EmpLastName = 'Viescas'
    OR EmpLastName IS NULL;