20210601_2

JOIN

 

1. INNER JOIN

 

use sqlDB;
select * from userTbl
where userID = 'JYP';
select * from buyTbl
where userID = 'JYP';

select * 
from userTbl
	INNER JoiN buyTbl
	ON userTbl.userID = buyTbl.userID
where buyTbl.userID = 'JYP';

 

self join 실습 

--use sqlDB
--create table smartTbl(
--	num int,
--	emp nchar(3), 
--	manager int, -- 관리자의 사원번호 
--	department nchar(3)
--);

--INSERT INTO smartTbl VALUES(1,'나사장',NULL, NULL);
--INSERT INTO smartTbl VALUES(2,'김재무',1,'재무부');
--INSERT INTO smartTbl VALUES(3,'김부장',2,'재무부');
--INSERT INTO smartTbl VALUES(4,'이부장',2,'재무부');
--INSERT INTO smartTbl VALUES(5,'우대리',4,'재무부');
--INSERT INTO smartTbl VALUES(6,'지사원',4,'재무부');
--INSERT INTO smartTbl VALUES(7,'이영업',1,'영업부');
--INSERT INTO smartTbl VALUES(8,'한과장',7,'영업부');
--INSERT INTO smartTbl VALUES(9,'최정보',1,'정보부');
--INSERT INTO smartTbl VALUES(10,'윤차장',9,'정보부');
--INSERT INTO smartTbl VALUES(11,'이주임',10,'정보부');

select * from smartTbl;


select a.emp as 이름, b.emp as 관리자, b.department as 직속상관부서
from smartTbl a left outer join smartTbl b 
	on a.manager = b.num;