Join/SubQuery/DB 모델링

백엔드/DB

Join/SubQuery/DB 모델링

코딩 화이팅 2023. 3. 15. 14:31

JOIN

둘 이상의 테이블에서 데이터를 조회하기 위해서 사용
일반적으로 조인조건은 PK(Primary Key, 유일하게 식별할 수 있는 키) 및 FK(Foreign Key)로 구성된다.
PK 및 FK 관계가 없더라도 논리적인 연관만으로도 JOIN 가능하다.
JOIN의 종류
- INNER JOIN : 조인 조건에 해당하는 칼럼 값이 양쪽 테이블에 모두 존재하는 경우에만 조회 동등 조인이라고도 한다. N개의 테이블 조인 시 N-1개의 조인 조건이 필요
- OUTER JOIN : 조인 조건에 해당하는 칼럼 값이 한 쪽 테이블에만 존재하더라도 조회 기준 테이블에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN으로 구분

카타시안 곱

-- 카타시안 곱 

SELECT empno, ename, job
FROM emp;

SELECT deptno, dname
FROM dept;

SELECT empno, ename, job, emp.deptno, dname
-- emp.deptno : emp의 deptno
FROM emp, dept;
-- 두개 이상의 테이블에서 데이터를 조회할 때
-- 조인 조건을 지정하지 않음
-- 조인 조건이 부적합함.

-- WHERE 사용하여 유의미하게 데이터를 뽑아
SELECT empno, ename, job, emp.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;

조인의 필요성

-- 사번 7788인 사원의 이름, 업무, 부서번호, 부서이름 조회
SELECT ename, job, deptno
FROM emp
WHERE empno = 7788;

SELECT dname
FROM dept
WHERE deptno = 20;


-- 조인을 이용하여 작성
SELECT ename, job, emp.deptno, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND empno = 7788;

INNER JOIN

-- INNER JOIN 키워드 사용해보자.
SELECT e.ename, e.job, e.deptno, d.dname
FROM emp e
INNER JOIN dept d
ON e.deptno = d.deptno
WHERE e.empno = 7788;

SELECT ename, job, deptno, dname
FROM emp
INNER JOIN dept
USING (deptno)
-- ON대신 USING을 써서 하나의 값으로 볼 수 있다.
WHERE empno = 7788;

OUTER JOIN

-- OUTER JOIN
-- 두 테이블에서 하나의 테이블에 조인조건 데이터가 존재하지 않더라도(조인 조건을 만족하지 않음) 데이터를 조회하기 위해서 사용
-- 기준 테이블에 따라 LEFT OUTER JOIN(LEFT JOIN), RIGHT JOIN(RIGHT JOIN)으로 구분
INSERT INTO emp
VALUES (7777, "김재이", "MANAGER", 7839, "2023-03-15", 5000, NULL, NULL);

-- 동등조인으로 이름, 부서번호, 부서 이름을 가져와
SELECT e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;
-- 김재이는 부서가 NULL로 표시되어 있기 때문에 dept테이블과 동등조건이 되지 않아
-- 김재이가 포함되지 않은 직원들이 출력된다.

-- 한쪽에 기준을 주고 너 붙어
-- 없으면 없는 데로 
-- 모든 사원을 기준으로 
-- 왼쪽 테이블을 기준으로 JOIN하여 조건에 일치하지 않는 데이터까지 조회
SELECT e.ename, e.deptno, d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;

-- 부서 테이블을 기준으로 
-- 오른쪽 테이블을 기준으로 JOIN하여 조건에 일치하지 않는 데이터까지 조회
SELECT e.ename, e.deptno, d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

LEFT OUTER JOIN

RIGHT OUTER JOIN

셀프 조인

-- 셀프 조인
-- 같은 테이블 2개를 조인
-- 모든 사원번호, 이름, 매니저 번호, 매니저 이름 
SELECT e1.empno, e1.ename, e1.mgr, e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno
ORDER BY e2.ename;
-- INNER JOIN 이라는 키워드를 사용해보자
SELECT e1.empno AS "사번", e1.ename AS 사원이름, e2.empno AS "매니저 번호", e2.ename AS "매니저 이름"
FROM emp e1
INNER JOIN emp e2
ON e1.mgr = e2.empno;

-- KING 이 안나왔어... 
SELECT e1.empno AS "사번", e1.ename AS 사원이름, e2.empno AS "매니저 번호", e2.ename AS "매니저 이름"
FROM emp e1 LEFT OUTER JOIN emp e2
ON e1.mgr = e2.empno;

비 동등 조인

-- 비 동등 조인(Non-Equi JOIN)
-- 조인 조건이 TABLE의 PK, FK등으로 정확히 일치하는 것이 아닐 때 사용
-- 모든 사원의 사번, 이름, 급여, 급여등급을 조회하고 싶다.
SELECT e.empno, e.ename, e.sal AS "급여", sg.grade AS "급여등급"
FROM emp e, salgrade sg
WHERE e.sal BETWEEN sg.LOSAL AND sg.HISAL
ORDER BY sg.grade DESC, e.sal DESC;


SELECT e.empno, e.ename, e.sal AS "급여", sg.grade AS "급여등급"
FROM emp e, salgrade sg
WHERE e.sal >= sg.LOSAL AND e.sal <= sg.HISAL
ORDER BY sg.grade DESC, e.sal DESC;

Subquery

하나의 sql문 안에 포함되어 있는 다른 SQL문을 의미한다.
서브 쿼리를 포함하는 SQL을 외부쿼리 또는 메인 쿼리라고 부르며, 서브 쿼리는 내부 쿼리라고도 부른다.

USE `ssafy_campus`;

-- 사번이 7788인 사원의 부서 이름을 조회하고 싶다.
SELECT deptno
FROM emp
WHERE empno = 7788;

SELECT dname
FROM dept
WHERE deptno = 20;

SELECT dname
FROM dept
WHERE deptno = (SELECT deptno
				FROM emp
				WHERE empno = 7788);
                -- 1. 매니저의 이름이 KING인 사원의 사번, 이름, 부서번호, 업무 
SELECT empno, ename, deptno, job
FROM emp
WHERE mgr = (SELECT empno
			 FROM emp
			 WHERE ename = "KING");
             
-- 2. 7566번 사원보다 급여를 많이 받는 사원의 이름, 급여를 조회
-- 7566번의 급여
SELECT sal
FROM emp
WHERE empno = 7566;

SELECT ename, sal
FROM emp 
WHERE sal > (SELECT sal
			 FROM emp
			 WHERE empno = 7566);
             
-- 3. 20번 부서의 평균 급여보다 급여가 많은 사원의 사번, 이름, 업무, 급여조회
SELECT AVG(sal)
FROM emp
WHERE deptno = 20;

SELECT empno, ename, job, sal
FROM emp
WHERE sal > (SELECT AVG(sal)
			 FROM emp
			 WHERE deptno = 20);

-- 4. 업무가 TURNER와 같고/ 사번 7934인 직원보다 급여/가 많은 사원의 사번, 이름, 업무를 조회
SELECT job
FROM emp
WHERE ename = "TURNER";

SELECT sal
FROM emp
WHERE empno = 7934;

SELECT empno, ename, job, sal
FROM emp
WHERE job = (SELECT job
			 FROM emp
			 WHERE ename = "TURNER")
AND sal > (SELECT sal
		   FROM emp
		   WHERE empno = 7934);

중첩 서브 쿼리-다중행

-- 다중행 IN / ANY / ALL
-- 5. 업무가 SALESMAN 인 직원들 중 최소 한명 이상보다 많은 급여를 받는 사원의 이름, 급여, 업무를 조회하시오.
SELECT sal
FROM emp
WHERE job = 'SALESMAN';
-- > ANY (최소값보다는 커야해)  < ANY (최대값보다는 작아야해)
SELECT ename, sal, job
FROM emp
WHERE sal > ANY (SELECT sal
				 FROM emp
				 WHERE job = 'SALESMAN');

-- 6. 업무가 'SALESMAN'인 모든 직원보다 급여(커미션포함)를 많이 받는 사원의 이름, 급여, 업무, 입사일, 부서번호를 조회하시오.
SELECT sal + IFNULL(comm, 0)
FROM emp
WHERE job = 'SALESMAN';
-- > ALL 최대값보다 큼 / < ALL 최소값보다 작음
SELECT ename, sal, job, hiredate, deptno
FROM emp
WHERE sal > ALL (SELECT sal + IFNULL(comm, 0)
				 FROM emp
				 WHERE job = 'SALESMAN');

-- 7. 직원이 최소 한명이라도 근무하는 부서의 부서번호, 부서이름, 위치
SELECT DISTINCT deptno
FROM emp;

SELECT deptno, dname, loc
FROM dept
WHERE deptno IN (SELECT DISTINCT deptno
				 FROM emp);

중첩 서브 쿼리-다중 열

-- 다중열
-- 서브 쿼리의 결과값이 두 개 이상의 칼럼을 반환하는 서브 쿼리
-- PK가 복합키이거나, 여러 칼럼의 값을 한꺼번에 비교해야 할 경우 사용
-- 행 생성자를 이용하여 다중 열 서브 쿼리를 비교
-- 8. 이름이 FORD인 사원과 매니저 및 부서가 같은 사원의 이름, 매니저번호, 부서번호를 조회 
SELECT mgr, deptno
FROM emp
WHERE ename = 'FORD';

SELECT ename, mgr, deptno
FROM emp
WHERE (mgr, deptno) = (SELECT mgr, deptno
					   FROM emp
                       WHERE ename = 'FORD')
AND ename <> 'FORD';
-- AND ename != 'FORD';

-- 9. 각 부서별 / 입사일이 가장 빠른 / 사원의 사번, 이름, 부서번호, 입사일을 조회
SELECT IFNULL(deptno, '대기발령') , MIN(hiredate)
FROM emp
GROUP BY deptno;

SELECT empno, ename, deptno, hiredate
FROM emp
WHERE (deptno, hiredate) IN (SELECT IFNULL(deptno, '대기발령') , MIN(hiredate)
							 FROM emp
							 GROUP BY deptno);

상호연관 서브 쿼리

-- 상호연관 서브쿼리
-- 외부 쿼리에 있는 테이블에 대한 참조를 하는 서브 쿼리를 의미한다.
-- 서브 쿼리의 FROM에는 t1에 대한 선언이 존재하지 않는다. 따라서 서브 쿼리는 외부 쿼리(메인 쿼리)에서 t1을 참조한다.
-- 테이블에서 행을 먼저 읽어서 각 행의 값을 관련된 데이터와 비교하는 방법 중 하나이다.
-- 기본 질의에서 고려된 각 후보행에 대해 서브 쿼리가 다른 결과를 반환해야 하는 경우에 사용한다.
-- 서브 쿼리에서는 메인 쿼리의 컬럼명을 사용할 수 있으나, 메인 쿼리에서는 서브 쿼리의 컬렴명을 사용할 수 없다.
-- 10. 소속 부서의 평균 급여보다 많은 급여를 받는 사원의 이름, 급여, 부서번호, 입사일, 업무를 조회
SELECT ename, sal, deptno, hiredate, job
FROM emp e
WHERE sal > (SELECT AVG(sal)
			 FROM emp
             WHERE deptno = e.deptno);

서브쿼리-인라인 뷰

-- 인라인 뷰(FORM 절에 서브쿼리)
-- FROM절에서 사용되는 서브 쿼리
-- 동적으로 생성된 테이블로 사용 가능하다. 뷰와 같은 역할을 한다.
-- 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다.
-- 그래서 동적 뷰라고도 한다.
-- 11. 모든 사원의 평균급여보다 적게 받는 사원들과 같은 부서에서 근무하는 사원의 사번, 이름, 급여, 부서번호를 조회

SELECT AVG(sal)
FROM emp;

SELECT DISTINCT deptno
FROM emp
WHERE sal < (SELECT AVG(sal) FROM emp);


SELECT e.ename, e.empno, e.sal, e.deptno
FROM emp e, (SELECT DISTINCT deptno FROM emp WHERE  sal < (SELECT AVG(sal) FROM emp)) AS d
WHERE e.deptno = d.deptno;
-- 12. 모든 사원에 대하여 사원의 이름, 부서번호, 급여, 사원이 소속된 부서의 평균 급여를 조회 (단, 이름 오름차순)
SELECT deptno, AVG(sal) AS avgsal
FROM emp
GROUP BY deptno;

SELECT e.ename, e.deptno, e.sal, d.avgsal
FROM emp e, (SELECT deptno, AVG(sal) AS avgsal
			 FROM emp
			 GROUP BY deptno) d
WHERE e.deptno = d.deptno
ORDER BY e.ename;

서브쿼리-스칼라서브쿼리

-- 스칼라서브쿼리
-- 하나의 행에서 하나의 컬럼 값만 반환하는 서브 쿼리
-- 다음과 같은 경우에 사용가능
-- GROUP BY를 제외한 SELECT의 모든 절
-- INSERT문의 VALUES
-- 조건 및 표현식 부분
-- UPDATE문의 SET 또는 WHERE절에서 연산자 목록
-- 13. 사원의 이름, 부서번호, 급여, 소속부서의 평균 급여를 조회
SELECT ename, deptno, sal, (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno) AS avgsal
FROM emp e;



-- 14. 부서번호가 10인 부서의 총 급여, 20인 부서의 평균 급여, 30인 부서의 최고, 최저 급여
SELECT  (SELECT SUM(sal) FROM emp WHERE deptno = 10) AS SUM10,
		(SELECT AVG(sal) FROM emp WHERE deptno = 20) AS AVG20,
		(SELECT MAX(sal) FROM emp WHERE deptno = 30) AS MAX30,
		(SELECT MIN(sal) FROM emp WHERE deptno = 30) AS MIN30
FROM dual;

-- 15. 모든사원의 번호, 이름, 부서번호, 입사일을 조회 (단, 부서이름기준으로 내림차순)
UPDATE emp SET deptno = 40 WHERE empno = 4168;
SELECT empno, ename, deptno, hiredate
  FROM emp e
 ORDER BY (SELECT dname
             FROM dept
            WHERE deptno = e.deptno) DESC;

서브쿼리를 이용한 CREATE문

-- 테이블을 카피 
CREATE TABLE emp_copy
(SELECT * FROM emp);

SELECT * FROM emp_blank;

CREATE TABLE emp_blank
(SELECT * FROM emp WHERE 1 = 0);

서브쿼리를 이용한 INSERT문

INSERT INTO emp_blank
(SELECT * FROM emp WHERE deptno = 30);

서브쿼리의 종류

중첩 서브쿼리-where 절에 작성하는 서브 쿼리
단일-행, 다중-행, 다중-열
인라인 뷰-from 절에 작성하는 서브 쿼리
스칼라 서브 쿼리-select문에 작성하는 서브 쿼리

서브 쿼리를 포함할 수 있는 SQL문

select,FROM, WHERE, HAVING, ORDER BY
INSERT 문의 VALUES
UPDATE문의 SET

서브 쿼리의 사용시 주의사항

서브 쿼리는 반드시 ()로 감싸서 사용한다.
서브 쿼리는 단일 행 또는 다중 행 비교 연산자와 함께 사용 가능하다.
단일 행 비교연산자는 서브 쿼리 결과가 1건 이하이어야 하고, 복수 행 비교 연산자는 결과 건수와 상관없다.

개념적 데이터베이스 모델링

사용자 부문의 처리현상을 분석
중요 실체(Entity->클래스)와 관계를 파악하여 ERD를 작성한다.
실체에 대한 상세 정의를 한다.
식별자를 정의하고, 식별자 업무규칙을 정한다.
실체별로 속성 상세화
필요한 속성 및 영역을 상세 정의
속성에 대한 업무규칙을 정의
각 단계를 마친 후 사용자와 함께 모델을 검토

개체(Entity) : 사용자와 관계가 있는 주요 객체(데이터로 관리되어야 하는 것)

Entity 찾는 법

새로 식별이 가능한 데이터 요소를 가짐
Entity는 속성(Attribute)를 가져야함.
- 속성(Attribute)
  - 저장할 필요가 있는 실체에 관한 정보
  - 개체(Entity)의 성질, 분류, 수량, 상태, 특성 등을 나타내는 세부사항
  - 개체에 포함되는 속성의 숫자는 10개 내외로 하는 것이 바람직함
  - 최종 DB 모델링 단계를 통해 테이블의 컬럼으로 활용
  - 예) 학생 : 학번, 이름, 주민번호, 전하번호, 주소/ 직원 : 직원 ID, 이름, 주민번호

식별자 : 한 개체 내에서 인스턴스를 구분할 수 있는 단일 속성 또는 속성 그룹

후보키 : 개체내에서 각각의 인스턴스를 구분할 수 있는 속성(기본키가 될 수 있음)

기본키 : 개체에서 각 인스턴스를 유일하게 식별하는데 적합한 Key

대체키 : 후보키 중에서 기본키로 선정되지 않은 Key

복합키 : 하나의 속성으로 기본키가 될 수 없는 경우 둘 이상의 컬럼을 묶어서 식별자로 정의

대리키 : 식별자가 너무 길거나 여러 개의 속성으로 구성되어 있는 경우 인위적으로 추가

관계 : 두 개체간의 업무적인 연관성 또는 관련 사실

각 개체 간의 특정한 존재여부 결정

현재의 관계 뿐만 아니라 장래에 사용될 경우도 고려

ERD 관계를 설정하는 순서

관계가 있는 두 실체를 실선(점선)으로 연결하고 관계를 부여
관계 차수를 표현
선택성을 표시

차수의 종류

1:1(일대일) : 두 실체의 레코드가 서로 하나씩 대응
1 : N(일대다) : 부모 실체의 하나의 레코드가 자식 실체의 여러 레코드에 대응
N :M(다대다) : 양쪽 실체 간에 여러 개의 레코드와 관계를 맺을 수 있는 경우

논리적 데이터베이스 모델링

개념적 데이터베이스 모델링 단계에서 정의된 ER-Diagram을 Mapping Rule을 적용하여 관계형 데이버베이스 이론에 입각한 스키마를 설계하는 단계와 이를 이용하여 필요하다면 정규화하는 단계로 구성

기본키

후보키 중에서 선택한 주 키
널의 값을 가질 수 없다.
동일한 값이 중복해서 저장될 수 없다.

참조키, 이웃키

관계를 맺는 두 엔티티에서 서로 참조하는 릴레이션의 attribute로 지정되는 키

Mapping Rule

개념적 데이터베이스 모델링에서 도출된 개체 타입과 관계 타입의 테이블 정의

정규화의 목적

데이터베이스의 변경 시 이상 현상 제거
데이터베이스 구조 확장 시 재 디자인 최소화
사용자에게 데이터 모델을 더욱 의미있게 작성하도록 함
다양한 질의 지원

제1 정규화(Atomic Columns)->값이 하나만 있어야됨.

제2 정규화(부분함수 종속 제거)

제3 정규화(이행적 함수 종속 제거)-2정규화와 비슷

물리적 데이터 베이스 모델링

논리적 데이터베이스 모델링 단계에서 얻어진 데이터베이스 스키마를 좀 더 효율적으로 구현하기 위한 작업
DBMS 특성에 맞게 실제 데이터베이스 내의 개체들을 정의하는 단계
데이터 사용량 분석과 업무 프로세스 분석을 통해서 효율적인 데이터베이스가 될 수 있도록 효과적인 인덱스를 정의하고 상황에 따른 역정규화 작업을 수행.

역정규화

시스템 성능으로 고려하여, 기존 설계를 재구성하는 것
정규화에 위배되는 행위

역정규화 방법

데이터 중복
파생 컬럼의 생성
테이블 분리
요약 테이블 생성
테이블 통합