SQL - JOIN

-- 내부 조인

USE market_db;
SELECT *
	FROM buy
		INNER JOIN member
        ON buy.mem_id = member.mem_id
	WHERE buy.mem_id = 'GRL';

-- 필요한 열만 join

-- mem_id 앞에 테이블명을 적어준다.
SELECT buy.mem_id mem_name, prod_name, addr, CONCAT(phone1, phone2) AS '연락처'
	FROM buy
		INNER JOIN member
        ON buy.mem_id = member.mem_id;
        




-- 테이블명에 별명을 붙여준다.
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr,
		CONCAT(M.phone1, M.phone2) AS '연락처'
	FROM buy B
		INNER JOIN member M
        ON B.mem_id = M.mem_id;

-- left outer join / right outer join => null값이 나오게됨

USE market_db;
SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
	FROM member M
		LEFT OUTER JOIN buy B
        ON M.mem_id = B.mem_id
	ORDER BY M.mem_id;
    
    
'APN','에이핑크','아이폰','경기'
'APN','에이핑크','혼공SQL','경기'
'APN','에이핑크','청바지','경기'
'APN','에이핑크','혼공SQL','경기'
'BLK','블랙핑크','지갑','경남'
'BLK','블랙핑크','맥북프로','경남'
'BLK','블랙핑크','청바지','경남'
'GRL','소녀시대','혼공SQL','서울'
'ITZ','잇지',NULL,'경남'
'MMU','마마무','아이폰','전남'
'MMU','마마무','에어팟','전남'
'MMU','마마무','지갑','전남'
'MMU','마마무','지갑','전남'
'OMY','오마이걸',NULL,'서울'
'RED','레드벨벳',NULL,'경북'
'SPC','우주소녀',NULL,'서울'
'TWC','트와이스',NULL,'서울'




SELECT M.mem_id, M.mem_name, B.prod_name, M.addr
	FROM buy B
		RIGHT OUTER JOIN member M
        ON M.mem_id = B.mem_id
	ORDER BY M.mem_id;
    
'APN','에이핑크','아이폰','경기'
'APN','에이핑크','혼공SQL','경기'
'APN','에이핑크','청바지','경기'
'APN','에이핑크','혼공SQL','경기'
'BLK','블랙핑크','지갑','경남'
'BLK','블랙핑크','맥북프로','경남'
'BLK','블랙핑크','청바지','경남'
'GRL','소녀시대','혼공SQL','서울'
'ITZ','잇지',NULL,'경남'
'MMU','마마무','아이폰','전남'
'MMU','마마무','에어팟','전남'
'MMU','마마무','지갑','전남'
'MMU','마마무','지갑','전남'
'OMY','오마이걸',NULL,'서울'
'RED','레드벨벳',NULL,'경북'
'SPC','우주소녀',NULL,'서울'
'TWC','트와이스',NULL,'서울'
'WMN','여자친구',NULL,'경기'

 

-- cross join 각 테이블 행 수 만큼 곱해서 결과 출력

SELECT *
	FROM buy
		CROss JOIN member;
        
        
SELECT COUNT(*) "데이터 개수"
	FROM sakila.invetory
		CROSS JOIN world.city;
        
        

DROP TABLE cross_table;
CREATE TABLE cross_table
	SELECT *
		FROM sakila.actor
			CROSS JOIN world.country;
            
SELECT FROM cross_table LIMIT 5;


-- self join => 테이블을 복사해서 join한다고 생각
USE market_db;
CREATE TABLE emp_table (emp CHAR(4), manager CHAR(4), phone VARCHAR(8));

INSERT INTO emp_table VALUES('대표', null, '0000');
INSERT INTO emp_table VALUES('영업이사', '대표', '');
INSERT INTO emp_table VALUES('', '', '');
INSERT INTO emp_table VALUES('', '', '');
INSERT INTO emp_table VALUES('', '', '');
INSERT INTO emp_table VALUES('', '', '');
INSERT INTO emp_table VALUES('', '', '');
INSERT INTO emp_table VALUES('', '', '');
INSERT INTO emp_table VALUES('', '', '');
INSERT INTO emp_table VALUES('', '', '');
INSERT INTO emp_table VALUES('', '', '');
INSERT INTO emp_table VALUES('', '', '');

SELECT A.emp "직원" , B.emp "직속상관", B.phone "직속상관연락처"
   FROM emp_table A
      INNER JOIN emp_table B
         ON A.manager = B.emp
   WHERE A.emp = '경리부장';

 

 

 

 

 

 

 

 

 

 

 

 

 

<출처 한빛미디어:  두 테이블을 묶는 JOIN(INNER JOIN, OUTER JOIN, CROSS JOIN, SELF JOIN)>

https://www.youtube.com/watch?v=tuQFkzjqEGw&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=11