SQL 기초 - 10. 조인 (단순 조인, 외부 조인, 다중 조인 등 SQL 조인 예제)

이 글은 '김상형의 SQL 정복'의 예제 코드를 설명하는 방식으로 SQL 쿼리 예제를 소개하고 있습니다.

 

SQL 기초 - 10. 조인 (단순 조인, 외부 조인, 다중 조인 등 SQL 조인 예제)

-- 실습을 위한 테이블들을 만들어 보자
CREATE TABLE tCar
(
	car VARCHAR(30) NOT NULL,		-- 이름
	capacity INT NOT NULL,			-- 배기량
	price INT NOT NULL,			-- 가격
	maker VARCHAR(30) NOT NULL		-- 제조사
);

INSERT INTO tCar (car, capacity, price, maker) VALUES ('소나타', 2000, 2500, '현대');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('티볼리', 1600, 2300, '쌍용');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('A8', 3000, 4800, 'Audi');
INSERT INTO tCar (car, capacity, price, maker) VALUES ('SM5', 2000, 2600, '삼성');

CREATE TABLE tMaker
(
	maker VARCHAR(30) NOT NULL,		-- 회사
	factory CHAR(10) NOT NULL,	    	-- 공장
	domestic CHAR(1) NOT NULL		-- 국산 여부. Y/N
);

INSERT INTO tMaker (maker, factory, domestic) VALUES ('현대', '부산', 'y');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('쌍용', '청주', 'y');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('Audi', '독일', 'n');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('기아', '서울', 'y');

-- 아무것도 쓰지 않으면 CROSS JOIN이 된다. CROSS JOIN은 이름처럼 곱연산처럼 작동한다.
SELECT * FROM tCar, tMaker;

SELECT * FROM tCar CROSS JOIN tMaker;

SELECT * FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

SELECT tCar.car, tCar.price, tMaker.maker, tMaker.factory FROM tCar, tMaker 
WHERE tCar.maker = tMaker.maker;

SELECT tCar.*, tMaker.factory FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

SELECT car, price, tMaker.maker, factory FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

SELECT car, price, maker, factory FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

SELECT C.car, C.price, M.maker, M.factory FROM tCar C, tMaker M WHERE C.maker = M.maker;

SELECT A.car, A.price, B.maker, B.factory FROM tCar A, tMaker B WHERE A.maker = B.maker;

-- INNER JOIN은 ON절을 같이 활용하여 겹치는 부분을 선택한다.
SELECT C.car, C.price, M.maker, M.factory FROM tCar C INNER JOIN tMaker M 
ON C.maker = M.maker;

SELECT C.car, C.price, M.maker, M.factory FROM tMaker M INNER JOIN tCar C 
ON M.maker = C.maker;

-- 이때, ON절 대신 매치하는 녀석을 USING( )안에 넣어서 쓸 수도 있다.
SELECT C.car, C.price, maker, M.factory FROM tCar C INNER JOIN tMaker M USING(maker);

-- LEFT OUTER JOIN은 왼쪽 테이블 전부와, 오른쪽에서 매치하는 녀석을 채우는 조인이다.
SELECT C.car, C.price, M.maker, M.factory FROM tCar C LEFT OUTER JOIN tMaker M 
ON C.maker = M.maker;

-- RIGHT OUTER JOIN은 오른쪽 테이블 전부와, 왼쪽에서 매치하는 녀석을 채우는 조인이다.
SELECT C.car, C.price, M.maker, M.factory FROM tCar C RIGHT OUTER JOIN tMaker M 
ON C.maker = M.maker;

SELECT C.car, C.price, M.maker, M.factory FROM tMaker M LEFT OUTER JOIN tCar C 
ON C.maker = M.maker;

-- FULL OUTER JOIN은 오른쪽 테이블과 왼쪽 테이블 전부 중 매치되는 녀석을 선택한다.
SELECT C.car, C.price, M.maker, M.factory FROM tCar C FULL OUTER JOIN tMaker M 
ON C.maker = M.maker;

SELECT * FROM tCar C 
INNER JOIN tMaker M ON C.maker = M.maker 
INNER JOIN tCity T ON M.factory = T.name;

SELECT C.car, M.factory, T.area FROM tCar C 
INNER JOIN tMaker M ON C.maker = M.maker 
INNER JOIN tCity T ON M.factory = T.name;

SELECT * FROM tCar C 
LEFT OUTER JOIN tMaker M ON C.maker = M.maker 
LEFT OUTER JOIN tCity T ON M.factory = T.name;

SELECT * FROM tMaker M 
INNER JOIN tCity T ON M.factory = T.name 
INNER JOIN tCar C ON M.maker = C.maker;

SELECT * FROM tMaker M 
LEFT OUTER JOIN tCity T ON M.factory = T.name 
LEFT OUTER JOIN tCar C ON M.maker = C.maker;

SELECT maker FROM tCar WHERE car = '티볼리';
SELECT factory FROM tMaker WHERE maker = '쌍용';

SELECT factory FROM tMaker WHERE maker = 
(SELECT maker FROM tCar WHERE car = '티볼리');

SELECT * FROM tCar C INNER JOIN tMaker M ON M.maker = C.maker;

SELECT * FROM tCar C INNER JOIN tMaker M ON M.maker = C.maker AND C.car = '티볼리';

SELECT M.factory FROM tCar C INNER JOIN tMaker M 
ON M.maker = C.maker AND C.car = '티볼리';

SELECT M.factory, C.price FROM tCar C INNER JOIN tMaker M 
ON M.maker = C.maker AND C.car = '티볼리';

-- 조인 대신 서브쿼리를 활용할 수도 있지만, 장기적으로 추천하는 방향은 아니다.
SELECT factory, price FROM tMaker WHERE maker = 
(SELECT maker FROM tCar WHERE car = '티볼리');

-- 서브쿼리를 이용한 표현은 조건이 많아지면 길어지고 복잡해지고 느려지는 단점이 있다.
SELECT factory, (SELECT price FROM tCar WHERE car = '티볼리') AS price 
FROM tMaker WHERE maker = (SELECT maker FROM tCar WHERE car = '티볼리');

-- INNER JOIN에서는 AND와 WHERE가 같은 출력 결과를 보인다.
-- 다만, AND는 조인시 관여를 하고, WHERE는 조인이 끝나고 출력시 관여를 한다.
-- 따라서 결과는 같아도 실행시간은 차이를 보일 수 있으며, OUTER JOIN시에는 결과값도 차이가 난다.
SELECT C.*, M.factory, M.domestic FROM tCar C INNER JOIN tMaker M 
ON M.maker = C.maker AND C.car = '티볼리';

INSERT INTO tCar (car, capacity, price, maker) VALUES ('티볼리', 1800, 2600, '쌍용');

SELECT C.*, M.factory, M.domestic FROM tCar C LEFT OUTER JOIN tMaker M 
ON C.maker = M.maker;

INSERT INTO tCar (car, capacity, price, maker) VALUES ('소나타', 2400, 2900, '현대');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('현대', '울산', 'y');
INSERT INTO tMaker (maker, factory, domestic) VALUES ('현대', '마산', 'y');

SELECT * FROM tCar C INNER JOIN tMaker M ON C.maker = M.maker;

SELECT * FROM tCar C INNER JOIN tMaker M ON C.maker = M.maker WHERE C.capacity = 2000;

SELECT * FROM tCar C INNER JOIN tMaker M ON C.maker = M.maker AND C.capacity = 2000;

SELECT * FROM tCar C LEFT OUTER JOIN tMaker M ON C.maker = M.maker 
WHERE C.capacity = 2000;

SELECT * FROM tCar C LEFT OUTER JOIN tMaker M 
ON C.maker = M.maker AND C.capacity = 2000;

SELECT * FROM tCar C LEFT OUTER JOIN tMaker M 
ON C.maker = M.maker AND C.capacity = 2000 WHERE C.price > 2800 ORDER BY price DESC;

SELECT * FROM tCar WHERE capacity > 2000 C LEFT JOIN tMaker M ON C.maker = M.maker;

SELECT * FROM (SELECT * FROM tCar WHERE capacity > 2000) C 
LEFT JOIN tMaker M ON C.maker = M.maker;

SELECT * FROM tCar C LEFT JOIN tMaker M ON C.maker = M.maker WHERE C.capacity > 2000;

SELECT * FROM (SELECT * FROM tCar WHERE capacity > 2000) C LEFT JOIN 
(SELECT * FROM tMaker WHERE factory = '울산') M ON C.maker = M.maker;

SELECT * FROM (SELECT * FROM tCar WHERE capacity > 2000) C LEFT JOIN 
tMaker M ON C.maker = M.maker WHERE M.factory = '울산';

SELECT * FROM tMember, tOrder;

SELECT * FROM tMember M, tOrder O WHERE M.member = O.member;
SELECT * FROM tMember M INNER JOIN tOrder O ON M.member = O.member;

SELECT M.addr, M.member, O.item, O.num, O.orderDate FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member;

SELECT M.addr, M.member, O.item, O.num, O.orderDate FROM tMember M 
LEFT OUTER JOIN tOrder O ON M.member = O.member;

SELECT M.addr, O.member, O.item, O.num, O.orderDate FROM tMember M 
RIGHT OUTER JOIN tOrder O ON M.member = O.member;

SELECT M.addr, M.member, O.member, O.item, O.num, O.orderDate FROM tMember M 
FULL OUTER JOIN tOrder O ON M.member = O.member;

SELECT item, price FROM tItem WHERE item = 
(SELECT item FROM tOrder WHERE member='춘향');

SELECT * FROM tItem I INNER JOIN tOrder O ON O.item = I.item;

SELECT * FROM tItem I INNER JOIN tOrder O ON O.item = I.item WHERE O.member = '춘향';
SELECT * FROM tItem I INNER JOIN tOrder O ON O.item = I.item AND O.member = '춘향';

SELECT O.item, I.price FROM tItem I 
INNER JOIN tOrder O ON O.item = I.item WHERE O.member = '춘향';

SELECT O.item, I.price, O.num FROM tItem I 
INNER JOIN tOrder O ON O.item = I.item WHERE O.member = '향단';

SELECT item, price, num FROM tItem WHERE item IN 
(SELECT item FROM tOrder WHERE member='향단');

SELECT item, price, (SELECT num FROM tOrder O WHERE O.item = I.item) 
FROM tItem I WHERE item IN (SELECT item FROM tOrder WHERE member='향단');

SELECT * FROM tMember;

SELECT * FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member; 

SELECT * FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member 
INNER JOIN tItem I ON I.item = O.item;

SELECT * FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member 
INNER JOIN tItem I ON I.item = O.item
INNER JOIN tCategory C ON I.category = C.category;

SELECT M.member, O.item, O.num, O.orderDate, I.price, C.delivery FROM tMember M 
INNER JOIN tOrder O ON M.member = O.member 
INNER JOIN tItem I ON I.item = O.item
INNER JOIN tCategory C ON I.category = C.category;

SELECT M.member, O.item, O.num, O.orderDate, I.price, C.delivery 
FROM (SELECT * FROM tMember WHERE age > 19) M 
INNER JOIN tOrder O ON M.member = O.member 
INNER JOIN tItem I ON I.item = O.item
LEFT OUTER JOIN tCategory C ON I.category = C.category AND C.category != '성인'
WHERE I.price * O.num > 100000 ORDER BY M.member;

SELECT M.member, O.item, O.num, I.price FROM tMember M 
INNER JOIN tOrder O ON M.member = O.MEMBER 
INNER JOIN tItem I ON I.item = O.item;

-- 자주 쓰는 조인 테이블은 인라인 뷰로 묶은 후, 별명을 붙이면 재활용이 용이하다.
SELECT member, num * price AS total, item FROM
(
    SELECT M.member, O.item, O.num, I.price FROM tMember M 
    INNER JOIN tOrder O ON M.member = O.MEMBER 
    INNER JOIN tItem I ON I.item = O.item
) A;

-- TRIM( )을 통해 CHAR(10)에서 뒤의 공백을 없애고, SUBSTR으로 도시 이름(앞의 두 글자)를 추출했다.
SELECT * FROM
(
	SELECT M.member, M.addr, O.item, O.num, O.orderDate, I.price, C.delivery 
	FROM (SELECT * FROM tMember WHERE age > 19) M 
	INNER JOIN tOrder O ON M.member = O.member 
	INNER JOIN tItem I ON I.item = O.item
	LEFT OUTER JOIN tCategory C ON I.category = C.category AND C.category != '성인'
	WHERE I.price * O.num > 100000
) A
LEFT OUTER JOIN tCity T ON TRIM(T.name) = SUBSTR(A.addr, 0, 2);

MSSQL : LEFT OUTER JOIN tCity T ON T.name = SUBSTRING(A.addr, 0, 4);
마리아 : LEFT OUTER JOIN tCity T ON T.name = SUBSTRING(addr, 1, 2);

-- 자체 조인(SELF JOIN)은 조인 테이블이 자기 자신이다. (내부 조인 문법을 사용)
-- 테이블 내의 필드끼리 참조 관계가 있는 테이블에 사용된다 (EX: 디렉토리 구조)
CREATE TABLE tDirectory
(
	id INT PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	parent INT NOT NULL
);

INSERT INTO tDirectory (id, Name, parent) VALUES (1, 'Root', 0);
INSERT INTO tDirectory (id, Name, parent) VALUES (2, 'Data', 1);
INSERT INTO tDirectory (id, Name, parent) VALUES (3, 'Program', 1);
INSERT INTO tDirectory (id, name, parent) VALUES (4, 'Sound', 2);
INSERT INTO tDirectory (id, name, parent) VALUES (5, 'Picture', 2);
INSERT INTO tDirectory (id, name, parent) VALUES (6, 'Game', 3);
INSERT INTO tDirectory (id, name, parent) VALUES (7, 'StartCraft', 6);

SELECT A.name 부모, B.name 자식 FROM tDirectory A 
INNER JOIN tDirectory B ON A.id = B.parent;

SELECT A.name 부모, A.id, B.name, B.parent 자식 FROM tDirectory A CROSS JOIN tDirectory B;

SELECT * FROM tCar, tMaker WHERE tCar.maker = tMaker.maker;

SELECT * FROM tCar INNER JOIN tMaker ON tCar.maker = tMaker.maker;

SELECT * FROM tCar LEFT OUTER JOIN tMaker ON tCar.maker = tMaker.maker;

-- 구형 조인 : (+)를 붙이는 쪽이 부테이블이다.
-- 즉, 첫번째 줄은 LEFT OUTER JOIN을, 두번째 줄은 RIGHT OUTER JOIN을 의미한다.
SELECT * FROM tCar, tMaker WHERE tCar.maker = tMaker.maker(+);
SELECT * FROM tCar, tMaker WHERE tCar.maker(+) = tMaker.maker;

SELECT * FROM tCar, tMaker WHERE tCar.maker *= tMaker.maker;