부트캠프(DB)
부트캠프 25일차 (join
동곤일상
2025. 3. 7. 17:49
반응형
1) JOIN
1-1) cross join
1-2) equi join(등가조인)
1-3) non equi join(비등가조인) (익숙치않음)
1-4) self join
1-5) inner join
1-6) outer join
2) 예제
3_)정리
1) @@ JOIN @@
2개이상의 테이블을 결합하여 데이터를 조회하는 방법
1-1) Cross Join (테이블1의컬럼 수 * 테이블2의 컬럼수)
#----------------Cross Join (알아만두자)--------------------------------------
SELECT * FROM emp; #14r,9c
SELECT * FROM dept; #5r 3c
#mariadb 방식
SELECT * FROM emp,dept; #(14*5)r (9*3)c
#ansi 방식
SELECT * FROM emp cross JOIN dept;
-- 중복된 컬럼은 테이블명 표시해야 함
-- 중복X 컬럼은 테이블명을 표시하지 않아도 됨.
SELECT e.empno , e.ename , e.job,d.deptno,d.dname
FROM emp e , dept d; #테이블명에 별명 설정1-2) Equl Join (등가조인)
가장 많이 사용하는 방식
조인컬럼을 이용해 필요한 레코드만 조회
조인의 조건이 = 인 경우!!
(조인하기 전에 항상 공통컬럼을 생각하자)
-- 사원번호 사원명 , 직책 , 부서코드 ,부서명 조회
-- mariadb방식
SELECT e.empno ,e.ename , e.job , e.deptno, d.deptno , d.dname
FROM emp e , dept d
WHERE e.deptno = d.deptno; -- 조인컬럼
-- ansi 방식
SELECT e.empno ,e.ename , e.job , e.deptno, d.deptno , d.dname
FROM emp e join dept d
on e.deptno = d.deptno; -- 조인컬럼
-- 학생테이블과 학과(major)테이블 사용해 학생이름 , 전공학과번호 , 전공학과명
DESC student;
DESC major;
-- maria db방식
SELECT s.`name` , s.major1 , m.`name`
FROM student s , major m
WHERE s.major1 = m.code;
-- ansi
SELECT s.`name` , s.major1 , m.`name`
FROM student s join major m ON (s.major1 = m.code);
-- 문제
-- 학생의 이름 , 지도교수 번호 , 지도교수이름 출력
SELECT s.name , s.profno 지도교수번호, p.`name` 지도교수명
FROM student s join professor p
ON s.profno = p.no;-- 문제 : 학생테이블에서 학번 , 이름 ,
#score테이블에서 학번에헤댕하는 국,수,영,총점
SELECT s.studno , s.name , c.kor 국, c.math 수, c.eng 영, (c.kor+c.math+c.eng) 총점
FROM student s JOIN score c ON(s.studno = c.studno)
-- 학생의 이름 , 학과이름 , 지도교수이름 조회
SELECT s.`name` , m.`name` ,p.`name`
FROM student s , major m ,professor p
where s.major1 = m.code
AND s.profno = p.no;
-- ansi 방식
SELECT s.`name` , m.`name` ,p.`name`
FROM student s join major m join professor p
on s.major1 = m.code
AND s.profno = p.no;
등가 조인 예제들
-- 장성태학생의 학번,이름 , 전공학과1번호 , 전공학과이름 ,
SELECT s.studno, s.name , s.major1 , m.`name`
FROM student s join major m
WHERE s.name='장성태' AND s.major1 = m.code;
# 두개의테이블을 join 후
#s.name='장성태'의 조건과 s.major1=m.code의 조건을 만족한다면
#select문을 출력
-- 몸무게 80kg이상인 학생의 학번 , 이름 , 체중 , 학과이름 , 학과위치
SELECT s.studno , s.name , s.weight , m.`name` , m.build
FROM student s JOIN major m
ON s.major1 = m.code
WHERE s.weight >= 80;
-- 학생의 학번, 이름 , score테이블에서 학번에 해당하는 점수조회
-- 1학년 학생의 정보 조회
SELECT grade,s.studno , s.name , c.kor , c.math , c.eng
FROM student s join score c ON s.studno=c.studno
WHERE grade=1;1-3)non equi join 비등가조인
조인컬럼의 조건이 = 이 아닌경우 , (범위값조인)
SELECT * from guest;
SELECT * FROM pointitem;
-- 고객명,고객 포인트, 고객이 포인트로 받을 수 있는 상품명을 조회
SELECT g.name , g.point , p.name
FROM guest g , pointitem p
where g.`point` BETWEEN p.spoint AND p.epoint;
SELECT g.name , g.point , p.name
FROM guest g JOIN pointitem p
ON g.`point` BETWEEN p.spoint AND p.epoint;
# 고객의 포인트가 spoint~~epoint 사이에 있다
-- 고객은 자기포인트보다 낮은 포인트의 상품을 선택할 수 있다 가정
-- 외장 하드를 선택할수있는 고객의 이름 , 포인트 , 받을수있는 상품명 ,
# 시작포인트 , 종료포인트
SELECT g.name , g.`point`,p.`name`,p.spoint,p.epoint
FROM guest g JOIN pointitem p
ON g.`point` >= p.spoint
WHERE p.name = '외장하드';
-- 낮은 포인트의 상품을 선택할 수 있다고 할때 , 개인별로 가져갈수있는 상품의갯수조회
SELECT g.name , COUNT(*) 가져갈상품
FROM guest g join pointitem p
ON g.`point` >= p.spoint # 내포인트가 상품의 포인트보다 크면 join
GROUP BY g.name # 중복이름을 하나의그룹으로만듬
ORDER BY COUNT(*) DESC; # 가져갈상품이 가장많은사람부터
예제_)
학생의 학번 이름 국어 수학 영어 총점 평균 학점 출력
평균은 반올림하여 정수로표현
# 학생의 학번 이름 국어 수학 영어 총점 평균 학점 출력
# 평균은 반올림하여 정수로표현
SELECT * FROM scorebase; # 학점기준표
SELECT s.studno , s.`name` ,c.kor , c.math , c.eng , (c.kor+c.math+c.eng) 총점
, (c.kor+c.math+c.eng)/3 '평균' , b.grade
FROM student s join score c join scorebase b
ON s.studno = c.studno
AND ROUND((c.kor+c.math+c.eng)/3) BETWEEN b.min_point AND b.max_point
#국수영 평균 >= 학점 최소점수
ORDER BY b.grade; # 학점 순으로 오름차순조인을 할때 등가조인만 사용하는것이 아닌 비등가조인을 이용해
국,수,영의 평균 between min and max 만족하는것들을 반환
학생의 평균에 따라 grade가 나오게될 것임.
1-4) self join
같은테이블의 다른컬럼들을 조인컬럼으로 사용
반드시 테이블의 별명을 설정해야한다
반드시 모든컬럼에 테이블의별명을 설정..
사용예시)
SELECT * FROM emp;
-- mgr : 상사의 사원번호
-- 사원테이블에서 사원번호 , 이름 , 상사의사원번호 , 상사의 이름 조회
-- mariaDB방식
SELECT e1.empno , e1.ename 이름 , e2.empno , e2.ename 상사이름
FROM emp e1 ,emp e2
WHERE e1.mgr = e2.empno;
-- ansi 방식
SELECT e1.empno , e1.ename 이름 , e2.empno , e2.ename 상사이름
FROM emp e1 join emp e2
on e1.mgr = e2.empno;emp테이블구조
(김수영의 mgr=1000(나영민))
self join 후
예시2)
major 테이블에서 학과코드 , 학과명 ,상위학과코드 ,상위학과명 조회
-- code : 전공학과명
-- part : 상위학부명
SELECT m1.code , m1.name 학과명 , m2.code , m2.`name` 상위학과
FROM major m1 join major m2
ON m1.part = m2.code;
# self join의 테이블명은 별명을 무조건써야하며
# 컬럼값도 별명을 사용해서 써야함
# m1은 상위코드 m2는 자신의코드로
#mojor테이블 2개를 조인한다(m1의상위 m2)
예시3)
self join 에 non equi join 활용
-- 교수번호 , 이름 , 입사일 , 입사일이 빠른사람 조회
-- 입사일이 빠른순으로 정렬
SELECT p1.no , p1.name , p1.hiredate , p2.name ,p2.hiredate
FROM professor p1 , professor p2
WHERE p1.hiredate > p2.hiredate #p1이 p2보다 늦은날짜인경우만
ORDER BY p1.name,p1.hiredate;
김경빈은 김유태보다 느리고 , 김옥남은 김유태 , 김경빈 ,이영택보다
입사일이 느리다
-- 위의 예제로 인원수조회하기
SELECT p1.name , COUNT(*) 입사일이빠른사람
FROM professor p1 , professor p2
WHERE p1.hiredate > p2.hiredate
GROUP BY p1.name;
1-5) Inner join
조인컬럼의 조건과맞는 레코드만
-- equi join , non equi join , self join
1-6) Outer join
Outer join : 조인컬럼의 조건이맞지않아도,한쪽,양쪽레코드조회
left outer join : 왼쪽테이블의 모든레코드 조회
right outer join : 오른쪽테이블의 모든레코드 조회
full outer join : 테이블의 모든레코드 조회
left join
왼쪽테이블의 모든레코드 조회
-- 지도교수가없는 학생도 조회
SELECT s.name , p.name 지도교수
FROM student s LEFT OUTER join professor p
on s.profno = p.no;
#------------------
-- 오라클 구현 방식
#---------------
-- SELECT s.name , p.name 지도교수
-- FROM student s , professor p
-- where s.profno = p.no(+);
-- 학생의학번, 이름 , 지도교수이름조회
-- 지도교수가없는학생도 조회되도록하며 없을경우 지도교수없음출력
SELECT s.studno , s.name , IFNULL(p.name,'지도교수없음') 지도교수
#p.name이 null일경우 '지됴교수없음'치환
FROM student s left outer JOIN professor p
ON s.profno = p.no;
right join
오른쪽테이블의 모든레코드 조회
-- 학생의학번, 이름 , 지도교수이름조회
-- 지도학생이 없는 교수까지 조회
SELECT s.name , p.name 지도교수
FROM student s right join professor p
on s.profno = p.no;
full outer join
: union으로 구현
학생의 이름 , 지도교수이름 조회
-- 지도교수X-> 학생정보 , 지도학생X-> 교수정보 모두조회
# maria DB에서의 full outer join 방법
# left union right
SELECT s.name , p.`name`
FROM student s left OUTER JOIN professor p
ON s.profno = p.no
UNION
SELECT s.name , p.`name`
FROM student s right OUTER JOIN professor p
ON s.profno = p.no;
문제)
-- emp , p_grade 테이블조인
-- 사원이름 , 직급 , 현재연봉 , 해당직급의 연봉하한 , 연봉상한조회
-- 연봉 : (급여*12+보너스)*10000 보너스가없는경우 0 으로처리
-- 단 모든사원 출력
SELECT * FROM p_grade;
SELECT e.ename , e.job , (e.salary*12+IFNULL(bonus,0))*10000 연봉 ,
p.s_pay 연봉하한, p.e_pay 연봉상한
FROM emp e LEFT JOIN p_grade p
ON e.job = p.`position`
union
SELECT e.ename , e.job , (e.salary*12+IFNULL(bonus,0))*10000 연봉 ,
p.s_pay 연봉하한, p.e_pay 연봉상한
FROM emp e right JOIN p_grade p
ON e.job = p.`position`;
문제2)
-- emp , p_grade 테이블 조인
-- 사원이름, 입사일 , 근속년도 , 현재직급 , 근속년도 기준 예상직급
-- 근속년도는 오늘을 기준으로 입사일의 일자/365나눈 후
-- 소수점이하는 버림
-- 단 모든사원출력
SELECT e.ename , e.hiredate , e.job ,
TRUNCATE(DATEDIFF(NOW(),e.hiredate)/365,0) 근속년도,
p.`position` 예상직급
FROM emp e left join p_grade p
ON floor(DATEDIFF(NOW(),e.hiredate)/365) between p.s_year AND p.e_year
UNION
SELECT e.ename , e.hiredate , e.job ,
TRUNCATE(DATEDIFF(NOW(),e.hiredate)/365,0) 근속년도,
p.`position` 예상직급
FROM emp e right join p_grade p
ON floor(DATEDIFF(NOW(),e.hiredate)/365) between p.s_year AND p.e_year;
문제3)
-- 사원이름 , 생일 , 나이 , 현재직급 , 나이 기준 예쌍직급
-- 나이는 오늘 기준으로 생일까지의 일자/365 (소수점이하는벌림)
-- 단 모든사원 출력
SELECT e.ename , e.birthday , TRUNCATE(datediff(NOW(),e.birthday)/365,0) 나이,
e.job , p.`position`
FROM emp e LEFT JOIN p_grade p
ON TRUNCATE(datediff(NOW(),e.birthday)/365,0) BETWEEN p.s_age AND p.e_age
UNION
SELECT e.ename , e.birthday , TRUNCATE(datediff(NOW(),e.birthday)/365,0) 나이,
e.job , p.`position`
FROM emp e right JOIN p_grade p
ON TRUNCATE(datediff(NOW(),e.birthday)/365,0) BETWEEN p.s_age AND p.e_age;
# 생일은 정해진게아니고 시작과 끝이 존재하므로
#non equi join(비등가)으로 범위를 지정해서 join해야함
2) 예제
-- 1. 지도 교수가 지도하는 학생의 인원수를 출력하기.
SELECT p.name , COUNT(*) 지도학생
FROM student s , professor p
WHERE s.profno = p.no
GROUP BY p.name;
-- 1-1 지도학생이없는 교수도출력해보자
SELECT p.name , COUNT(s.name) 지도학생
#count 는 null도 센다
FROM student s RIGHT join professor p
on s.profno = p.no
GROUP BY p.name;
-- 2. 지도 교수가 지도하는 학생의 인원수가 2명이상인 지도교수 이름를
-- 출력하기.
SELECT p.name 교수 , COUNT(*) 지도학생수
FROM student s JOIN professor p
ON s.profno = p.no
GROUP BY p.name
HAVING COUNT(p.name) > 2;
-- 3. 지도 교수가 지도하는 학생의 인원수가 2명이상인
-- 지도교수 번호,이름,학과코드,학과명 출력하기.
SELECT p.no , p.name 교수 ,p.deptno 학과코드 , m.`name` , COUNT(*)
FROM student s JOIN professor p JOIN major m
ON s.profno = p.no AND p.deptno = m.code
GROUP BY p.no
HAVING COUNT(p.no) >= 2;
-- 4. 학생의 이름과 지도교수 이름 조회하기.
-- 지도 교수가 없는 학생과 지도 학생이 없는 교수도 조회하기
-- 단 지도학생이 없는 지도교수는 '0000' 으로 출력하고
-- 지도 교수가없는 지도학생은 '****' 로 출력하기
SELECT s.name 학생명 , ifnull(p.`name`,'0000')지도교수
FROM student s left JOIN professor p
ON s.profno = p.no
UNION
SELECT IFNULL(s.name ,'****') , p.`name`
FROM student s right JOIN professor p
ON s.profno = p.no;
-- 5. 지도 교수가 지도하는 학생의 인원수를 출력하기.
-- 단 지도학생이 없는 교수의 인원수 0으로 출력하기
-- 지도교수번호, 지도교수이름, 지도학생인원수를 출력하기
SELECT p.no , p.name, s.name
FROM professor p left JOIN student s
ON p.no = s.profno; #이복영 김옥남 김현정 이명정 이정미 박성신 0
SELECT p.no , p.name ,COUNT(s.name)'지도학생'
FROM professor p left JOIN student s
ON p.no = s.profno
GROUP BY p.`name`
ORDER BY COUNT(s.name) DESC;
-- 6.교수 중 지도학생이 없는 교수의 번호,이름, 학과번호, 학과명 출력하기
SELECT p.name , p.no , p.deptno ,m.name
FROM student s right JOIN (professor p JOIN major m ON p.deptno = m.code)
ON s.profno = p.no
WHERE s.name IS NULL;
-- 7. emp 테이블에서 사원번호, 사원명,직급, 상사이름, 상사직급 출력하기
-- 모든 사원이 출력되어야 한다.
-- 상사가 없는 사원은 상사이름을 '상사없음'으로 출력하기
SELECT e1.empno , e1.ename , ifnull(e2.ename,'상사X')상사이름 , ifnull(e2.job,'상사X') 상사직급
FROM emp e1 left join emp e2
ON e1.mgr = e2.empno;
-- 8.교수 테이블에서 송승환교수보다 나중에 입사한
-- 교수의 이름, 입사일,학과코드,학과명을 출력하기
SELECT hiredate FROM professor WHERE NAME='송승환';
# 송승환교수 는 2020 03 01 입사
SELECT p2.`name` , p2.hiredate , p2.deptno , m.name
FROM professor p1 , professor p2 , major m
WHERE p1.`name`='송승환' AND p1.hiredate< p2.hiredate
AND p2.deptno = m.code;
-- 9.학생 중 2학년 학생의 최대 체중보다
-- 체중이 큰 1학년 학생의 이름, 몸무게, 키를 출력하기
SELECT s1.name , s1.weight , s1.height
FROM student s1
WHERE s1.grade=1
and s1.weight >( SELECT MAX(weight)
FROM student
WHERE grade = 2
GROUP BY grade);
SELECT s2.name , s2.weight , s2.height
FROM student s1, student s2
WHERE s1.grade=2 and s2.grade=1
GROUP BY s2.name
HAVING MAX(s1.weight)<s2.weight;
-- 10.학생테이블에서 전공학과가 101번인 학과의 평균몸무게보다
-- 몸무게가 많은 학생들의 이름과 몸무게, 학과명 출력
SELECT s2.major1 ,s2.name , s2.weight , m.name
FROM student s1, student s2 , major m
WHERE s1.major1=s2.major1 AND s1.major1 = 101 AND s2.major1 = m.code
# 전공학과101의조건을 걸어준다
GROUP BY s2.name #비교할 이름을 그룹화해줌.
HAVING avg(s1.weight)<s2.weight;
-- 11.이상미 교수와 같은 입사일에 입사한 교수 중 이영택교수 보다
-- 월급을 적게받는 교수의 이름, 급여, 입사일 출력하기
SELECT NAME , salary FROM professor WHERE NAME = '이영택';# 400만
SELECT NAME , hiredate FROM professor WHERE NAME = '이상미'; #2020-03-01
SELECT p2.name , p2.salary , p2.hiredate
FROM professor p1 join professor p2 JOIN professor p3
ON p1.hiredate = p2.hiredate AND p1.`name`='이상미'
AND p2.salary < p3.salary AND p3.name = '이영택';
-- 12. 101번 학과 학생들의 평균 몸무게 보다
-- 몸무게가 적은 학생의 학번과,이름과, 학과번호, 몸무게를 출력하기
select avg(s1.weight)
from student s1
where s1.major1 = 101; # 65.5 가 출력으로나옴
select s2.studno , s2.name , s2.major1 , s2.weight
from student s1, student s2 # join을 하지않고 각각출력할것임
where s1.major1 = 101 #s1은 major1이 101인 학생만 !
group by s2.studno #s2의 학번으로 그룹을 만듬(고유한값으로 그룹만들면됨)
# having 에서만 avg 등(집계함수)사용이 가능하므로 해주는것임
having avg(s1.`weight`)>s2.weight; #s1의 평균무게보다 작은s2들만 나오게함
-- 13. score 테이블과, scorebase 테이블을 이용하여 학점별 인원수,학점별평균값의 평균 조회하기
SELECT b.grade , AVG(s.kor+s.math+s.eng)/3 학점별평균 , COUNT(*) 인원수
FROM score s , scorebase b
WHERE ROUND((s.kor+s.math+s.eng)/3) BETWEEN b.min_point AND b.max_point
GROUP BY b.grade;
-- 14. 고객의 포인트로 상품을 받을 수 있을때 필요한 상품의 갯수를 조회하기
SELECT p.name , COUNT(*)'상품갯수'
FROM guest g , pointitem p
WHERE g.`point` BETWEEN p.spoint AND p.epoint
GROUP BY p.name;
-- 15. 교수번호,이름,입사일, 입사일이 늦은 사람의 인원수 조회하기
-- 입사일이 늦은 순으로 정렬하여 출력하기
select no , name , hiredate ,
rank()over(order by hiredate desc) '입사 늦은 순'
from professor;
-- 16. major 테이블에서 학과코드, 학과명, 상위학과코드, 상위학과명 조회하기
-- 모든 학과가 조회됨. => 상위학과가 없는 학과도 조회됨.
select m1.code , m1.name ,m2.code 상위학과코드,m2.name 상위학과명
from major m1 left join major m2
on m1.`part` = m2.code;
3) 정리
/*
join 구문 : 여러개의테이블을 연결해 데이터조회
cross join : n*m의 갯수로 레코드생성
등가조인(equl join) : 조인컬럼을 이용해 조건에맞는 레코드
선택 , 조인컬럼의 조건문 = 인경우
비등가 조인(non equi join) : 조인컬럼이용해 조건에맞는 레코드만
선택 , 조인컬럼의 조건문이 =이 아닌경우
self join : 같은테이블을 join하는경우
테이블의 별명 설정 필수( 컬럼조회시 별명설정)
inner join : 조인컬럼을 이용해 조건에맞는 레코드 선택
outer join : 조인컬럼이용해 조건에맞는 레코드선택 ,
한쪽 또는 양쪽테이블에서 조건에맞지않아도 선택
left join : 왼쪽테이블의 모든레코드 선택
right join : 오른쪽테이블의 모든레코드 선택
full outer join : 양쪽테이블 모든레코드 선택