Do it SQL__DO_IT_04_(based SQL Server)

chapter 04 테이블을 서로 통합하는 조인 알아보기

04-1 조인 

내부 조인/ 외부조인/ 교차조인/ 셀프조인

데이터 모델링과 정규화	모델링 : 주어진 상황에서 논리 데이터 모델을 구성하는 작업 정규화 : 데이터 모델이 적절한 일관성을 유지하며 중복이 없는 논리 데이터 모델을 구성하는 것
내부조인 INNER JOIN	조인키에 해당하는 각 테이블의 열값을 비교해 조건에 맞는 값
	|    |--Index Seek( OBJECT:([DoItSQL].[dbo].[stock].[nci_stock_2] AS [b]),  SEEK:([b].[symbol]=N'MSFT' AND  [b].[date] >= '2021-10-01 00:00:00.000' AND  [b].[date] < '2021-11-01 00:00:00.000')  ORDERED FORWARD) [DoItSQL].[dbo].[stock].[nci_stock_2]는 사용된 인덱스 이 인덱스는 [DoItSQL].[dbo].[stock] 테이블에 있는 **nci_stock_2**라는 이름의 인덱스 AS [b]는 이 인덱스가 **테이블 별칭(b)**으로 참조
	Index Seek 연산은 [DoItSQL].[dbo].[industry_group] 테이블에 있는 [nci_industry_group_2] 인덱스를 사용하여, industry 컬럼이 '자동차'인 데이터만 효율적으로 검색하는 작업 이 검색은 **오름차순(정방향)**으로 정렬된 인덱스를 기반으로 수행 인덱스 검색을 사용함으로써 성능이 향상되며, 테이블에서 필요한 데이터만 빠르게 찾을 수 있음 Index Seek 연산은 [DoItSQL].[dbo].[industry_group_symbol] 테이블의 ci_industry_group_symbol_1 인덱스를 사용하여, num 값이 industry_group 테이블의 num 값과 일치하는 데이터를 효율적으로 검색하는 과정 이는 두 테이블 간의 조인 연산으로, num 값이 동일한 데이터를 찾아내며, 검색은 정렬된 인덱스를 이용하여 오름차순으로 수행 Clustered Index Seek 연산은 [DoItSQL].[dbo].[nasdaq_company] 테이블의 클러스터드 인덱스를 사용하여, symbol 값이 industry_group_symbol 테이블의 symbol 값과 동일한 데이터를 효율적으로 검색하는 과정 이 연산은 두 테이블 간의 조인 작업을 수행하며, 클러스터드 인덱스를 기반으로 오름차순으로 정렬된 데이터를 검색
외부 조인  OUTER JOIN	LEFT, RIGHT, FULL
LEFT OUTER JOIN / Null 필터링
	Clustered Index Scan 연산은 [DoItSQL].[dbo].[nasdaq_company] 테이블에서 정의된 클러스터드 인덱스를 사용하여, 테이블의 모든 데이터를 순차적으로 스캔하는 작업입니다. 데이터는 오름차순으로 정렬된 순서로 스캔되며, 특정 조건 없이 테이블을 전부 탐색하는 경우에 주로 발생 Index Scan 연산은 [DoItSQL].[dbo].[industry_group_symbol] 테이블의 nci_industry_group_symbol_2 비클러스터드 인덱스를 사용하여, 전체 인덱스를 순차적으로 스캔하는 작업을 수행 검색은 오름차순으로 이루어지며, 인덱스의 모든 항목을 읽음 인덱스에 저장된 모든 데이터를 탐색할 필요가 있을 때, 혹은 적절한 필터링 조건이 없거나 너무 많은 데이터를 포함하는 경우 이 연산이 발생할 수 있으며, 성능에 부정적인 영향 **Merge Join**은 정렬된 두 입력 집합을 병합 조인 방식 이 조인은 일반적으로 정렬된 데이터를 기반으로 수행되며, 조인 조건에 따라 두 집합을 병합한 결과 **Left Outer Join**은 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블에서 일치하는 행이 없으면 NULL을 반환하는 조인 방식 이 경우, [a].[symbol] 값이 왼쪽 테이블에 존재하면, 오른쪽 테이블에 매칭되는 값이 없어도 반환됨
RIGHT OUTER JOIN / Null 필터링	RIGHT 도 LEFT 방식으로 수행됨
	Merge Join 연산은 두 테이블 ([industry_group_symbol] 테이블과 [nasdaq_company] 테이블) 간의 Left Outer Join을 수행하는 과정 왼쪽 테이블 ([industry_group_symbol] 테이블)의 모든 행을 반환하며, 오른쪽 테이블 ([nasdaq_company] 테이블)에서 symbol 값이 동일한 행이 있으면 병합하고, 일치하는 행이 없으면 오른쪽 테이블 값에 NULL을 반환 또한, RESIDUAL 조건을 통해 조인 조건의 정확한 일치 여부를 다시 한 번 확인 이 연산은 정렬된 데이터를 효율적으로 처리하며, 두 테이블 간의 조인을 수행하는 데 있어 성능이 최적화된 방식
FULL OUTER JOIN
	Full Outer Join이므로, 왼쪽 테이블([nasdaq_company])에만 있는 데이터도 반환되고, 오른쪽 테이블([industry_group_symbol])에만 있는 데이터도 반환되며, 양쪽 테이블에서 매칭되는 데이터는 병합 매칭되지 않는 데이터는 상대 테이블의 값이 NULL로 채워짐 MERGE 조건은 두 테이블의 symbol 값이 동일할 때 데이터를 병합하는 기준 RESIDUAL 조건은 이 조인 조건이 정확히 맞는지를 최종적으로 검증하는 역할 이 방식은 대량의 데이터를 정렬된 상태에서 병합해야 할 때 매우 효율적으로 동작 Full Outer Join은 두 테이블의 모든 데이터를 포함하므로, 매칭 여부와 관계없이 모든 데이터를 반환하는 것이 특징

 

교차 조인 Cross JOIN	카르테시안 곱
	중첩 루프(내부 조인) **Nested Loops**는 두 개의 테이블을 중첩 루프 방식으로 조인하는 방법 이 방식은 하나의 테이블을 루프의 외부(외부 루프)로 사용하고, 이 외부 루프의 각 행에 대해 내부 루프에서 조건에 맞는 행을 검색 작동 방식: 외부 루프: 첫 번째 테이블(테이블 A)의 각 행을 차례로 읽음 내부 루프: 두 번째 테이블(테이블 B)의 모든 행을 읽어 첫 번째 테이블의 현재 행과 조인 조건이 일치하는지 검사 조인 조건: 일치하는 행이 발견되면 두 행을 결합하여 결과를 생성 장점 유연성: Nested Loops는 조인 조건이 복잡하거나 인덱스가 없는 경우에도 사용 적은 메모리 사용: 큰 데이터 집합이 아닌 경우, 메모리 사용이 비교적 적습니다. 단점 성능 문제:  데이터 집합이 크거나 조인 조건이 복잡한 경우 성능이 저하 특히, 중첩 루프는 외부 테이블의 각 행에 대해 내부 테이블을 전부 스캔하기 때문에, 시간 복잡도가 O(n * m) 따라서 데이터가 많을 경우 처리 시간이 급격히 증가 비효율적인 경우: 인덱스가 없거나 조인 조건이 자주 변동하는 경우 Nested Loops는 비효율적

셀프조인 Self join	별칭 사용 필수
	이 Index Seek 연산은 [DoItSQL].[dbo].[stock] 테이블의 비클러스터드 인덱스인 nci_stock_2를 사용하여, **symbol이 'MSFT'**이고 date가 2021년 10월 한 달 동안인 데이터를 효율적으로 검색하는 과정 이 Compute Scalar 연산은 쿼리의 실행 과정에서 계산식을 수행하여 결과 집합에 새로운 열을 추가하는 작업 **SEEK**는 RID Lookup에서 검색할 조건 **[Bmk1002]=[Bmk1002]**는 행 식별자(북마크) 값으로 데이터를 찾는 조건 이 조건은 북마크 값이 인덱스에서 검색된 값을 사용하여 실제 데이터 페이지에서 행을 찾아내는 작업을 수행 외부 루프에서 A 테이블의 각 행을 읽고, 내부 루프에서 B 테이블을 스캔하여 조인 조건이 맞는 행을 찾음 외부 루프의 행에서 [Bmk1002] (예: 행 식별자)와 [Expr1011] (계산된 열 값) 등을 참조하여 내부 루프에서 B 테이블의 해당 데이터 페이지를 미리 로드하고 비정렬된 방식으로 읽음 이 방식은 성능을 최적화하면서 조인 작업을 효율적으로 수행하는 데 사용
퀴즈 1. industry_group 테이블에서 industry 열의 데이터가 0il에 해당하는 symbol 을 industry_group_symbol 테이블에서 검색한 다음, nasdaq_company 테이블에 서 해당 symbol의 company_name을 검색	where a.industry = 'oil' select a.industry, b.symbol, c.company_name From industry_group as a inner join  industry_group_symbol as b on a.num = b. num inner join  nasdaq_company as c on b.symbol = c. symbol
퀴즈 2. nasdaq_company 테이블에서 industry_group_symbol 테이블에 포함되지 않는 symbol, industry, company_name 목록을 검색	where s_b.symbol is null select a.symbol, a.industry, a.company_name from nasdaq_company as a  left outer join industry_group_symbol as b on a.symbol = b.symbol

 

04-2 서브 쿼리 

WHERE 문 서브 쿼리/ 단일 행 서브 쿼리/ 다중 행 서브 쿼리/ FROM문 서브쿼리/ SELECT 문 서브 쿼리

특징	반드시 소괄호로 감싸 사용 주 쿼리를 실행하기 전에 1번만 실행  비교 연산자에 서브 쿼리를 사용하는 경우 서브 쿼리를 오른쪽에 기술 내부에는 정렬 구문인 ORDER BY 문을 사용할 수 없음
WHERE 문 서브 쿼리	비교연산자(=,>,<,<>) 반환결과 1건 이하 반환결과 2건 이상인 경우 다중행 연산자 사용 (IN, ANY, EXISTS, ALL)
<단중행 서브쿼리>	Clustered Index Seek 연산은 클러스터된 인덱스를 사용하여 특정 symbol 값(즉, 'AMD', 'AMZN', 'MSFT')을 가진 데이터를 효율적으로 검색하는 과정
SELECT a.industry, c.symbol, c.company_name, c.ipo_year, c.sector FROM industry_group AS a  INNER JOIN industry_group_symbol AS b ON a.num = b.num  INNER JOIN nasdaq_company AS C ON b.symbol = c.symbol WHERE a. industry =N'자동차' ORDER BY symbol	SELECT *  FROM nasdaq_company  WHERE symbol IN (SELECT symbol FROM industry_group AS a INNER JOIN industry_group_symbol AS b ON a.num = b.num   WHERE a.industry = N'자동차'  )
EXIST문	WHERE EXIST 서브 쿼리 결과 값이 TRUE 면 메인 쿼리 실행
From 문 서브 쿼리	서브쿼리 결과를 조인 가능, 논리적 격리 가능 인라인 뷰
SELECT 문 서브 쿼리	집계함수와 함께 사용하는 경우가 많지만 성능문제 이슈 발생으로 지양 스칼라 쿼리
QUIZ 퀴즈 3. nasdaq_company 테이블에서 sector 열의 값이 Energy인 데이터 중에 industry_group_symbol 테이블에 포함되지 않은 symbol, company_name을 검색	select symbol, company_name   from industry_group_symbol    nasdaq_company where sector = 'Energey'
	SELECT a.symbol, a.company_name, s_b.symbol   FROM nasdaq_company AS a   LEFT OUTER JOIN (   SELECT symbol   FROM industry_group AS a   INNER JOIN industry_group_symbol AS b ON a.num = b.num   WHERE a.industry = 'Oil') AS s_b ON a.symbol =s_b.symbol   WHERE a.Sector = 'Energy' AND  s_b.symbol IS NULL;
	SELECT symbol, company_name   FROM nasdaq_company   WHERE sector = 'Energy' AND   symbol NOT IN (   SELECT symbol   FROM industry_group AS a   INNER JOIN industry_group_symbol AS b ON a.num = b.num WHERE a.industry = '0il');

 

04-3 공통 테이블 식

일반 CTE/재귀CTE

CTE	DB에 없는 TB이 필요할 때 사용하며, 바로 다음에 실행할 SELECT문에만 사용해야 한다는 특징