8.1 Subqueries

데이터베이스 시스템 2024. 11. 14. 08:03

Subqueries

서브쿼리는 때때로 중첩 쿼리(Nested Query) 또는 내부 쿼리(Inner Query)라고도 불리며, 하나의 SQL 쿼리 내에 포함된 또 다른 쿼리이다. 일반적으로 SELECT 문장의 WHERE 절에서 사용되어 외부 쿼리에 데이터를 제공하고 선택된 결과를 제한한다. 서브쿼리는 괄호 () 안에 작성된다.

외부 SELECT 문은 서브쿼리를 사용하여, Aruba의 공식 언어보다 높은 비율로 사용되는 Language를 확인한다.
서브쿼리가 먼저 실행되어 ABW의 공식 언어 Percentage인 5.3을 찾는다.
외부 쿼리는 서브쿼리에서 반환된 값 5.3을 사용하여 실행한다. 그 결과, 세 가지 Language가 Percentage 5.3보다 크다.
SELECT 문은 서브쿼리와 함께 IN 연산자를 사용하여 Europe에서 사용되는 Language를 확인한다.
서브쿼리는 먼저 Europe의 모든 코드(ALB와 AND)를 찾고, 외부 쿼리는 이후에 이들 CountryCode(ALB 및 AND)에 해당하는 CountryCode와 Language를 선택한다.

Correlated Subqueries

서브쿼리의 WHERE 절이 외부 쿼리의 열을 참조할 때, 이 서브쿼리를 상관 서브쿼리(Correlated Subquery)라고 한다. 상관 서브쿼리에서는 외부 쿼리가 현재 조사 중인 행에 따라 선택된 행이 달라진다.

상관 서브쿼리에서 열 이름이 외부 쿼리의 열 이름과 동일한 경우, TableName.ColumnName 형식을 사용하여 열을 구분한다. 예: City.CountryCode는 City 테이블의 CountryCode 열을 나타낸다.

별칭(Alias)을 사용하여 열을 구분할 수도 있다. 별칭은 열이나 테이블에 일시적으로 할당된 이름으로, AS 키워드가 열 또는 테이블 이름 뒤에 따라붙어 별칭을 생성한다.

예: SELECT Name AS N FROM Country AS C는 Name 열에 대해 별칭 N을, Country 테이블에 대해 별칭 C를 생성한다.

AS 키워드는 선택사항으로 생략할 수 있다.

예: SELECT Name N FROM Country C.

아래 예시에서 외부 SELECT 문은 상관 서브쿼리를 사용하여 각 국가의 평균 도시 인구보다 인구가 많은 도시를 찾는다.

외부 쿼리와 상관 서브쿼리 모두 City 테이블에서 선택한다. 외부 쿼리는 City 테이블에 대해 별칭 C를 사용하므로, C.CountryCode는 외부 쿼리의 CountryCode 열을 나타낸다.
외부 쿼리는 City 테이블에서 행을 선택한다. 각 City 행이 선택될 때마다 서브쿼리가 해당 도시의 국가에 대한 평균 인구를 찾는다.
그런 다음 외부 쿼리는 서브쿼리에서 반환된 평균 인구를 사용하여 실행된다. 예를 들어, 부에노스아이레스(Buenos Aires)의 인구는 2982146명으로 2124303.5명보다 많다.
외부 쿼리는 다음 행을 처리하며, ARG에 대한 평균 인구가 다시 계산된다. 라마탄자(La Matanza)는 인구가 2124303.5보다 크지 않기 때문에 선택되지 않는다.
외부 쿼리는 상파울루(São Paulo) 역시 BRA의 평균 인구보다 큰 인구를 가지고 있음을 찾는다.
리우데자네이루(Rio de Janeiro)는 인구가 5598953명으로, 7783719보다 크지 않기 때문에 선택되지 않는다.

1) What is missing to compare the subquery's CountryCode with the outer query's CountryCode?

SELECT Name, CountryCode
FROM City
WHERE 2 <=  
    (SELECT COUNT(*)
    FROM CountryLanguage
    WHERE CountryCode = *******);

▼View answer

City.CountryCode

2) What is missing to compare the subquery's CountryCode with the outer query's CountryCode?

SELECT Name, CountryCode
FROM City T
WHERE 2 <=  
    (SELECT COUNT(*)
    FROM CountryLanguage
    WHERE CountryCode = ********);

▼View answer

T. CountryCode

3) How many times does the subquery execute?

SELECT Name, CountryCode
FROM City C
WHERE 2 <=  
    (SELECT COUNT(*)
    FROM CountryLanguage
    WHERE CountryCode = C.CountryCode);

▼View answer

서브쿼리는 외부 쿼리의 테이블에 있는 각 행마다 한 번씩 실행된다. City 테이블에 5개의 행이 있으므로, 서브쿼리는 총 5번 실행된다.

4) How many cities does the query return?

SELECT Name, CountryCode
FROM City C
WHERE 2 <=  
    (SELECT COUNT(*)
    FROM CountryLanguage
    WHERE CountryCode = C.CountryCode);

▼View answer

서브쿼리는 CountryCode와 일치하는 CountryLanguage의 행 수를 반환한다: ABW는 2개, AFG는 1개, AGO는 2개이다. 외부 쿼리는 내부 쿼리가 2개 이상의 행을 반환하는 도시만 선택한다. ABW와 AGO는 각각 2개의 행을 가지므로, Luanda, Huambo, Oranjestad 도시가 반환된다.

5) What is missing to select the languages used most in each country?

SELECT Language
FROM CountryLanguage C
WHERE  = ********
    (SELECT MAX(Percentage) 
    FROM CountryLanguage
    WHERE CountryCode = C.CountryCode);

▼View answer

Percentage

서브쿼리는 각 국가의 가장 큰 Percentage 값을 반환하며, 이는 외부 쿼리의 Percentage 열과 비교된다. 외부 쿼리는 Papeiamento, Balochi, Mbundu를 반환한다.

Exists 연산자

상관 서브쿼리(Correlated Subquery)는 일반적으로 EXISTS 연산자를 사용한다. EXISTS 연산자는 서브쿼리가 적어도 하나의 행을 선택하면 TRUE를 반환하고, 선택된 행이 없으면 FALSE를 반환한다. 반대로, NOT EXISTS 연산자는 서브쿼리가 행을 선택하지 않으면 TRUE를 반환하고, 적어도 하나의 행을 선택하면 FALSE를 반환한다.

이 쿼리는 인구의 97% 이상이 하나의 언어를 사용하는 국가의 도시들을 선택한다.
서브쿼리는 ARG에서 97%를 초과하는 비율이 없기 때문에, 아무 행도 선택하지 않는다. 따라서 EXISTS 절이 FALSE가 되어 외부 쿼리는 부에노스아이레스를 선택하지 않는다.
ARG에 대해 EXISTS 절이 FALSE이므로, 아르헨티나의 도시는 선택되지 않는다.
서브쿼리는 BRA에서 97%를 초과하는 비율을 가진 하나의 행을 선택하므로, EXISTS 절이 TRUE가 되어 외부 쿼리는 상파울루를 선택한다.
BRA에 대해 EXISTS 절이 TRUE이므로, 브라질의 모든 도시가 선택된다.

8.1.7: Select albums with EXISTS.

주어진 SQL은 Album 및 Song 테이블을 생성하고 일부 앨범과 노래를 삽입한다. 각 노래는 하나의 앨범과 연관되어 있다.

SELECT 문은 세 곡 이상의 노래가 있는 모든 앨범을 선택한다. 쿼리를 실행하여 결과 테이블에 Saturday Night Fever와 앨범 21만 표시되는지 확인한다.
GROUP BY 절을 수정하여 동일한 아티스트의 세 곡 이상의 노래가 있는 앨범을 선택한다. 쿼리를 실행하여 결과 테이블에 앨범 21만 표시되는지 확인한다.

CREATE TABLE Album (
  ID INT,
  Title VARCHAR(60),
  ReleaseYear INT,
  PRIMARY KEY (ID)
);

INSERT INTO Album VALUES
  (1, 'Saturday Night Fever', 1977),
  (2, 'Born in the U.S.A.', 1984),
  (3, 'Supernatural', 1999),
  (4, '21', 2011);

CREATE TABLE Song (
  ID INT,
  Title VARCHAR(60),
  Artist VARCHAR(60),
  AlbumID INT,
  PRIMARY KEY (ID),
  FOREIGN KEY (AlbumID) REFERENCES Album(ID)
);

INSERT INTO Song VALUES
  (100, 'Stayin\' Alive', 'Bee Gees', 1),
  (101, 'More Than a Woman', 'Bee Gees', 1),
  (102, 'If I Can\'t Have You', 'Yvonne Elliman', 1),
  (200, 'Dancing in the Dark', 'Bruce Springsteen', 2),
  (201, 'Glory Days', 'Bruce Springsteen', 2),
  (300, 'Smooth', 'Santana', 3),
  (400, 'Rolling in the Deep', 'Adele', 4),
  (401, 'Someone Like You', 'Adele', 4),
  (402, 'Set Fire to the Rain', 'Adele', 4),
  (403, 'Rumor Has It', 'Adele', 4);

SELECT *
FROM Album
WHERE EXISTS 
  (SELECT COUNT(*)
   FROM Song
   WHERE AlbumID = Album.ID 
   GROUP BY AlbumID
   HAVING COUNT(*) >=3);

▼View solution

SELECT *
FROM Album
WHERE EXISTS 
  (SELECT COUNT(*)
   FROM Song
   WHERE AlbumID = Album.ID
   GROUP BY Artist
   HAVING COUNT(*) >= 3);

1) What does the query return?

SELECT Name 
FROM Employee E
WHERE EXISTS
   (SELECT * 
   FROM Family
   WHERE Id = E.Id 
      AND Relationship = 'Spouse');

▼View answer

Lisa Ellison and Maria Rodriguez

2) What does the query return?

SELECT Name 
FROM Employee E
WHERE NOT EXISTS
   (SELECT * 
   FROM Family
   WHERE Id = E.Id 
      AND Relationship = 'Spouse');

▼View answer

Sam Snead

3) Write subquery which makes the outer query return only Jose, Gina, and Clara Rodriguez.

SELECT Name
FROM Family F
WHERE EXISTS (___);

▼View answer

SELECT *
	FROM Employee
    WHERE Id = F.Id AND Salary > 50000

서브쿼리는 급여가 50000 이상인 Maria Rodriguez에 대해서만 하나의 행을 반환한다. 따라서 Maria의 가족만 반환된다.

4) Write subquery which makes the outer query return two rows.

SELECT *
FROM Employee E
WHERE EXISTS (___);

SELECT *
	FROM Family
    WHERE Id = E.Id AND Relationship = 'Son'

상관 서브쿼리는 각각 아들이 한 명 있는 Lisa Ellison과 Sam Snead에 대해 하나의 행을 반환한다. 아들이 없는 Maria Rodriguez에 대해서는 서브쿼리가 행을 반환하지 않는다. 따라서 외부 쿼리는 두 개의 행을 반환한다.

Flattening subqueries 서브쿼리 평탄화

많은 서브쿼리는 조인으로 다시 작성할 수 있다. 대부분의 데이터베이스는 서브쿼리와 외부 쿼리를 별도로 최적화하지만, 조인은 한 번에 최적화된다. 따라서 성능이 중요한 경우 조인이 더 빠르고 선호된다.

서브쿼리를 동등한 조인으로 대체하는 것을 쿼리 평탄화(flattening)라고 한다. 서브쿼리 평탄화의 기준은 복잡하며, 각 데이터베이스 시스템의 SQL 구현에 따라 다르다. IN 또는 EXISTS를 따르거나 단일 값을 반환하는 대부분의 서브쿼리는 평탄화할 수 있다. NOT EXISTS를 따르거나 GROUP BY 절을 포함하는 대부분의 서브쿼리는 평탄화할 수 없다.

쿼리 평탄화의 첫 번째 단계는 다음과 같다:

외부 쿼리의 SELECT, FROM, GROUP BY, HAVING, ORDER BY 절을 유지한다.
각 서브쿼리 테이블에 대해 INNER JOIN 절을 추가한다.
서브쿼리와 외부 쿼리 열 간의 비교를 ON 절로 이동한다.
서브쿼리와 외부 쿼리의 WHERE 절에 있는 나머지 표현식으로 WHERE 절을 추가한다.
필요시 중복 행을 제거하기 위해 SELECT DISTINCT를 사용한다.

이 첫 번째 단계를 거친 후 평탄화된 쿼리를 테스트하고 올바른 결과를 얻기 위해 조정한다. 원본 쿼리와 평탄화된 쿼리가 다양한 데이터에 대해 동일한 결과를 반환하는지 확인한다.

서브쿼리는 인구가 1,000,000명 이상인 도시의 국가 코드를 선택한다.
외부 쿼리는 국가 이름을 선택한다.
쿼리를 평탄화하기 위해 서브쿼리를 INNER JOIN 절로 대체한다.
조인 쿼리는 인구가 1,000,000명 이상인 각 도시에 대해 하나의 국가 이름을 선택한다.
DISTINCT 절은 중복 행을 제거한다. 서브쿼리와 조인 쿼리는 동일한 결과를 반환한다.

Given the data in the tables below, which query pairs return the same result table?

SELECT E.Name 
FROM Employee E 
WHERE EXISTS
  (SELECT * 
   FROM Family F
   WHERE F.Id = E.Id AND Relationship = 'Spouse');

SELECT E.Name 
FROM Employee E 
INNER JOIN Family F ON F.Id = E.Id
WHERE Relationship = 'Spouse';

Same result

E.Name
Lisa Ellison
Maria Rodriguez

SELECT E.Name 
FROM Employee E 
WHERE EXISTS
  (SELECT * 
   FROM Family F
   WHERE F.Id = E.Id AND Relationship = 'Daughter');

SELECT E.Name 
FROM Employee E 
INNER JOIN Family F ON F.Id = E.Id
WHERE Relationship = 'Daughter';

Different result

첫 번째 쿼리는 Employee 행을 처리하고 한 명 이상의 딸이 있는 직원을 선택한다.

E.Name
Maria Rodriguez

두 번째 쿼리는 Employee와 Family를 조인하고 관계가 'Daughter'인 모든 조인 행을 선택한다. 이 조인에는 Maria Rodriguez에 대해 두 행이 있으며, 각각 그녀의 딸을 나타낸다.

E.Name
Maria Rodriguez
Maria Rodriguez

SELECT E.Name 
FROM Employee E 
WHERE EXISTS
  (SELECT * 
   FROM Family F
   WHERE F.Id = E.Id AND Relationship = 'Daughter');

SELECT DISTINCT E.Name 
FROM Employee E 
INNER JOIN Family F ON F.Id = E.Id
WHERE Relationship = 'Daughter';

Same result

쿼리는 이전 질문과 동일하지만, 이제 두 번째 쿼리에 SELECT DISTINCT가 추가되어 중복된 Maria Rodriguez 행이 제거된다. 이제 두 쿼리는 동일한 결과를 반환한다.

SELECT E.Name 
FROM Employee E
WHERE NOT EXISTS
  (SELECT * 
   FROM Family F
   WHERE F.Id = E.Id AND Relationship = 'Spouse');

SELECT E.Name 
FROM Employee E
INNER JOIN Family F ON F.Id = E.Id
WHERE Relationship != 'Spouse';

Different result

첫 번째 쿼리는 Employee 행을 처리하며, 직원에게 배우자가 없는 경우 해당 직원 이름을 반환한다.

E.Name
Sam Snead

두 번째 쿼리는 Employee와 Family를 조인하여 관계가 'Son' 또는 'Daughter'인 모든 조인 행을 선택한다. 결과는 다음과 같다: Lisa Ellison has one son, Sam Snead has one son, and Maria Rodriguez has two daughters

E.Name
Lisa Ellison
Sam Snead
Maria Rodriguez
Maria Rodriguez

'데이터베이스 시스템' 카테고리의 다른 글

8.2.1: Complex queries example (0)	2024.11.16
8.2 Complex query (0)	2024.11.15
7.7 LAB - Multiple joins with aggregate (Sakila) (0)	2024.11.13
7.6 LAB - Select employees and managers with inner join (0)	2024.11.12
7.5 LAB - Select lesson schedule with multiple joins (0)	2024.11.11

ABOUT ME

바꾸자키의 데이터쪼가리 바꾸자키의 데이터쪼가리

Subqueries

Correlated Subqueries

Exists 연산자

Flattening subqueries 서브쿼리 평탄화

'데이터베이스 시스템' 카테고리의 다른 글

티스토리툴바

ABOUT ME

Subqueries

Correlated Subqueries

Exists 연산자

Flattening subqueries 서브쿼리 평탄화

'데이터베이스 시스템' 카테고리의 다른 글

관련글 관련글 더보기

티스토리툴바