JOIN

; 여러 개의 테이블이나 뷰를 연결


1) CROSS JOIN

SELECT * FROM tbl_a CROSS JOIN tbl_b;
(= SELECT * FROM tbl_a, tbl_b;)

 

id	price		id	name		id	price	id	name
1	100	+	1	연필	=	1	100	1	연필
2	250	2	지우개	2	250	1	연필
1	100	2	지우개
2	250	2	지우개

                                                                                       
* 열 지정은 '.'피리어드로 한다. ex) tbl_a.name


2) INNER JOIN

SELECT * FROM tbl_name INNER JOIN tbl_age ON id = no;
(= SELECT * FROM tbl_name, tbl_age WHERE tbl_name.id = tbl_age.no;)

id	name		no	age		id	name	id	age
1	kkoma	1	16	1	kkoma	1	16
2	ran	+	2	7	=	2	ran	2	7
3	alex	4	14

3) LEFT, RIGHT JOIN

SELECT * FROM tbl_name LEFT JOIN tbl_age ON id = no;
SELECT * FROM tbl_name RIGHT JOIN tbl_age ON id = no;

id	name		id	name		id	name	id	age
1	kkoma		1	16		1	kkoma	1	16
2	ran	+	2	7	=	2	ran	2	7
3	alex		4	14		3	alex	NULL	NULL
id	name		id	name		id	name	id	age
1	kkoma		1	16		1	kkoma	1	16
2	ran	+	2	7	=	2	ran	2	7
3	alex		4	14		NULL	NULL	4	14

4) FULL JOIN

SELECT * FROM tbl_name FULL JOIN tbl_age ON id = no;

id	name		id	name		id	name	id	age
1	kkoma		1	16		1	kkoma	1	16
2	ran	+	2	7	=	2	ran	2	7
3	alex		4	14		NULL	NULL	4	14
						3	alex	NULL	NULL

* 예외

(SQL SERVER)
SELECT * FROM tbl_name, tbl_age WHERE  id *= no; <- LEFT JOIN
SELECT * FROM tbl_name, tbl_age WHERE  id =* no; <- RIGHT JOIN

(ORACLE)
SELECT * FROM tbl_name, tbl_age WHERE  id = no(+); <- LEFT JOIN
SELECT * FROM tbl_name, tbl_age WHERE  id = (+)no; <- RIGHT JOIN