SQL | query-14(조인).sql | 외부조인 OUTER JOIN | query-15(외부조인,자체조인,크로스조인,union).sql *

kjh95.tistory.com/224

SQL | 테이블복사, group by, having, 집계함수 | DML, DDL, DCL

query-6(DML,DDL,DCL).sql

삼일 테이블 기본설정 참고

 

query-14(조인).sql

-- 조인 : 2개 이상의 테이블에 있는 정보중 사용자가 필요한 정보에 맞게
-- 		 가상의 테이블처럼 만들어서 결과를 보여주는 것임.

-- 조인의 종류
-- 1) Inner 조인 : 특정 컬럼을 기준으로 정확히 매칭된 집합을 출력함.
-- 				  조인시 NULL값을 허용하지 않는다.
-- 				  null 값을 가진 레코드는 조인 결과에 빠짐.
 
use world;

-- city 테이블과 contry 테이블을 조인하시오.
-- city.countrycode = country.code
select  * 
from city c
join country c2 
on c.countrycode = c2.code;

-- 국가코드와 해당 나라의 GNP를 표시하시오.
select  c2.GNP, c.CountryCode 
from city c
join country c2 
on c.countrycode = c2.code;

use samil;
-- 고객과 고객의 주문에 관한 데이터를 모두 보이시오.
select *
from customer c, orders o
where c.custid = o.custid;

-- 고객과 고객의 주문에 대한 데이터를 고객번호 순으로 정렬하여 보이시오.
select *
from customer c, orders o
where c.custid = o.custid
order by c.custid;

-- 고객의 이름과 고객이 주문한 도서의 판매가격을 검색하시오.
select name, saleprice
from customer c , orders o
where c.custid = o.custid;

-- 고객별로 주문한 모든 도서의 총 판매액을 구하고, 고객별로 정렬하시오.
select name, sum(saleprice) as '총 판매액'
from customer c , orders o
where c.custid = o.custid
group by c.name
order by c.name;

-- 고객의 이름과 고객이 주문한 도석의 이름을 구하시오.
select b.bookname, c.name
from customer c , orders o, book b
where c.custid = o.custid and o.bookid = b.bookid;

-- 가격이 20,000원인 도서를 주문한 고객의 이름과 도서의 이름을 구하시오.
select b.bookname, c.name
from customer c , orders o, book b
where c.custid = o.custid and o.bookid = b.bookid
and o.saleprice=20000;

 

외부조인(outer join)

ANSI SQL -> sql 표준

상호 조인(cross join)

셀프 조인(self join)

UNION과 UNIONALL  연산자 사용 

NOT IN 과 IN

 

query-15(외부조인,자체조인,크로스조인,union).sql

use sqldb2;

-- 내부조인 : 조건에 해당하는 행만 리턴한는 조인.
-- 외부조인 : 특정 컬럼을 기준으로 매칭된 집합을 출력하지만
--  		한쪽의 집합은 모두 풀력하고 다른 한쪽의 집합은 매칭되는 컬럼의 값만 출력함.
-- self조인 : 동일한 테이블끼리 특정 컬럼을 기준으로 매칭되는 집합을 출력함.
-- cross조인 : 곱집합을 반환함

select *
from buy b ;

select *
from 'user' u 

-- 구매내역이 없는 회원정보까지 다 출력하시오.
select u.userID, u.userName, b.prodName, u.addr, 
	concat(u.mobile1, u.mobile2) as '연락처' 
from user u 
left outer join buy b 
on u.userID = b.userID 
order by u.userID;

-- right outer join은 left outer join만 바꿔주면 된다.
select u.userID, u.userName, b.prodName, u.addr, 
	concat(u.mobile1, u.mobile2) as '연락처' 
from user u 
right outer join buy b 
on u.userID = b.userID 
order by u.userID;

-- 상호 조인
-- cross join의 결과 개수는 두테이블의 행의 개수를 곱하여 나온 개수가 됨.
-- 카티션 곱(Cartesian Product)이라고도 부름.
-- 일반적으로 대용량데이터를 생성할때 사용함.
-- buy : 12개 행, user : 10개 행

select *
from buy
cross join user;

use employees;
-- employees : 300,024건
-- title : 443,308건

-- 셀프조인 : 테이블 하나로 두개 이상엮어서 결과값을 추출해내는 방법임.
-- 셀프조인은 주로 계층구조를 수평적인 관계로 바꾸는 역할을 해줌.
use sqldb2;
drop table if exists emp;
create table emp(
	emp       char(3)   not null primary key,
	manager   char(3),
	emptel    varchar(8)
);

insert into emp values('나사장', null, '0000'),
('김재무', '나사장', '2222'),('김부장', '김재무', '2222-1'),('이부장', '김재무', '2222-2'),
('우대리', '이부장', '2222-2-1'),('지사원', '이부장', '2222-2-2'),('이영업', '나사장', '1111'),
('한과장', '이영업', '1111-1'),('최정보', '나사장', '3333'),('윤차장', '최정보', '3333-1'),
('이주임', '윤차장', '3333-1-1');

select *
from emp
where emp = '이부장';

-- 우대리의 상관인 이부장의 전화번호를 나타내시오.
-- emp 테이블이 마치 2개가 있는 것처럼 분리해서 조건을 설정해야함.
select a.emp as '부하직원', b.emp as '직속상관', b.emptel
from emp a
inner join emp b
on a.manager = b.emp
where a.emp = '우대리';

-- not in과 in을 알아봄.
-- not in : 서브쿼리나 데이터값을 제외할 때 사용되는 것임. 
-- in : 포함할때 사용하는 것임.
-- 헨드폰 번호가 없는 사람들을 제외하고 출력하는 쿼리를 만드시오

select userName, concat(mobile1, mobile2) as '연락처' 
from user
where userName not in(
	select userName 
	from user
	where mobile1 is null 
);
 
-- 헨드폰 번호가 없는 사람만 출력하시오.
select userName, concat(mobile1, mobile2) as '연락처' 
from user
where userName in(
	select userName 
	from user
	where mobile1 is null 
);