일부 질문에는 답변하는 데 두 단계가 필요합니다. “평균보다 높은 주문은 무엇입니까?” 먼저 평균을 구한 다음 각 주문을 평균과 비교해야 합니다. SQL 하위 쿼리를 사용하면 두 단계를 하나의 문으로 작성할 수 있습니다.

이 튜토리얼에서는 두 개의 작은 테이블을 사용하여 각 유형을 다룹니다. 우리는 SQLite 3.50에서 이러한 쿼리를 실행했으며 표시된 결과는 해당 실행에서 나온 것입니다. 표준 하위 쿼리 구문은 널리 지원되며 데이터베이스가 다른 부분을 지적합니다.

하위 쿼리란 무엇입니까?

내부 또는 중첩 쿼리라고도 하는 하위 쿼리는 다른 문 안에 대괄호로 묶인 SELECT 문입니다. 개념적으로 내부 쿼리는 외부 쿼리가 사용하는 결과를 생성합니다.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

샘플 테이블

CREATE TABLE customers (id INT, name TEXT, city TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);

INSERT INTO customers VALUES
 (1,'Asha','Pune'), (2,'Ben','Delhi'), (3,'Chitra','Pune'), (4,'Dev','Goa');

INSERT INTO orders VALUES
 (101,1,1200), (102,1,800), (103,2,300), (104,3,2500), (105,3,100);

Dev에는 주문이 없습니다. 이 세부 사항은 아래의 몇 가지 예에서 중요합니다.

서브쿼리 종류 한눈에 보기

스칼라 하위 쿼리

스칼라 하위 쿼리는 정확히 하나의 값을 반환하므로 단일 값이 적합한 모든 곳에서 사용할 수 있습니다. 다음은 이전 쿼리와 그 결과입니다. 모든 주문의 평균은 980입니다.

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- 101 | 1200
-- 104 | 2500

WHERE에서는 집계가 직접 허용되지 않으므로 WHERE total > AVG(total)을 쓸 수 없습니다. 하위 쿼리는 값을 별도로 계산하여 이 문제를 해결합니다.

“스칼라” 하위 쿼리가 여러 행을 반환하면 어떻게 되나요? PostgreSQL, MySQL, SQL Server를 포함한 대부분의 데이터베이스에서는 오류가 발생합니다. SQLite는 그렇지 않습니다. 우리 테스트에서는 첫 번째 행만 자동으로 사용했는데, 이는 실제 버그를 숨길 수 있으므로 이 동작에 의존하지 마십시오.

IN과 NOT IN

내부 쿼리가 목록을 반환하는 경우 IN을 사용하세요. 1000개 이상의 주문이 하나 이상 있는 고객을 찾습니다.

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Asha
-- Chitra

NOT IN은 반대로 주문한 적이 없는 고객을 찾습니다.

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Dev

여기에서는 작동하지만 NOT IN에는 아래에서 다루는 위험한 극단적인 경우가 있습니다.

FROM의 하위 쿼리

FROM의 하위 쿼리는 종종 파생 테이블이라고 불리는 임시 테이블 역할을 합니다. 각 도시의 고객당 평균 지출에 대한 다음 쿼리에서와 같이 두 번 집계해야 할 때 유용합니다.

SELECT city, AVG(spent) AS avg_spent
FROM (
  SELECT c.city, SUM(o.total) AS spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.city
) AS per_customer
GROUP BY city;

-- Delhi |  300.0
-- Pune  | 2300.0

내부 쿼리는 각 고객의 합계를 구하고, 외부 쿼리는 해당 합계의 평균을 계산합니다. 일부 데이터베이스에서 요구하는 것처럼 파생 테이블에 별칭을 지정하는 것은 좋은 습관입니다. GROUP BY이 처음이라면 SQL GROUP BY 및 HAVING을 참조하세요.

SELECT의 하위 쿼리

스칼라 하위 쿼리는 계산된 열을 추가할 수도 있습니다. 이것은 고객당 주문을 계산합니다.

SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- Asha   | 2
-- Ben    | 1
-- Chitra | 2
-- Dev    | 0

Dev는 0으로 나타납니다. Dev에 일치하는 행이 없기 때문에 내부 조인은 Dev를 완전히 삭제했을 것입니다. SQL 조인 설명에서 설명하는 LEFT JOIN과 GROUP BY를 사용하여 동일한 결과를 얻을 수 있습니다.

상관 하위 쿼리

이전 예제도 내부 쿼리가 외부 쿼리의 c.id을 참조하므로 상관 관계가 있습니다. 상관되지 않은 하위 쿼리는 단독으로 실행할 수 있습니다. 상관된 항목은 현재 외부 행의 값에 따라 달라지므로 그럴 수 없습니다.

이는 동일한 고객의 평균보다 높은 주문을 찾습니다.

SELECT o.id, o.customer_id, o.total
FROM orders o
WHERE o.total > (
  SELECT AVG(i.total)
  FROM orders i
  WHERE i.customer_id = o.customer_id
);

-- 101 | 1 | 1200
-- 104 | 3 | 2500

읽는 방법은 다음과 같습니다. 주문 101의 경우 내부 쿼리는 고객 1의 주문(1200 및 800)을 평균화하여 1000을 제공합니다. 1200이 더 크므로 행이 유지됩니다. 주문 102의 경우 동일한 평균이 적용되지만 800은 더 크지 않으므로 삭제됩니다. 고객 2의 단일 주문은 자체 평균과 동일하므로 해당 주문도 삭제됩니다.

“행당 한 번”은 논리를 설명하며 쿼리 최적화 프로그램은 종종 이를 훨씬 더 효율적인 것으로 다시 작성합니다. 그럼에도 불구하고 느린 상관 하위 쿼리는 조사해 볼 가치가 있습니다. 내부 열(여기서는 orders.customer_id)의 인덱스가 큰 차이를 만들 수 있습니다. SQL 인덱스 설명을 참조하세요.

존재하고 존재하지 않음

EXISTS은 예 또는 아니오 질문을 합니다. 내부 쿼리가 최소한 하나의 행을 반환합니까? 첫 번째 일치에서 멈출 수 있으며 내부에서 무엇을 선택하든 상관이 없기 때문에 사람들은 종종 SELECT 1를 씁니다.

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Asha, Ben, Chitra

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Dev

NOT IN NULL 트랩

이것은 가장 일반적인 하위 쿼리 버그 중 하나입니다. 하나의 실제 ID와 하나의 NULL이 있는 테이블을 만든 다음 이에 대해 두 가지 양식을 모두 실행했습니다.

CREATE TABLE r (customer_id INT);
INSERT INTO r VALUES (1), (NULL);

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM r);
-- (no rows at all)

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM r WHERE r.customer_id = c.id);
-- Ben, Chitra, Dev

첫 번째는 왜 아무것도 반환하지 않습니까? id NOT IN (1, NULL)은 id <> 1 AND id <> NULL를 의미합니다. NULL과의 비교는 true가 아니라 알 수 없으므로 전체 조건이 true가 될 수 없습니다. 오류 메시지나 경고도 없고 그럴듯해 보이는 빈 결과만 표시됩니다.

하위 쿼리 vs JOIN vs CTE

많은 쿼리는 여러 가지 방법으로 작성될 수 있습니다. 이전에는 IN을 사용하여 대량 주문 고객을 찾았습니다. 다음은 조인을 사용한 동일한 결과입니다.

SELECT DISTINCT c.name FROM customers c
JOIN orders o ON o.customer_id = c.id AND o.total > 1000;

-- Asha
-- Chitra

DISTINCT을 주목하세요. 고객이 일치하는 항목이 여러 개인 경우 조인을 통해 중복 항목이 생성될 수 있지만 IN와 EXISTS은 그럴 수 없습니다.

성능은 데이터베이스와 데이터에 따라 다르며 최신 최적화 프로그램은 종종 유사한 계획을 생성합니다. 따라서 가장 명확한 형식을 먼저 선택한 다음 느린 것으로 판명되면 EXPLAIN을 확인하십시오.

일반적인 실수

• 여러 행 결과에 = 사용. IN을 사용하거나 내부 쿼리가 한 행을 반환하는지 확인하세요.
• null 허용 열에서 NOT IN를 사용합니다. 모든 행을 자동으로 잃을 수 있습니다.
• 불명확한 별칭을 사용합니다. 상관 쿼리에서는 모든 열을 한정해야 합니다. 그렇지 않으면 잘못된 테이블에 바인딩될 수 있습니다.
• 깊은 중첩. 세 가지 수준은 읽기 어렵기 때문에 CTE로 변환하세요.
• 내부에 ORDER BY를 추가합니다. 일반적으로 외부 결과에는 영향을 미치지 않으며 일부 데이터베이스에서는 이를 거부합니다.

마무리

하위 쿼리는 2단계 질문을 표현하는 자연스러운 방법입니다. 스칼라 하위 쿼리와 IN로 시작한 다음, 한 번에 하나의 외부 행을 통해 추론하여 상관 관계를 연습합니다. 그리고 NULL이 가능할 때마다 NOT EXISTS를 기억하세요. 연습하려면 하위 쿼리, 조인 및 CTE를 사용하여 세 가지 방법으로 하나의 질문을 작성하고 결과와 가독성을 모두 비교하십시오.

자주 묻는 질문

계속 학습하세요