[데이터베이스] MSSQL 조인 방법, inner join과 outer join 차이

관계형 데이터베이스에서 조인 (JOIN)이란, 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것을 의미한다.

 

데이터베이스에서 테이블은 정규화되어 여러 개의 테이블로 나뉘어 있으며, 

이때 필요한 데이터를 결합하여 원하는 데이터를 추출할 수 있다.

 

조인에는 여러가지 종류가 있다. 

Inner Join =  (A ∩ B)

A INNER JOIN B on A.key = B.key

결과 집합은 교집합으로 중복을 허용한다. 

 

Inner join

OUTER JOINs 은

차집합 + 교집합으로, 교집합은 inner join 차집합은 NULL 값 열로 결합한다. 조인 조건은 교집합에 대한 조건으로 where 절의 위치나 조인 순서에 따라 결과나 성능이 달라진다. 잘못된 OUTER JOIN은 사용하지 않도록 해야한다.

Left Outer Join  = (A - B) + (A ∩ B)

차집합만 구하고자 할 때에는

A LEFT OUTER JOIN B On A.Key = B.Key WHERE B.key IS NULL

Right Outer Join  = (B - A) + (A ∩ B)

Cross Join = Cartesian Product

• N x M
• 조인 조건은 명시적으로 없지만, 물리적으로 행 x 행
• 비 관계 열 기준 복제 시,  전체 행을 원하는 수만큼 복제 시 등 사용 가능

SELECT 
	*
FROM 
	(VALUES (1, 'A'), (2, 'B'), (3, 'C')) AS t1 (A, B)
CROSS JOIN 
	(VALUES ('DB', 100)) AS t2 (C, D);

-- 현재 데이터 

SELECT 
	ProductID
,	UnitPrice
FROM 
	dbo.[Order Details] As od
WHERE
	OrderID <= 10250
order by 1;

SELECT AvgPrice = AVG(UnitPrice) FROM dbo.[Order Details] AS od;

-- Cross Join의 활용: 평균가 대비 단가별 편차
SELECT
	ProductID
,	UnitPrice

/* 아래 두 가지 열 값을 생성 */
,	av.AvgPrice
,	av.AvgPrice - UnitPrice as 'diff'
FROM 
	dbo.[Order Details] As od
CROSS JOIN (SELECT AvgPrice = AVG(UnitPrice) FROM dbo.[Order Details] AS od) AS av
WHERE
	OrderID <= 10250;

 

FULL OUTER JOIN 

집합의 결과는, A ∪ B 합집합이다.

 

-- 예제 테이블 생성
CREATE TABLE dbo.Depts 
(
	DeptCode	char(4)	PRIMARY KEY
,	DeptName	varchar(20)
);

CREATE TABLE dbo.Emps
(
	EmpID		int	PRIMARY KEY
,	DeptCode	char(4)
);

INSERT Depts
VALUES
	('0001', '인사')
,	('0002', '회계')
,	('0003', '영업1팀')
,	('0004', '구매1팀')
,	('0005', '생산1팀')
,	('0006', 'DBA팀');

INSERT Emps
VALUES 
	(1, '0001')
,	(2, '0002')
,	(3, '0004')
,	(4, NULL)
,	(5, '0005')
,	(6, '0006');


-- 차집합
SELECT * 
FROM dbo.Depts AS d 
WHERE NOT EXISTS (SELECT 1 
				FROM dbo.Emps AS e 
				WHERE e.DeptCode = d.DeptCode);

SELECT * 
FROM dbo.Emps AS e
WHERE NOT EXISTS (SELECT 1 
				FROM dbo.Depts AS d 
				WHERE e.DeptCode = d.DeptCode);

-- 교집합
SELECT * 
FROM dbo.Emps AS e
INNER JOIN dbo.Depts AS d ON e.DeptCode = d.DeptCode;

-- Full Outer Join 
SELECT * 
FROM dbo.Emps AS e
FULL JOIN dbo.Depts AS d ON e.DeptCode = d.DeptCode;

 

OUTER와 INNER JOIN의 차이

SELECT 
	o.OrderID, * 
FROM
	dbo.Customers AS c -- 부모 테이블, Outer join 의 기준이 됨
LEFT JOIN
	dbo.Orders AS o ON c.CustomerID = o.CustomerID -- Orders 테이블은 자식 테이블
WHERE
	o.OrderID <= 10250;

 

Customers 테이블의 CustomerID 는 Orders 테이블에 존재하지 않을 수 있지만,

Orders 테이블의 CustomerID는 Customers 테이블에 없으면, 무결성에 위반되는 것이다. 

where 절의 o.OrderID <= 10250 의 조건은 outer join의 의미론적으로 맞지 않는다.

따라서 옵티마이저는 알아서 inner join으로 처리해 주게 된다.

이로 인해 결과는 문제 없을 수 있지만, 성능이 떨어지게 되므로 불필요한 outer join은 사용하지 않도록 해야한다.