7.이너 조인

이너 조인, INNER JOIN

이너 조인(INNER JOIN)은 조인이 성공한 범위를 반환
→ A와 B의 교집합 부분

inner_join

ta 테이블과 tb 테이블의 각 컬럼 x, y 가 서로 같을 때,

sql


SELECT a.x AS 'x of ta', a.y AS 'y of ta', a.z AS 'z of ta', b.x AS 'x of tb', b.y AS 'y of tb', b.z AS 'z of tb' 
FROM ta a 
INNER JOIN tb b 
ON b.x = a.x AND b.y = a.y;

ta 테이블과 tb 테이블의 각 컬럼 x, y, z가 서로 같을 때,

→ ta.x == tb.x AND ta.y == tb.y AND ta.z == tb.z

sql


SELECT a.x AS 'x of ta', a.y AS 'y of ta', a.z AS 'z of ta', b.x AS 'x of tb', b.y AS 'y of tb', b.z AS 'z of tb' 
FROM ta a 
INNER JOIN tb b
ON b.x = a.x AND b.y = a.y AND b.z = a.z;

x of TA	y of TA	z of TA	x of TB	y of TB	z of TB
1	1	1	1	1	1
2	2	NULL	2	2	NULL
3	3	NULL	3	NULL	3
4	NULL	NULL	4	NULL	NULL
→ x, y, z가 각각 서로 같은 행만 조회됨

조인 범위 연습용 스키마

아래의 스키마를 참고하여 실습 진행

sql


CREATE DATABASE join_practice;
use join_practice;
 
DROP TABLE IF EXISTS ta;
DROP TABLE IF EXISTS tb;
 
CREATE TABLE ta (x INT NOT NULL, y INT, z INT);
CREATE TABLE tb (x INT NOT NULL, y INT, z INT);
 
INSERT INTO ta VALUES (1, 1, 1);
INSERT INTO ta VALUES (2, 2, NULL);
INSERT INTO ta VALUES (3, 3, NULL);
INSERT INTO ta VALUES (4, NULL, NULL);
INSERT INTO tb VALUES (1, 1, 1);
INSERT INTO tb VALUES (2, 2, NULL);
INSERT INTO tb VALUES (3, NULL, 3);
INSERT INTO tb VALUES (4, NULL, NULL);
 
SELECT * FROM ta;
SELECT * FROM tb;

x of TA	y of TA	z of TA	x of TB	y of TB	z of TB
1	1	1	1	1	1
2	2	NULL	2	2	NULL
3	3	NULL	3	NULL	3
4	NULL	NULL	4	NULL	NULL