[SQL 문법] CROSS JOIN

모든 row 의 내용을 교차하여 새로운 테이블 생성

 

샘플 테이블 cross_t1, cross_t2 가 있다

CREATE TABLE cross_t1 
(
 	LABEL char(1) PRIMARY key
);

CREATE TABLE cross_t2
(
	score int PRIMARY key
);

INSERT INTO cross_t1(label)
VALUES
('a')
,('b');

INSERT INTO cross_t2(score)
VALUES
(1)
,(2)
,(3);

COMMIT;

SELECT * FROM cross_t1 ;

SELECT * FROM cross_t2 ;

 

CROSS JOIN 

SELECT *
FROM cross_t1 
CROSS JOIN cross_t2 
ORDER BY LABEL 
;

2 x 3 = 6 row의 테이블이 만들어짐

이것과 같은 것이

SELECT *
FROM cross_t1 ,cross_t2
ORDER BY LABEL 
;

실제로 쓰일 때는 이렇게

SELECT LABEL,
	CASE WHEN LABEL = 'a' THEN SUM(SCORE)
		WHEN LABEL = 'b' THEN SUM(SCORE) * -1
		ELSE 0
		END AS CALC
FROM cross_t1 
CROSS JOIN cross_t2 
GROUP BY LABEL
ORDER BY LABEL 
;