[MSSQL] INNER JOIN

SQL Basic

[MSSQL] INNER JOIN

ssh9308 2022. 5. 13. 10:30

1. 내부조인 (Inner Join)

내부조인은 두 개의 논리적 쿼리 프로세싱 단계를 수행한다.

우선, 두 입력 테이블간에 카티션 곱을 적용한 후, 지정한 술어에 따라 행들을 필터링 한다.

크로스 조인과 같이 내부 조인 역시 ANSI SQL-92 및 ANSI SQL-89 두개의 표준 문법 형태로 사용할 수 있다.

예시를 위해 아래와 같은 테이블을 생성하여 연습해보자.

-- 회원 테이블
CREATE TABLE dbo.TB_CUST( 
	cust_no INT NOT NULL, --회원번호 
	cust_name NVARCHAR(100) NOT NULL --회원이름 
	CONSTRAINT PK_TB_CUST PRIMARY KEY(cust_no) 
); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(1, 'MIKE'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(2, 'OBAMA'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(3, 'TRUMP'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(4, 'MESSI'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(5, 'RONALDO'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(6, 'MALDINI'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(7, 'SANCHEZ');

--주문 테이블
CREATE TABLE dbo.TB_ORDER( 
	order_no INT NOT NULL, --회원의 주문번호 
	cust_no INT NOT NULL, -- 회원번호 
	product_no INT NOT NULL -- 제품번호 
	CONSTRAINT PK_TB_ORDER PRIMARY KEY(ORDER_NO) 
); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201901, 1, 701); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201902, 1, 702); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201904, 2, 704); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201905, 3, 701); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201906, 4, 703); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201907, 5, 701);

ANSI SQL-92 문법

ANSI SQL-92 문법을 이용한다면, 테이블들 이름 사이에 INNER JOIN이라는 키워드를 지정해야 한다.

INNER라는 키워드는 선택적으로 사용 가능하다.

내부 조인이 조인의 기본값이기 때문에 JOIN 키워드만 지정해도 된다.

행들을 필터링하기 위한 술어는 ON이라는 절 뒤에 지정해야 한다.

이 술어를 조인 조건 이라고도 한다.

아래의 쿼리는 어떠한 고객이 어떤 상품을 주문 했는지 조회해주는 쿼리이다.

SELECT * FROM dbo.TB_CUST c WITH(NOLOCK) 
INNER JOIN dbo.TB_ORDER o WITH(NOLOCK) ON c.cust_no = o.cust_no

관계형 대수를 바탕으로 내부조인의 원리를 설명하면(논리적 방식),

우선 두 테이블에 대해 카티션 곱을 수행한다.

(7명의 회원 X 5개의 구매내역 = 35)

그 다음 join조건에 있는 c.cust_no = o.cust_no 라는 술어를 기반으로 행을 필터링한다.

해당 조건으로 필터링 하면 총 6개의 행이 나오게 된다.

이러한 방식이 조인이 처리되는 논리적인 방식이다.

(*물리적인 방식은 이와는 다르다.)

ANSI SQL-89 문법

내부 조인 역시 ANSI SQL-89 문법을 사용할 수 있다.

테이블 이름들 사이에 쉼표를 지정하고, 쿼리의 WHERE 절 부분에 조인조건을 지정하면 된다.

SELECT * 
FROM dbo.TB_CUST c WITH(NOLOCK),dbo.TB_ORDER o WITH(NOLOCK) 
WHERE c.cust_no = o.cust_no

마찬가지로, 두 문법 모두 표준 문법이며 SQL Server 에서도 모두 지원한다.

엔진에서 동일한 방식으로 해석하기 때문에 두 방식 간의 성능 차이는 전혀 없다.

하지만 첫 번째 방식(ANSI SQL-92 문법)이 더 안전하다. 이유는 뒤에서 알아보자.

2. 내부조인의 안정성

ANSI SQL-92 조인 문법이 여러 면에서 안전하기 때문에 이 방식을 사용할 것을 강력히 권장한다.

내부 조인 쿼리를 작성하고자 할 때, 실수로 조인 조건을 쓰는 것을 잊었다고 생각해보자

ANSI SQL-92 문법에서는 쿼리가 유효하지 않다고 처리되기 때문에 파싱 단계에서 오류가 발생된다.

SELECT * FROM dbo.TB_CUST c WITH(NOLOCK) 
INNER JOIN dbo.TB_ORDER o WITH(NOLOCK)

하지만 이를 ANSI SQL-89 버전으로 작성하게 되면

크로스 조인으로 인식하기 때문에 오류 없이 정상적으로 수행된다.

SELECT *  
FROM dbo.TB_CUST c WITH(NOLOCK),dbo.TB_ORDER o WITH(NOLOCK)

쿼리가 실패로 처리되지 않기 때문에, 논리적인 오류를 빨리 인지하지 못할 수도 있다.

최종적으로 애플리케이션 사용자도 잘못된 결과를 사용하게 될 수도 있다.

예제는 짧아서 어떤방식을 써도 눈에 들어오지만, 실제 업무환경은 더욱 방대하고 복잡하므로

그러한 경우에는 조인 조건을 지정하는 것을 잊어버리는 것과 같은 실수를 범할 확률이 높다.

3. 복합조인 (COMPOSITE JOIN)

복합조인(composite join)은 두 개 이상의 특성을 포함하는 술어를 기반으로 한다.

이 조인은 일반적으로 기본키 및 참조키 관계를 가지며,

이 관계가 복합적인(두 개 이상의 컬럼으로 키가 지정된) 테이블을 조인하는 경우에 사용된다.

아래와 같은 테이블에서 직원의 출퇴근 상황이 알고싶다고 했을때, 복합조인을 사용할 수 있다.

아래의 테스트 테이블을 기반으로 이해해보자.

-- 직원 테이블
CREATE TABLE dbo.TEST_STAFF 
( 
	staff_name NVARCHAR(5) NOT NULL, -- 직원 이름 
	team VARCHAR(20) NOT NULL, --소속 팀 
	enter_date DATE NOT NULL, -- 입사일 
	out_date DATETIME NULL, -- 퇴사일 
	staff_address NVARCHAR(50) NULL -- 집주소  
)

ALTER TABLE dbo.TEST_STAFF ADD CONSTRAINT PK__TEST_STAFF__STAFF_NAME__TEAM PRIMARY KEY (staff_name,team)

INSERT INTO dbo.TEST_STAFF VALUES (N'이도윤','HR','2009-08-09',NULL,N'사랑시 고백구 행복동') 
INSERT INTO dbo.TEST_STAFF VALUES (N'방성필','DEV','2012-04-05',NULL,N'사랑시 고백구 희망동') 
INSERT INTO dbo.TEST_STAFF VALUES (N'성지현','FM','2015-01-13',NULL,N'사랑시 고백구 기대동') 
INSERT INTO dbo.TEST_STAFF VALUES (N'홍준표','PR','2018-04-09',NULL,N'사랑시 정열구 용기동') -- 동명이인 
INSERT INTO dbo.TEST_STAFF VALUES (N'홍준표','DEV','2020-10-13',NULL,N'사랑시 우정구 성실동') -- 동명이인


-- 직원 출퇴근 시간 테이블
CREATE TABLE dbo.TEST_STAFF_ATTENDANCE 
( 
	att_seq BIGINT IDENTITY(1,1) NOT NULL, 
	staff_name NVARCHAR(5) NOT NULL, -- 직원이름 
	team VARCHAR(20) NOT NULL, -- 소속 팀 
	enter_time SMALLDATETIME NULL, -- 출근시간 
	exit_time SMALLDATETIME NULL -- 퇴근시간 
)

ALTER TABLE dbo.TEST_STAFF_ATTENDANCE ADD CONSTRAINT PK__QOOTEST_STAFF_ATTENDANCE__ATT_SEQ PRIMARY KEY (att_seq)

INSERT INTO dbo.TEST_STAFF_ATTENDANCE VALUES (N'이도윤','HR','2020-04-29 09:12:13','2020-04-29 18:33:13') 
INSERT INTO dbo.TEST_STAFF_ATTENDANCE VALUES (N'이도윤','HR','2020-04-30 09:34:22','2020-04-30 18:30:43') 
INSERT INTO dbo.TEST_STAFF_ATTENDANCE VALUES (N'방성필','DEV','2020-04-29 09:32:12','2020-04-29 18:30:02') 
INSERT INTO dbo.TEST_STAFF_ATTENDANCE VALUES (N'방성필','DEV','2020-04-30 09:10:03','2020-04-30 18:30:43') 
INSERT INTO dbo.TEST_STAFF_ATTENDANCE VALUES (N'성지현','FM','2020-04-29 09:08:00','2020-04-29 18:30:15') 
INSERT INTO dbo.TEST_STAFF_ATTENDANCE VALUES (N'성지현','FM','2020-04-30 09:18:44','2020-04-30 18:31:43') 
INSERT INTO dbo.TEST_STAFF_ATTENDANCE VALUES (N'홍준표','PR','2020-04-29 10:00:04','2020-04-29 19:00:15') 
INSERT INTO dbo.TEST_STAFF_ATTENDANCE VALUES (N'홍준표','PR','2020-04-30 09:14:54','2020-04-30 21:02:13') 
INSERT INTO dbo.TEST_STAFF_ATTENDANCE VALUES (N'홍준표','DEV','2020-04-29 09:01:40','2020-04-29 18:30:15') 
INSERT INTO dbo.TEST_STAFF_ATTENDANCE VALUES (N'홍준표','DEV','2020-04-30 09:14:55','2020-04-30 19:30:44')

이때까지 봐온 INNER JOIN과 차별점은 테이블의 기본키가

하나의 컬럼이 아닌 복합컬럼으로 지정되어 있다는 것이다.

어떤 직원이 몇시에 출근하고 몇시에 퇴근했는지 정보와

직원의 기본정보를 출력하는 쿼리문을 만든다면 아래와 같다.

SELECT 
	q.staff_name 
,	q.team 
,	q.enter_date 
,	q.out_date 
,	q.staff_address 
,	a.att_seq 
,	a.staff_name 
,	a.team 
,	a.enter_time, 
	a.exit_time 
FROM dbo.TEST_STAFF q WITH(NOLOCK) 
INNER JOIN TEST_STAFF_ATTENDANCE a WITH(NOLOCK) ON q.staff_name = a.staff_name

하지만, 위의 쿼리의 문제점이 있다.

바로 TEST_STAFF 테이블에서 기본키가 하나의 컬럼에만 걸려있다는게 아니라는것이다.

하지만, JOIN조건으로는 하나의 컬럼만을 참조하고 있다. 결과는 아래와 같다.

아래와 같은 조인결과의 문제점은 동명이인의 정보가 서로 얽혀있어서

잘못된 결과를 도출해내고 있다는 것이다.

(부서정보가 다르므로 다른 사람이라고 봐야한다.)

TEST_STAFF 테이블의 기본키를 모두 JOIN 조건에 참조해준다면 원하는 결과가 나오게 된다.

SELECT 
	q.staff_name 
,	q.team 
,	q.enter_date 
,	q.out_date 
,	q.staff_address 
,	a.att_seq 
,	a.staff_name 
,	a.team 
,	a.enter_time, 
	a.exit_time 
FROM dbo.TEST_STAFF q WITH(NOLOCK) 
INNER JOIN TEST_STAFF_ATTENDANCE a WITH(NOLOCK) ON q.staff_name = a.staff_name AND q.team = a.team

이처럼 결과가 다른 이유는 TEST_STAFF 테이블에 동명이인이 있기 때문이다.

즉, 이처럼 한 테이블의 기본키가 두개의 열로 지정된 경우에는 유일성을 보장하기 위해

JOIN 조건에도 기본키를 모두 참조해야 원하는 결과를 얻을 수 있음을 알 수 있다.

4. NON-EQUI JOIN & EQUI JOIN

조인이 하나의 동일 연산자(=)만을 포함할 때 이 조인을 equi 조인이라고 한다.

동일 연산자 외에 다른 연산자가 하나라도 포함한다면

해당 조인을 "non-equi" 조인이라고 부른다.

이해를 위해 아래 예시를 참고하자.

--고객 테이블
CREATE TABLE dbo.TB_CUST( 
	cust_no INT NOT NULL, -- 고객번호 
	cust_name NVARCHAR(100) NOT NULL -- 고객이름 
	CONSTRAINT PK__TB_CUST__CUST_NO PRIMARY KEY(cust_no) 
);

INSERT INTO TB_CUST(cust_no, cust_name) VALUES(1, 'MIKE'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(2, 'OBAMA'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(3, 'TRUMP'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(4, 'MESSI'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(5, 'RONALDO'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(6, 'MALDINI'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(7, 'SANCHEZ');


SELECT  
	t1.cust_no 
,	t1.cust_name 
,	t2.cust_no 
,	t2.cust_name 
FROM dbo.TB_CUST t1 WITH(NOLOCK) 
INNER JOIN dbo.TB_CUST t2 WITH(NOLOCK) ON t1.cust_no < t2.cust_no
ORDER BY t1.cust_no

ON 절에 지정된 술어를 살펴보자. 크로스 조인을 사용하였다면,

결과에는 셀프쌍(1 대 1)과 미러쌍(1 대 2 와 2 대 1)도 포함될 것이다.

SELECT  
	t1.cust_no 
,	t1.cust_name 
,	t2.cust_no 
,	t2.cust_name 
FROM dbo.TB_CUST t1 WITH(NOLOCK) 
CROSS JOIN dbo.TB_CUST t2 WITH(NOLOCK) 
ORDER BY t1.cust_no

위의 결과가 CROSS JOIN의 결과인데

여기서 INNER JOIN의 술어절인 부등호를 적용하면

[ t1.cust_no < t2.cust_no ]

셀프 쌍(두 값이 같은 쌍)은 두 값이 같을 때만 나타나기 때문에 제거된다.

또한 미러 쌍의 경우(셀프쌍이 아닌 쌍),

두 경우 중 왼쪽 값이 오른쪽 값보다 적을 때에만

나타나기 때문에 두 경우 중 하나만 나타나게 된다.

이 예에서는 크로스 조인으로 49개의 쌍이 만들어지지만,

NON-EQUI JOIN 을 사용한 쿼리에서는 21개의 고유한 쌍만 나타나게 된다.

5. MULTI JOIN

조인 테이블 연산자는 단지 두 테이블에 대해서만 연산을 수행한다.

하지만 단일 쿼리는 여러 조인을 가질 수 있다.

일반적으로 하나 이상의 테이블들이 FROM 절에 나타날 때에는

테이블들은 논리적으로 왼쪽에서 오른쪽순으로 처리된다.

즉, 첫 번째 테이블 연산자의 결과는 두번째 테이블 연산자의 왼쪽 입력 값으로 간주되어 처리되며,

두 번째 테이블 연산자의 결과는 세 번째 테이블 연산자의 왼쪽 입력 값으로 간주되어 처리되는방식이다.

따라서, FROM 절에 여러 조인이 있으면 두 개의 기본 테이블에 대한 첫 번째 조인이 수행되며,

나머지 다른 조인들은 앞의 조인 결과를 왼쪽 입력 값으로 취해서 결과를 얻게 된다.

크로스 조인과 내부 조인의 경우, 데이터베이스 엔진은 최적화를 위해

조인의 순서를 내부적으로 변경하기도 한다.

이렇게 변경하더라도 쿼리 결과의 정합성에는 영향을 미치지 않기때문이다.

아래는 고객테이블, 제품테이블, 주문 테이블 이 존재한다.

해당 세 테이블의 관계를 통해 어떤 고객이 어떤상품을 샀는지 알 수 가 있다.

아래의 테이블을 보면 마지막 주문테이블에서

고객테이블의 기본키와 제품 테이블의 기본키를 참조하는 것을 볼수 있다.

-- 고객테이블
CREATE TABLE dbo.TB_CUST( 
	cust_no INT NOT NULL, -- 고객번호 
	cust_name NVARCHAR(100) NOT NULL -- 고객이름 
	CONSTRAINT PK__TB_CUST__CUST_NO PRIMARY KEY(cust_no) 
);

INSERT INTO TB_CUST(cust_no, cust_name) VALUES(1, 'MIKE'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(2, 'OBAMA'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(3, 'TRUMP'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(4, 'MESSI'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(5, 'RONALDO'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(6, 'MALDINI'); 
INSERT INTO TB_CUST(cust_no, cust_name) VALUES(7, 'SANCHEZ'); 

--제품 테이블
CREATE TABLE dbo.TB_PRODUCT( 
	product_no INT NOT NULL, -- 제품 번호   
	product_name NCHAR(100) NOT NULL -- 제품 이름 
	CONSTRAINT PK__TB_PRODUCT__PRODUCT_NO PRIMARY KEY(product_no) 
);

INSERT INTO TB_PRODUCT(product_no, product_name) VALUES(701, 'MacBook'); 
INSERT INTO TB_PRODUCT(product_no, product_name) VALUES(702, 'MagicMouse'); 
INSERT INTO TB_PRODUCT(product_no, product_name) VALUES(703, 'IPad'); 
INSERT INTO TB_PRODUCT(product_no, product_name) VALUES(704, 'IPhone'); 

-- 주문 테이블
CREATE TABLE dbo.TB_ORDER( 
	order_no INT NOT NULL, --주문 번호 
	cust_no INT NOT NULL, -- 주문을 한 고객 번호 (TB_CUST 의 기본키를 참조로 함) 
	product_no INT NOT NULL -- 주문한 고객이 산 제품 번호(TB_PRODUCT 의 기본키를 참조로 함) 
	CONSTRAINT PK__TB_ORDER__ORDER_NO PRIMARY KEY(order_no) 
);

INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201901, 1, 701); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201902, 1, 702); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201904, 2, 704); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201905, 3, 701); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201906, 4, 703); 
INSERT INTO TB_ORDER(order_no, cust_no, product_no) VALUES(201907, 5, 701);

만약 어떤 고객이 어떤 상품을 구매했는지 알고싶다면,

다음과 같이 세개의 테이블을 조인해야 한다.

SELECT  
	c.cust_name  
,	p.product_name  
,	p.product_no  
,	o.order_no  
,	o.cust_no  
,	o.product_no  
,	o.order_date 
FROM dbo.TB_CUST c WITH(NOLOCK)  
INNER JOIN dbo.TB_ORDER o WITH(NOLOCK) ON c.cust_no = o.cust_no  
INNER JOIN dbo.TB_PRODUCT p WITH(NOLOCK) ON p.product_no = o.product_no

논리적인 측면으로 접근했을때, JOIN은 카티젼 곱 후에 필터링 해주는 것이므로

일단 카티전 곱 결과를 봐주면 아래와 같다. (7 x 4 x 6 = 168 개의 행이 나오게 된다.)

아래는 필터링 조건이다.