SQL 고급 (2)

Preface

이번 장에선 두 개 이상의 테이블을 조인하는 방법을 공부했다.

개념 면에선 크게 어려운 부분이 없지만, 아무래도 실습으론 처음 접하는 코드이므로 따로 코드 형식을 찾아보지 않고 능숙하게 사용하기 위해선 시간이 조금 필요할 것 같다.

이번 책은 주문할 때 분철 신청을 했더니 총 3권으로 나뉘어 왔고, 그 중 첫 번째를 방금 막 마쳤다.

이제 조금 있으면 기말고사 기간인데, 시험 공부를 시작하기 전에 두 번째 책을 끝내고 싶다.

4. 조인

- 조인: 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것

- 1대다 관계: 한쪽 테이블에는 하나의 값만 존재해야 하지만, 다른 쪽 테이블에는 여러 개가 존재할 수 있는 관계

- 내부 조인(inner join): 가장 많이 사용되는 조인

→ 'inner'은 생략해도 되며, 일반적으로 조인이라고 하면 이 조인을 의미하는 것이다.

use sqldb;
select *
	from buytbl
		inner join usertbl
			on buytbl.userID = usertbl.userID
	where buytbl.userID = 'JYP';

※ 두 테이블을 결합하는 경우 동일한 열 이름이 있다면 꼭 '테이블명.열 이름' 형식으로 표기해야 한다.

- 조인한 후 필요한 열만 추출

select buytbl.userID, username, prodName, addr, concat(mobile1, mobile2) as '연락처'
	from buytbl
		join usertbl
			on buytbl.userID = usertbl.userID
	order by num;

- where문을 사용한 inner join

→ 호환성 등의 문제로 권장하지 않음

select buytbl.userID, username, prodName, addr, concat(mobile1, mobile2) as '연락처'
	from buytbl, usertbl
    where buytbl.userID = usertbl.userID
    order by num;  -- 호환성 등의 문제로 권장하지 않는 조인 방법

- 각 테이블에 별칭을 붙여줄 수 있다.

→ from절에 나오는 테이블의 이름 뒤에 별칭을 붙여주면 된다.

select B.userID, U.username, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
	from buytbl B
		join usertbl U
			on B.userID = U.userID
	where B.userID = 'JYP';

- inner join과 outer join의 차이

1) inner join: 양쪽 테이블에 모두 내용이 있는 것만 조인되는 방식

2) outer join: 양쪽에 내용이 있으면 당연히 조인되고, 한쪽에만 내용이 있어도 그 결과가 표시되는 조인 방식

- 구매 기록이 있는 회원을 추출하는 방법

select distinct U.userID, U.username, U.addr
	from usertbl U
		join buytbl B
			on U.userID = B.userID
	order by U.userID;	-- 구매한 적이 있는 회원 조회
    
    
select U.userID, U.username, U.addr
	from usertbl U
	where exists (	-- exists문 사용
		select *
        from buytbl B
        where U.userID = B.userID );

- 다대다 관계는 두 테이블 사이에 연결 테이블을 둬서 이 연결 테이블과 두 테이블이 일대다 관계를 맺도록 구성한다.

- 세 개 테이블의 조인을 실습하기 위한 테이블 데이터

use sqldb;
create table stdTbl
( stdName	varchar(10) not null primary key,
  addr		char(4) not null
);
create table clubTbl
( clubName	varchar(10) not null primary key,
  roomNo	char(4) not null
);
create table stdclubTbl
( num int auto_increment not null primary key,
  stdName	varchar(10) not null,
  clubName	varchar(10) not null,
  foreign key(stdName) references stdtbl(stdName),
  foreign key(clubName) references clubtbl(clubName)
);

insert into stdtbl values ('김범수', '경남'), ('성시경', '서울'), ('조용필', '경기'), ('은지원',
		'경북'), ('바비킴', '서울');
insert into clubtbl values ('수영', '101호'), ('바둑', '102호'), ('축구', '103호'), ('봉사',
		'104호');
insert into stdclubtbl values (null, '김범수', '바둑'), (null, '김범수', '축구'), (null,
		'조용필', '축구'), (null, '은지원', '축구'), (null, '은지원', '봉사'), (null, '바비킴', '봉사');

- 학생의 이름, 지역, 가입한 동아리, 동아리방을 출력하는 코드

select S.stdName, S.addr, C.clubName, C.roomNO
	from stdtbl S
		inner join stdclubTbl SC
			on S.stdName = SC.stdName
		inner join clubTbl C
			on SC.clubName = C.clubName
	order by S.stdName;
    
    
-- 동아리를 기준으로 가입한 학생의 목록 출력
select C.clubName, C.roomNO, S.stdName, S.addr
	from stdtbl S
		inner join stdclubTbl SC
			on S.stdName = SC.stdName
		inner join clubTbl C
			on SC.clubName = C.clubName
	order by C.clubName;

- 외부 조인(outer join)의 형식

select (열 목록)
from (첫 번째 테이블(left 테이블))
	(left/right/full) outer join (두 번째 테이블(right 테이블))
    	on (조인될 조건)
    [where 검색조건] ;

- 전체 회원의 구매기록 출력 (구매 기록이 없는 회원도 출력)

select U.userID, U.username, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
	from usertbl U
		left outer join buytbl B
			on U.userID = B.userID
	order by U.userID;


select U.userID, U.username, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
	from buytbl B
		right join usertbl U
			on U.userID = B.userID
	order by U.userID;

- left/right outer join문은 왼쪽/오른쪽 테이블의 것은 모두 출력되어야 한다는 의미이다.

→ 'outer'은 생략해도 된다.

- 구매 기록이 없는 회원의 목록을 출력하는 코드

select U.userID, U.username, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
	from usertbl U
		left outer join buytbl B
			on U.userID = B.userID
	where B.prodName is null	-- 구매 기록이 없는 회원 목록
	order by U.userID;

- full outer join: left outer join과 right outer join이 합쳐진 것

→ 한쪽을 기준으로 조건과 일치하지 않는 것을 출력하는 것이 아니라, 양쪽 모두에 조건이 일치하지 않는 것을 모두 출력

- outer join을 사용해서 동아리에 가입하지 않은 학생도 출력하는 코드

select S.stdName, S.addr, C.clubName, C.roomNO
	from stdtbl S
		left outer join stdclubTbl SC
			on S.stdName = SC.stdName
		left outer join clubTbl C
			on SC.clubName = C.clubName

- 동아리를 기준으로 가입된 학생을 출력하되, 가입 학생이 없는 동아리도 출력하는 코드

select S.stdName, S.addr, C.clubName, C.roomNO
	from stdtbl S
		left outer join stdclubTbl SC
			on S.stdName = SC.stdName
		right outer join clubTbl C
			on SC.clubName = C.clubName;

- 위의 두 결과를 하나로 합쳐 출력하는 코드

select S.stdName, S.addr, C.clubName, C.roomNO
	from stdtbl S
		left outer join stdclubTbl SC
			on S.stdName = SC.stdName
		left outer join clubTbl C
			on SC.clubName = C.clubName
union
select S.stdName, S.addr, C.clubName, C.roomNO
	from stdtbl S
		left outer join stdclubTbl SC
			on S.stdName = SC.stdName
		right outer join clubTbl C
			on SC.clubName = C.clubName;

- 상호 조인(cross join): 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시킴

1) 결과 개수: 두 테이블 개수를 곱한 값

2) 카티션곱(cartesian preduct)이라고도 한다.

3) on 구문을 사용할 수 없다.

4) 테스트로 사용할 대용량의 데이터를 생성할 때 주로 사용한다.

select *
	from buytbl
		cross join usertbl;


select *
	from buytbl, usertbl;	-- 권장하는 방식이 아님

- 자체 조인(self join): 자기 자신과 자기 자신이 조인하는 것

→ 대표적인 예: 조직도와 관련된 테이블

- UNION: 두 쿼리의 결과를 행으로 합치는 것

1) select 문장1과 select 문장2의 결과 열의 개수가 같아야 한다.

2) 데이터 형식이 각 열 단위로 같거나 서로 호환되는 데이터 형식이어야 한다.

3) 중복된 열은 제거되고 데이터가 정렬되어 나온다.

→ UNION ALL을 사용하면 중복된 열가지 모두 출력된다.

use sqldb;
select stdName, addr from stdtbl
	union all
select clubName, roomNO from clubtbl;

- NOT IN: 첫 번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문

select username, concat(mobile1, mobile2) as '전화번호' from usertbl
	where username not in ( select username from usertbl where mobile1 is null);
    -- 전화기가 없는 사람은 제외하고 모두 출력

- IN: 첫 번째 쿼리의 결과 중에서, 두 번째 쿼리에 해당되는 것만 조회하기 위한 구문

select username, concat(mobile1, mobile2) as '전화번호' from usertbl
	where username in ( select username from usertbl where mobile1 is null);
    -- 전화기가 없는 사람만 출력

728x90