[SQL] 2개 이상의 테이블에서 쿼리를 작성하는 법, JOIN

SQL

[SQL] 2개 이상의 테이블에서 쿼리를 작성하는 법, JOIN

권벡터 2023. 6. 11. 18:32

기능

테이블 A와 B가 있을 때, 테이블 A의 열과, 테이블 B의의 정보를 포함해 검색할 수 있게 해준다.

Q: "테이블을 만들 때 처음부터 하나로 만들면 되지 않을까? 왜 번거롭게 테이블을 나누지?"

충분히 의문이 들 수 있는 요소이다. 하지만 테이블을 하나로 만들면 테이블 열에 데이터가 중복적으로 저장되는 문제가 발생한다. 앞에서 언급했듯이 데이터를 중복 저장하면 저장 공간의 문제와, 중복되는 데이터를 수정 할 경우 해당 데이터를 모두 찾아서 수정해야하는 번거로움이 생긴다. 이에 대한 자세한 설명은 '데이터 모델링' , '정규화' 에 설명되어 있다.

내부 조인

기본 개념

조인키에 해당하는 각 테이블의 열값을 비교해 조건에 맞는 값을 검색한다. (가장 많이 사용하는 JOIN이기도 하다.)

내부 조인의 기본 형식

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

예시

2개 테이블 조인

SELECT
a.symbol,
a.company_name,
a.ipo_year,
a.sector,
a.industry,
b.date,
b.[open],
b.[high],
b.[low],
b.[close],
b.adj_close,
b.volume
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'

해석

쿼리의 열을 생성한다.
'nasdaq_company' 테이블을 'a' 라고 정의한다.
'stock' 테이블을 'b'로 정의한 테이블의 symbol 데이터를 내부 조인한다.
'a' 테이블의 symbol 데이터 중에 'MSFT' 인 것을 검색한다.
그리고 'b' 테이블의 날짜가 2021년 10월 1일 ~ 2021년 11월 1일 사이의 데이터를 검색한다.

2개 이상의 조건 적용하여 조인

SELECT
a.symbol,
a.last_crawel_date,
b.date
FROM nasdaq_company AS a
INNER JOIN stock AS b ON a.symbol = b.symbol AND a.last_crawel_date = b.date
WHERE a.symbol = 'MSFT'

해석

쿼리의 열을 생성한다.
'nasdaq_company' 테이블을 'a' 라고 정의한다.
'stock' 테이블을 'b'로 정의한 테이블의 symbol 데이터와 a.last_crawel_date, b.date를 내부 조인한다.
'a' 테이블의 symbol 데이터 중에 'MSFT' 인 것을 검색한다.

3개 이상 테이블 조인

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 a.num = b.num
INNER JOIN nasdaq_company AS c ON b.symbol = c.symbol
WHERE a.industry = N'자동차'
ORDER BY symbol

해석

쿼리의 열을 생성한다.
'industry_group' 테이블을 'a' 라고 정의한다.
'a' 테이블은 'industry_group_symbol' 테이블을 'b'로 정의한 테이블의 num 데이터와 내부 조인한다.
'b' 테이블은 'nasdaq_company' 테이블을 'c'로 정의 한 테이블의 symbol 데이터와 내부 조인한다.
a.industry 가 '자동차'인 데이터를 검색한다.
symbol 열 기준으로 정렬한다.

외부 조인

기본 개념

다른 테이블에 있는 행에서 일치 항목이 아닌 행을 조합해 검색할 때 사용한다.

외부 조인은 세가지 옵션이 있다.

LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

외부 조인의 기본 형식

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

예시

LEFT OUTER JOIN 사용 (and NULL 필터링)

SELECT
a.symbol AS a_symbol,
b.symbol AS b_symbol
FROM nasdaq_company AS a
LEFT OUTER JOIN inasdaq_company AS b ON a.symbol = b.symbol
WHERE b.symbol IS NULL -- 해당 코드 추가 하면 a 테이블과 b 테이블의 중복 데이터는 제외된다.

해석

쿼리의 열을 생성한다.
'nasdaq_company' 테이블을 'a'로 정의한다.
'a' 테이블 기준으로 'industry_group_symbol' 테이블을 'b'로 정의한 테이블과 symbol 데이터를 외부 조인한다.
b.symbol 데이터가 NULL 인 데이터를 검색한다.

RIGHT OUTER JOIN 사용 (and NULL 필터링)

SELECT
a.symbol AS a_symbol,
b.symbol AS b_symbol
FROM industry_group_symbol AS a
RIGHT OUTER JOIN nasdaq_company AS b ON a.symbol = b.symbol
WHERE a.symbol IS NULL -- 해당 코드 추가 하면 a 테이블과 b 테이블의 중복 데이터는 제외된다.

해석

쿼리의 열을 생성한다.
'industry_group_symbol' 테이블을 'a'로 정의한다.
'a' 테이블 기준으로 'nasdaq_company' 테이블을 'b'로 정의한 테이블과 symbol 데이터를 외부 조인한다.
b.symbol 데이터가 NULL 인 데이터를 검색한다.

FULL OUTER JOIN 사용 (and NULL 필터링)

SELECT
a.symbol AS a_symbol,
b.symbol AS b_symbol
FROM nasdaq_company AS a
FULL OUTER JOIN inasdaq_company AS b ON a.symbol = b.symbol
WHERE a.symbol IS NULL
OR b.symbol IS NULL -- 해당 코드 추가 하면 a 테이블과 b 테이블의 중복 데이터는 제외된다.

해석

쿼리의 열을 생성한다.
'nasdaq_company' 테이블을 'a'로 정의한다.
'a' 테이블 기준으로 'industry_group_symbol' 테이블을 'b'로 정의한 테이블과 symbol 데이터를 외부 조인한다.
a.symbol 데이터가 NULL 인 데이터를 검색한다.
혹은 b.symbol 데이터가 NULL 인 데이터를 검색한다.

교차 조인

기본 개념

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

교차 조인의 기본 형식

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

예시

CROSS JOIN 쿼리 실행

CREATE TABLE doit_cross1(num INT);
CREATE TABLE doit_cross2(name NVARCHAR(10));
INSERT INTO doit_cross1 VALUES (1), (2), (3);
INSERT INTO doit_cross2 VALUES ('Do'), ('It'), ('SQL');
GO

SELECT
a.num, b.name
FROM doit_cross1 AS a
CROSS JOIN doit_cross2 AS b

해석

'doit_cross1' 와 'doit_cross2' 테이블을 생성한다.
a.num 과 b.num을 검색한다.
'doit_cross1' 테이블을 'a' 로 정의하고
'doit_cross2' 테이블을 'b' 로 정의해서 교차 조인한다.

셀프 조인

기본 개념

같은 테이블을 사용하는 특수한 조인이다. (해당 조인 사용 시 별칭 사용 필수)

예시

실행 예 1

SELECT
a.symbol, b.company_name
FROM nasdaq_company AS a
INNER JOIN nasdaq_company AS b
ON a.symbol = b.symbol

해석

a.symbol 과 b.company_name을 검색한다.
'nasdaq_company'테이블을 'a'로 정의하고 'nasdaq_company' 테이블을 'b'로 정의한 테이블 내부 조인한다.
a.symbol과 b symbol 를 내부 조인한다.

실행 예 2

SELECT
a.[date] AS a_date,
a.[close] AS a_close,
b.[date] AS b_date,
b.[close] AS b_close,
b.[close] - a.[close] AS diff_close
FROM stock AS a
LEFT OUTER JOIN stock AS b ON a.date = dateadd (day, -1, b.date)
WHERE a.symbol = 'MSFT' AND b.symbol = 'MSFT'
AND a.date >= '2021-10-01' AND a.date < '2021-11-01'
AND b.date >= '2021-10-01' AND b.date < '2021-11-01'

해석

쿼리의 열을 생성한다.
a.symbol 과 b.company_name을 검색한다.
'stock'테이블을 'a'로 정의하고 'stock' 테이블을 'b'로 정의한 테이블을 외부 조인한다.
'a' 테이블의 날짜와 'b' 테이블의 하루 뒤의 날짜를 나열하도록 한다.
a.symbol과 b symbol 이 'MSFT' 인 데이터를 검색한다.
그리고 a.date 가 2021년 10월 01일~ 2021년 11월 1일 사이인 데이터를 검색한다.
그리고 b.date 가 2021년 10월 01일~ 2021년 11월 1일 사이인 데이터를 검색한다.

참고

데이터 모델링

추후 포스트 예정

정규화

추후 포스트 예정

ON 문과 WHERE 문의 차이

ON 문 : 조인할 때 조인 조건을 위해 사용한다.

WHERE 문: 조인을 완료한 상태에서 조건에 맞는 값을 가져오고자 사용한다.

그럼 ON문에 다양한 조건을 사용하여 WHERE문처럼 사용하면 안될까 라는 생각이 들 수도 있는데, 조인 조건을 만족하는 데이터 매칭 과정에서 오차가 발생하므로 WHERE 사용해야 할때는 그냥 WHERE 쓰도록 하자.

별칭을 사용하지 않으면 어떻게 될까?

조인을 할 때 열 이름이 유일하지 않으면 데이터베이스 엔진은 "도대체 무슨 데이터를 말하는거지?" 라고 혼동하므로 오류를 발생시킨다. 그러므로 열 이름이 중복되지 않도록 별칭을 사용하도록 하자.