[SQL Server] Do it SQL 입문 4장 테이블을 서로 조합하는 조인 알아보기

교재 : Do it SQL 입문 (이지스퍼블리싱, 강성욱)

4.1 조인 (join)

1) 조인의 의미

- 테이블 A의 열과 테이블 B의 정보를 포함해 검색할 수 있도록 한다.

- 데이터 중복을 최소화하는 설계는 피할 수 없으므로, 조인을 사용해 2개 이상의 테이블을 조합한 결과를 검색한다.

- 데이터 모델링(data modeling)과 정규화

• 데이터 모델링 :  주어진 상황에서 논리 데이터 모델을 구성하는 작업
• 데이터 모델링이 끝나면 논리 데이터 모델을 물리 데이터모델로 바꾼 다음 실제 데이터베이스에 반영한다.
• 정규화 : 논리 데이터 모델의 중복을 제거해 일관성 있고 안정적인 자료구조를 만드는 단계 

 

2) 내부 조인 (INNER JOIN)

- 조인키에 해당하는 각 테이블의 열 값을 비교해 조건에 맞는 열 값을 검색한다.

- 기본 형식

SELECT [열 이름]
FROM [테이블 1]
INNER JOIN [테이블 2] ON [테이블 1.열] = [테이블 2.열]
WHERE [검색조건]

- 두 테이블의 교집합인 열을 조인 조건으로 해서 두 테이블을 조인한다.

- FROM 문에 조인할 테이블을 나열한다.

- ON은 조인할 때 조인 조건을 위해 사용하고, WHERE은 조인을 완료한 상태에서 조건에 맞는 값을 가져오기 위해 사용한다.

- 예시

SELECT
a.symbol, a.company_table, a.ipo_year, a_sector, a.industry, b.date, b.[open], b.[high], b.[low], b.[close], b.adj_close,b.volumn
FROM nasdaq_company AS a INNER JOIN stock AS b ON  a.symbol = b.symbol
WHERE a.symbol = 'MSFT' AND b.date >= '2021-10-01' AND b.date < '2021-11-01'

- 조인 조건으로 두 개 이상의 열을 사용할 수 있다.

• 조인 조건의 열이 달라도 상관없다. 

- 3개 이상의 테이블 조인 : 두 테이블의 관계가 다대다인 경우가 많다.

SELECT [열 이름]
FROM [테이블 1]
INNER JOIN [테이블 2] ON [테이블 1.열] = [테이블 2.열]
INNER JOIN [테이블 3] ON [테이블 2.열] = [테이블 3.열]
WHERE [검색조건]

 

3) 외부 조인 (OUTER JOIN)

- 다른 테이블이 있는 행에서 일치 항목이 아닌 행을 조합해 검색해야 하는 경우

- 열의 일치 항목을 고려하지 않고 한쪽 테이블을 다른 쪽 테이블에 조합할 때 사용

- 기본 형식 : < >로 감싼 항목 중 하나만 선택

SELECT [열 이름]
FROM [테이블 1]
<LEFT, RIGHT, FULL> OUTER JOIN [테이블 2] ON [테이블 1.열] = [테이블 2.열]
WHERE [검색 조건]

- LEFT OUTER JOIN 

• A, B테이블이 좌우에 있을 때 A 테이블을 기준으로 B 테이블 조인
• A 테이블은 우선 결과에 포함된다.
• A 테이블과 B 테이블을 비교해 A 테이블에 있는 내용만 B 테이블에서 골라 결과에 포함시킨다. 없으면 NULL
• 기준 테이블에 있는 데이터만을 추출하기 위해서는 LEFT OUTER JOIN 결과에서 NULL데이터만 필터링한다.

- RIGHT OUTER JOIN

• B, A 테이블이 좌우에 있을 때 A 테이블을 기준으로 B 테이블 조인
• A 테이블은 우선 결과에 포함된다.
• A 테이블과 B 테이블을 비교해 A 테이블에 있는 내용만 B 테이블에서 골라 결과에 포함시킨다. 없으면 NULL
• 기준 테이블에 있넌 데이터만을 추출하기 위해서는 RIGHT OUTER JOIN 결과에서 NULL데이터만 필터링한다.

- FULL OUTER JOIN

• LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합친 것으로 양쪽 테이블의 일치하지 않는 행도 모두 검색한다.
• 데이터베이스 디자인이나 데이터에 문제가 있을 때, 데이터의 누락이나 오류가 있을 때 주로 사용한다.
• FULL OUTER JOIN에서 LEFT 테이블과 RIGHT 테이블에 있는 데이터만을 추출하기 위해서는 NULL 데이터를 필터링한다.

 

4) 교차 조인 (CROSS JOIN)

- 각 테이블의 모든 경우의 수를 조합한 데이터가 필요한 경우 사용

- cartesian product라고도 한다.

- 기본 형식

SELECT [열 이름]
FROM [테이블 1]
	CROSS JOIN [테이블 2]
WHERE [검색 조건]

 

5) 셀프 조인 (SELF JOIN)

- 같은 테이블을 사용하는 특수한 조인

- 반드시 별칭을 사용해야 한다.

 

4.2 서브 쿼리 (subquery)

1) 서브 쿼리

- 쿼리 안에 포함되는 또 다른 쿼리

- 조인하지 않은 상태에서 다른 테이블과 일치하는 행을 찾거나, 조인 결과를 다시 조인할 때 사용 가능하다.

- 특징

• 반드시 소괄호로 감싸 사용한다.
• 주 쿼리를 실행하기 전에 1번만 실행된다.
• 비교 연산자에 서브 쿼리를 사용하는 경우 서브 쿼리를 오른쪽에 기술해야 한다.
• 내부에서는 정렬 구문인 ORDER BY 문을 사용할 수 없다.

2) WHERE문에 서브 쿼리 사용하기 : 중첩 서브 쿼리 (nested subquery)

- 조건문의 일부로 사용한다. (또 다른 SELECT 문을 사용한 결과를 주 쿼리의 조건값으로 사용한다.)

- 비교 연산자와 함께 사용할 때는 반드시 서브 쿼리의 반환 결과가 1건 이하여야 한다.

- 서브 쿼리의 반환 결과가 2건 이상일 경우 다중 행 연산자를 사용

다중 행 연산자	내용
IN	서브 쿼리의 결과에 존재하는 임의의 값과 같은 조건 검색
ANY	서브 쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건 검색
EXISTS	서브 쿼리의 결과를 만족하는 값이 존재하는지 여부 확인
ALL	서브 쿼리의 결과에 존재하는 모든 값을 만족하는 조건 검색

 

3) 단일 행 서브 쿼리 : 서브 쿼리의 결과로 1행만 반환된다.

SELECT [열 이름]
FROM [테이블]
WHERE [열] = (SELECT [열] FROM [테이블])

 

4) 다중 행 서브 쿼리 :  서브 쿼리의 결과로 2행 이상 반환된다.

- IN

SELECT [열 이름]
FROM [테이블]
WHERE [열] IN (SELECT [열] FROM [테이블])

- NOT IN

SELECT [열 이름]
FROM [테이블]
WHERE [열] NOT IN (SELECT [열] FROM [테이블])

- ANY 문 : 서브 쿼리 결과에서 값이 하나라도 만족하는 조건을 검색

-- 예시
SELCET FROM nasdaq_company 
WHERE symbol = ANY (
	SELECT symbol FROM nasdaq_company
    WHERE symbol IN ('MSFT', 'AMD', 'AMZN')
)

- EXISTS : 조건의 결괏값이 있는지 확인해 하나라도 있을경우 TRUE 반환

-- 예시
SELCET FROM nasdaq_company 
WHERE EXISTS (
	SELECT symbol FROM nasdaq_company
    WHERE symbol IN ('MSFT', 'AMD', 'AMZN')
)

- NOT EXISTS

-- 예시
SELCET FROM nasdaq_company 
WHERE NOT EXISTS (
	SELECT symbol FROM nasdaq_company
    WHERE symbol IN ('MSFT', 'AMD', 'AMZN')
)

- ALL : 서브 쿼리 결괏값에 있는 모든 값을 만족하는 조건을 주 쿼리에서 검색해 결과를 반환한다.

-- 예시
SELCET FROM nasdaq_company 
WHERE = ALL (
	SELECT symbol FROM nasdaq_company
    WHERE symbol IN ('MSFT', 'AMD', 'AMZN')
)

 

5) FROM문에 서브 쿼리 사용하기 : 인라인 뷰(inline view)

SELECT [열 이름]
FROM [테이블] AS a
INNER JOIN (SELECT [열] FROM [테이블] WHERE [열] = [값]) AS b ON [a.열] = [b.열]
WHERE [열] = [값]

 

6) SELECT 문에 서브 쿼리 사용하기 : 스칼라 서브 쿼리 (scalar subquery)

- 반드시 1개의 행을 반환해야 하므로 SUM, COUNT 등의 집계함수와 함께 사용되는 경우가 많다. → 성능 문제 발생 가능

- 스칼라 서브 쿼리는 1개 이상 사용 가능하다.

SELECT [열 이름],
    (SELECT <집계 함수> [열 이름] FROM [테이블 2]
    WHERE [테이블 2.열] = [테이블 1.열]) as Alias
FROM [테이블 1]
WHERE [조건]

 

 

4.3 공통 테이블 식 (CTE; common table expression)

1) 공통 테이블 식

- 주로 데이터베이스에 없는 테이블이 필요할 때 사용

- 바로 다음에 실행할 SELECT문에만 사용해야 한다.

- 목적에 따라 일반 공통 테이블 식과 재기 공통 테이블 식으로 나뉜다.

 

2) 일반 CTE

WITH [CTE_테이블 이름] (열 이름 1,열 이름 2, ...)
AS
(
	<SELECT문>
)
SELECT [열 이름] FROM [CTE_테이블 이름];

- CTE 내에 UNION, UNION ALL, INTERSECT, EXCEPT문을 사용해 여러 개의 일반 CTE 쿼리를 결합할 수 있다.

- CTE에서 정의한 열 개수와 CTE의 SELECT문에서 얻은 열 목록이 다르면 오류가 발생한다.

- UNION문과 UNION ALL문으로 CTE 결합하기

WITH cte_stock_price (date, symbol, price)
AS
(
    SELECT data, symbol, [close] FROM stock
    WHERE date >= '2021-01-01' AND date <= '2021-01-10'
    UNION ALL
    SELECT data, symbol, [close] FROM stock
    WHERE date >= '2021-02-01' AND date <= '2021-02-07'
)
SELECT * FROM cte_stock_price WHERE symbol ='MSFT'

• 중복을 제거한 결과를 보고 싶다면 UNION 문을 사용한다. → 성능 문제 유발 가능
• 다른 쿼리에서 미리 중복을 제거하고 UNION ALL문을 사용하는 것을 권장한다.

- INERSECT문으로 CTE 결합하기

• 내부 조인과 비슷하지만, INTERSECT문은 각 쿼리에서 반환한 결과에서 중복 결과를 걸러내 반환한다.

WITH cte_stock_price (date, symbol, price)
AS
(
    SELECT data, symbol, [close] FROM stock
    WHERE date >= '2021-01-01' AND date <= '2021-01-10'
    INTERSECT
    SELECT data, symbol, [close] FROM stock
    WHERE date >= '2021-02-01' AND date <= '2021-02-07'
)
SELECT * FROM cte_stock_price WHERE symbol ='MSFT'

- EXCEPT문으로 CTE 결합하기

• NOT IN과 비슷하지만, EXCEPT문은 결괏값에서 중복을 제거한 유일한 행을 반환한다.
• CTE에서 먼저 작성한 쿼리 기준으로, 그 다음에 작성한 SELECT문 커리와 중복되지 않는 데이터를 반환한다.

WITH cte_stock_price (date, symbol, price)
AS
(
    SELECT data, symbol, [close] FROM stock
    WHERE date >= '2021-01-01' AND date <= '2021-01-10'
    EXCEPT
    SELECT data, symbol, [close] FROM stock
    WHERE date >= '2021-01-07' AND date <= '2021-01-20'
)
SELECT * FROM cte_stock_price WHERE symbol ='MSFT'

 

3) 재귀 CTE

- CTE 결과를 CTE 내부에서 재사용함으로써 반복 실행하는 쿼리 구조를 갖는다.

- 기본 형식

WITH [CTE_테이블 이름] (열 이름 1, 열 이름 2, ...)
AS (
    <SELECT * FROM 테이블A>
    UNION ALL
    <SELECT * FROM 테이블B JOIN CTE_테이블 이름>
)
SELECT * FROM [CTE_테이블 이름];

• 적어도 두 개의 CTE 쿼리가 필요하다.
• 각 쿼리는 앵커 멤버 (anchor member), 재귀 멤버(recursion member)를 포함한다.
• 앵커 멤버 : 자기 자신 CTE를 참고하지 않는 멤버로, 1번째 재귀 멤버 앞에 위치한다.
• 재귀 멤버의 열 자료형은 반드시 앵커 멤버의 열 자료형과 일치해야 한다.
• 앵커 멤버와 재귀 멤버는 여러 개 정의할 수 있다.

- 재귀 CTE의 실행 순서

1. 최초에 쿼리 1을 실행한다. 이때 쿼리 2의 기본값은 0으로 초기화된다.
2. 이어서 쿼리 2를 실행한다. 이때 쿼리 2의 기본값은 1만큼 증가한다. 쿼리 1의 결과 행 수만큼 쿼리 2에서 CTE_테이블 이름을 재귀호출하고, 쿼리 2의 기본값이 1씩 증가하면서 쿼리 1의 결과 행 수까지 도달해 결과가 더 없다면 재귀 호출을 중단한다.
3. 외부 SELECT문에서 과정 1, 2를 통해 만든  CTE 누적 결과를 검색한다.