Data Base 2

20230718

 

이전 내용을 확인 하실 분은 아래 링크를 클릭해 주세요.

2023.07.17 - [LG 헬로비전 DX DATA SCHOOL/DATA BASE] - Data Base

Data Base

 

MySQL

• DataBase Server 구동 중 인지 확인
• 접속 도구에서 연결이 되는지 확인
• 접속 도구에서 SQL을 작성하고 실행 해 볼 편집기를 열거나 생성
•  
• 사용할 데이터베이스를 선택

-- 데이터 베이스 사용 설정

use svng;

 

-- 데이터 베이스에 존재하는 테이블 확인

show tables;

 

 

 

1. SELECT

5- SELECT [DISTINCT]

LIMIT [오프셋] 조회할 행 개수

OFFSET 오프셋 (오프셋이 먼저 정의되어야 함!)

SQL은 작성 순서대로 동작하지 않음 - 비절차적

 

1.1)Scala Function

• 행 단위로 적용해서 리턴하는 함수로 SELECT 절과 WHERE 절 그리고 HAVING , ORDER BY 절에 사용
• 시스템 정보 함수 : 시스템과 관련된 함수

USER()

DATABASE()

ROW_COUNT()

VERSION()

SLEEP(초)

 

• 형 변환 함수 - CAST

숫자를 문자로 변환

         CAST(숫자 AS 문자자료형)

문자를 숫자로 변환

         CAST(문자 AS 숫자자료형)

 

NULL 관련 함수

IFNULL(인자1, 인자2): 첫번째 값이 NULL이면 두번째 인자를 리턴하고 NULL이 아니면 첫번째 값을 리

 

NULLIF(인자1, 인자2) : 두 개의 값이 같으면 -> NULL 그렇지 않으면 첫번째 값을 리턴

 

COALESCE(데이터 나열): NULL이 아닌 첫번째 데이터를 리턴

 

=>제어 흐름 함수

IF(조건, 참일 때 값, 거짓일 때 값) : 조건 분기

 

SELECT

    CASE 데이터

    WHEN 값1 THEN 데이터가 값1 일 때 값

    WHEN 값2 THEN 데이터가 값2 일 때 값

    ....

    ELSE 일치하는 값이 없을 때 값

END;

 

-- EMP 에서 ENAME과 COMM을 조회

SELECT ENAME, COMM
FROM EMP;


-- EMP 테이블에서 ENAME 과 SAL, COMM 그리고 SAL+COMM 을 조회
SELECT ENAME, SAL, COMM, SAL +COMM AS 수령액
FROM EMP; -- NULL과 연산값이 있어 결과가 NULL이 나옴.

-- 똑같이 조회하되, COMM이 NULL이면 0으로 계산해보자
SELECT ENAME, SAL, COMM, SAL+IFNULL(COMM, 0) AS 수령액
FROM EMP;

-- COMM이 NULL이 아니면 COMM, NULL이면, SAL이 NULL이 아니면 SAL, 
SELECT COALESCE(COMM, SAL)
FROM EMP;

 

이런 제어 흐름은 데이터베이스에서 가능하고 프로그래밍에서 가능

 

 

 

ID를 대문자로 만들어서 처리해야할 일이 있다면,

1. DATA SERVER에서 일 처리 후 CLIENT에 전송할 수 도 있고,
2. DATA SERVER에서 나온 RAW DATA를 APPLICATION SERVER가 처리해서 CLIENT로 보낼 수도 있고,
3. 아니면 프론트 엔드가 전달 받은 데이터를 직접 데이터를 처리해 CLIENT에게 바로 전달.

3번 같은 경우는 사용자가 많을 서버내의 일 처리 부담을 줄여줄 수 있다.

 

 

2.GROUPING

2.1)집계 함수 : NULL인 데이터는 제외하고 연산

 

• 종류

- SUM
- AVG
- COUNT
- MAX
- MIN
- VARIANCE
- STDDEV

합, 평균, 갯수, 등등 다 무엇인지는 아시겠죠?

 

• COUNT를 제외하고는 컬럼 이름 이나 연산식을 대입해서 결과를 리턴합니다.

COUNT는 컬럼 이름이나 연산식 대신에 *을 대입하는 경우도 있음.

 

• SUM과 AVG는 합계와 평균을 구해주는 함수

NULL은 제외하고 계산한다.

NULL을 어떻게 처리할 것인지 여부를 판단을 해야함.

 

• MAX와 MIN은 최대값과 최소값을 구해주는 함수

날짜와 문자열에도 사용이 가능

 

GROUP 함수인 이유는 GROUP BY에 영향을 받고, GROUP BY 가 없으면 전체를 대상으로 진행함.

 

GROUP BY DEPTNO 시,

SELECT 절의 DEPTNO, ENAME의 개수가 나올때 결과들의 개수가 같아야함.

 

CARIDINALITY(기수) : 행의 개수를 의미,

값이 아닌 종류의 수

 

 

2.2) GROUP BY

• 그룹화 하기 위한 절
• 컬럼 이름이나 연산식을 기재
• GROUP BY가 있는 경우에는 SELECT 절에서는 GROUP BY 절에 사용한 컬럼이나 연산식 그리고 그룹 함수만 조회가 가능

 

 

2.3) HAVING

• GROUP BY 이후의 조건을 설정해서 행 단위로 추출하기 위한 절

-- EMP 테이블의 데이터 개수를 조회
SELECT COUNT(EMPNO) -- EMPNO를 쓰지 않으면 오류
FROM EMP;

-- COMM이 NULL이 아닌 데이터를 조회하는 경우임. (특정 열을 입력하면 NULL이 아닌 데이터의 개수가 나옴)
SELECT COUNT(COMM)
FROM EMP;

-- 모든 컬럼에서 NULL이 아닌 데이터 개수를 조회
SELECT COUNT(*) AS 개수
FROM EMP;

-- EMP 테이블에서 SAL의 평균
SELECT ROUND(AVG(SAL),0)
FROM EMP;

-- EMP 테이블에서 COMM의 평균구하기, COMM은 4개만 NULL이 아니고, 나머지 10개는 NULL

SELECT AVG(COMM) -- 4로 나누어 버림
FROM EMP; -- 550

SELECT SUM(COMM) / COUNT(*) -- 14로 나누게 됨.
FROM EMP; -- 157.14, 다른 결과 초래

-- NULL을 0으로 바꿀려면, IFNULL을 써야함.

SELECT AVG(IFNULL(COMM,0))
FROM EMP; -- 157.14



-- EMP 테이블에서 DEPTNO 별로 SAL의 평균 조회
-- GROUP BY가 있는 경우 GROUP BY 절에 없는 컬럼이나
-- 연산식을 조회하면 행의 개수가 맞지 않아서 에러
SELECT DEPTNO, AVG(SAL), ENAME
FROM EMP
GROUP BY DEPTNO

-- tCity 테이블에서 region 별 popu의 합계 조회
SELECT region, sum(popu)
FROM tCity
group by region


-- 2개 이상의 컬럼으로 그룹화가 가능
-- tStaff 테이블에서 depart, gender 별로 데이터 개수를 조회
SELECT		depart, gender, COUNT(*)
FROM		tStaff
GROUP BY	depart, gender
ORDER BY depart;


-- EMP 테이블에서 DEPTNO가 5번 이상 나오는 경우 DEPTNO 와 SAL의 평균을 조회
SELECT DEPTNO, COUNT(*)
FROM EMP
GROUP BY DEPTNO;


SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
ORDER BY AVG(SAL) DESC;



SELECT DEPTNO, AVG(SAL)
FROM EMP
-- WHERE COUNT(DEPTNO) > 5 이 문법은 에러임
GROUP BY DEPTNO 
HAVING COUNT(DEPTNO) >= 5;
-- 그룹 함수를 이용한 조건은 HAVING 절에 기재해야 합니다.


-- tStaff 테이블에서 depart 별로 salary 의 평균이 340이 넘는 부서의 depart 와 salary의 평균을 조회
-- 집계 함수를 사용하지 않아도 되므로 WHERE에 조건을 작성하는 것이 좋습니다.
SELECT AVG(salary), depart
FROM tStaff
GROUP BY depart
HAVING AVG(salary) > 340 ;


-- tStaff 테이블에서 depart가 인사과나 영업부 인 데이터의 depart와 salary의 최대값을 조회
SELECT MAX(SALARY), depart
FROM tStaff
WHERE depart IN ("인사과", "영업부") -- 로 쓰고, HAVING을 지워도 결과는 같다..! 위에 쓰는게 좋다. 필터링은 먼저 하는게 좋다
GROUP BY depart
-- HAVING depart = "인사과" OR depart ="영업부"; -- depart IN ("인사과", "영업부")

 

 

2.4) WINDOW 함수

 

• 행과 행 사이의 관계를 쉽게 정의하기 위한 함수
• OVER 절과 함께 사용되는데 그룹 함수와 그 이외 함수(CUME_DIST, DENSE_RANK, FIRST_VALUE, LAG, LAST_VALUE, LEAD, NTH_VALUE, NTILE, PERCENT_RANK, RANK, ROW_NUMBER 등)를 같이 이용
• 순위 : RANK, NTILE, DENSE_RANK, ROW_NUMBER

순위 함수 이름() OVER([PARTITION BY 파티션을 만들 컬럼]

ORDER BY 정렬할 컬럼이름 또는 인덱스 또는 연산식);

 

PARTITION을 생략하면 전체 데이터를 가지고 순위를 구하고 PARTITION을 설정하면 그룹 별로 순위를 구함.

 

ROW_NUMBER는 일련 번호 순  -  동일한 순위가 없음

DENSE_RANK는 동일한 순위가 나오게 되는데 동일한 순위가 있어도 다음 순위를 이어서 출력

RANK는 동일한 순위가 나오게 되는데 동일한 순위가 있으면 다음 순위를 건너뛰고 출력 (1,2,2,4)

NTILE은 숫자를 설정하면 그 만큼으로 분할해서 출력

 

-- EMP 테이블에서 sal이 많은 순서부터 일련번호를 부여해서 ENAME 과 sal을 조회 
SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC)AS 'RANK',ENAME, SAL
FROM EMP;

-- EMP 테이블에서 sal이 많은 순서부터 일련번호를 부여해서 ENAME 과 sal을 조회 , 동일하면 같은 순위, 그 다음은 앞의 수만큼 띄어서 출력
SELECT RANK() OVER(ORDER BY SAL DESC)AS 'RANK',ENAME, SAL
FROM EMP;

-- DENSE_RANK는 같으면 같은 순위이지만, 그 수만큼 건너 띄진 않고 바로 그 다음 순위가 출력 됨. 
SELECT DENSE_RANK() OVER(ORDER BY SAL DESC)AS 'RANK',ENAME, SAL
FROM EMP;

-- NTILE은 (N)개의 그룹으로 분할. 등분을 진행함.
SELECT NTILE(3) OVER(ORDER BY SAL DESC)AS 'RANK',ENAME, SAL
FROM EMP;

-- EMP테이블 에서 DEPTNO 별로 SAL이 많은 순서부터 동일한 값은 동일한 순위를 부여해서 ENAME과 SAL을 조회
SELECT DEPTNO, DENSE_RANK() OVER(ORDER BY SAL DESC)AS 'RANK',ENAME, SAL
FROM EMP
ORDER BY DEPTNO; -- DEPTNO 안에서 순위를 메김


-- PARTION은 그룹별로 순위를 매김.
SELECT DEPTNO, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)AS 'RANK',ENAME, SAL
FROM EMP;

 

 

• LEAD와 LAG는 이전이나 이후 행을 가리키고자 할 때 사용

LEAD(컬럼이름, 정수) : 정수 번째 다음 행을 리턴

LAG(컬럼이름, 정수) : 정수 번재 이전 행을 리턴

주로 이전 행이나 이후 행 과의 차이를 알고자 할 때 사용.

 

• FIRST_VALUE 와 LAST_VALUE는 처음과 마지막 데이터를 의미하므로 컬럼 이름만 입력함.
• CUME_DIST()는 누적 백분율을 조회하기 위한 함수 - 실제 계싼은 0.0에서 1.0 이므로 * 100을 해주면 좋다.

%SAL - LAST_VALUE(SAL) 은 불가, 그 이유는 LAST_VALUE(SAL) 은 항상 자기 자신이 마지막 VALUE임 따라서 이러한 연산을 하고 싶으면, FIRST_VALUE를 쓰데 원래의 정렬의 반대로 정렬을 해주면 된다.

-- EMP 테이블에서 SAL의 내림차순으로 정렬한 다음, 다음 데이터 와의 SAL의 차이를 알곶 ㅏ하는 경우
SELECT ENAME, SAL, SAL - (LEAD(SAL, 1) OVER(ORDER BY SAL DESC)) AS "급여 차이"
FROM EMP
ORDER BY SAL DESC;


-- EMP 테이블에서 SAL의 내림차순으로 정렬한 다음, 이전 데이터 와의 SAL의 차이를 알곶 ㅏ하는 경우
SELECT ENAME, SAL, ABS(SAL - (LAG(SAL, 1) OVER(ORDER BY SAL DESC))) AS "급여 차이"
FROM EMP
ORDER BY SAL DESC;
-- 보통 두개 셀을 가지고 비교를 하기 때문에, 1말고 다른 수를 잘 사용하지는 않음.


-- EMP 테이블에서 SAL의 내림차순으로 정렬한 다음, 첫번째 데이터 와의 SAL의 차이를 알곶 ㅏ하는 경우
SELECT ENAME, SAL, ABS(SAL - (FIRST_VALUE(SAL) OVER(ORDER BY SAL DESC))) AS "급여 차이"
FROM EMP
ORDER BY SAL DESC;

-- 급여의 누적 백분율
SELECT ENAME, SAL, CUME_DIST()OVER(ORDER BY SAL DESC)*100 AS "누적 백분율"
FROM EMP
ORDER BY SAL DESC;

 

PIVOT : 두개의 기준으로 그룹화

• 가로와 세로 방향 모두 그룹화를 적용하는 것
• 집계 함수와 GROUP BY 절을 이용해서 생성합니다.

 

EMP 테이블에는 JOB 과 DEPTNO 와 SAL 항목이 존재한다.

JOB별 그리고 DEPTNO별 SAL의 합계를 구하고자 한다.

 

SELECT JOB, DEPTNO, SUM(SAL)

FROM EMP

GROUP BY JOB, DEPTNO;

 

-- PIVOT 이용
SELECT JOB,
	SUM(IF(DEPTNO=10, SAL, 0)) AS '10',
	SUM(IF(DEPTNO=20, SAL, 0)) AS '20',
	SUM(IF(DEPTNO=30, SAL, 0)) AS '30',
	SUM(SAL) AS '합계'
FROM EMP
GROUP BY JOB; -- DEPTNO는 ORDER BY 를 안함.

 

 

2.5) 기타

 

• JSON(JavaScript Object Notation - JavaScript 의 객체 표현 방법 : Python 도 동일) 출력

서로 다른 시스템 간의 데이터를 주고 받는 방법

 

이름 : JENNY, JESSICA

주소 : 서울, 뉴욕

나이 : 23, 21

 

- XML(eXtensible Markup Language) : 확장 마크업 언어

HTML은 태그의 해석을 부라우저가, XML은 태그의 해석을 부라우저가 하지 않는 것. 데이터 표현 방법으로 태그를 사용하는 방식인데, 과거에 주로 사용.

 

<persons>

               <person>

                               <name>jenny</name>

                               <address>서울</address>

                               <age>23</age>

               </person>

               <person>

                               <name>jessica</name>

                               <address>뉴욕</address>

                               <age>21</age>

               </person>

</persons

 

여기서 person은 클래스가 될 수 있다.

 

- JSON 

형태 [{"name":"jenny","address":"서울","age":"23"},{"name":"jessica","address":"뉴욕","age":"21"}]

꼭 외우길!

 

JSON_OBJECT("열이름","실제 열 이름", "열이름", "실제 열 이름...) AS '별명' 으로 조회 가능

 

JSON 문자열 관련 함수

JSON_VSLID(JSON 문자열) : JSON 문자열의 유효성 검사

JSON_SEARCH(JSON 문자열, 옵션, 검색한 문자열) : 문자열을 검색

JSON_EXTRACT(JSON 문자열, 컬럼이름, 데이터) : 데이터 삽입

JSON_REPLACE(JSON 문자열, 컬럼이름, 데이터) : 데이터 수정

JSON_REMOVE(JSON 문자열, 컬럼이름): 데이터 삭제

 

= NoSQL 이 JSON 형식으로 데이터를 표현

최근에는 RDBMS에서 JSON 형식을 지원하기 시작

RDBMS와 NoSQL의 경계가 없어지고 있음. 

따라서 RDBMS와 NoSQL 사이에 데이터를 공유할 수 있는 어플리케이션이 존재

CQRS는 로그인 / 가입, 수정, 탈퇴를 나누어 서비스 속도를 향상시키는 것.

 

 

 

3. SET OPERATOR(집합 연산자)

 

3.1) 개요

 

• 2개의 테이블을 이용해서 하나의 테이블을 만드는 연산자
• 2개의 테이블의 구조가 일치해야 한다.

컬럼의 개수가 같아야 하고 각 컬럼의 자료형이 일치해야 한다.

 

 

3.2) 종류

 

• UNION : 합집합(중복 시 1번만 출력)
• UNION ALL : 합집합(중복되어도 출력)
• INTERSECT : 교집합
• EXCEPT(MINUS : 차집합

 

 

3.3) 작성방법

 

 

SELECT 구문

 

SET 연산자

 

SELECT 구문

 

[ORDER BY 절]

 

 

3.4) 가이드 라인

 

• 컬럼의 이름은 첫번째 SELECT 구문의 컬럼이름이 출력
• ORDER BY는 마지막에 1번만 기술
• BLOB, CLOB, BFILE, LONG 자료형은 사용할 수 없음

위의 자료형은 큰 데이터를 표현하기 위한 자료형인데 데이터가 크면 비교하는데 시간이 많이 걸리기 때문

 

 

4. Sub Query

4.1) 개요

 

• 하나의 SQL 구문 안에 포함된 SQL 구문
• 포함하고 있는 SQL 을 Main Query 라고 하고 포함된 SQL을 Sub Query 라고 한다.
• Sub Query는 WHERE 절이나 FROM 절에 주로 사용되고 반드시 괄호로 감싸야 한다.

WHERE 절에 사용된 Sub Query를 Sub Query 라고 하고 FROM 절에 사용된 Sub Query는 Inline View라고 한다.

 

Query의 결과가 하나의 행이면 단일 행 Sub Query 라고 하고 2개 이상의 행이면 다중 행 Sub Query 라고 한다.

 

• Sub Query는 메인 쿼리가 실행되기 이전에 한번만 실행됨

 

 

4.2) 테이블 구조 확인

 

• EMP 테이블 구조 확인 : DESC EMP;

EMPNO - 사원번호, 기본키

ENAME - 사원이름

JOB - 직무

MGR - 관리자 사원번호

HIREDATE - 입사일

SAL - 급여

 

COMM - 보너스

DEPTNO - 부서번호

 

• DEPT 테이블 구조 확인 : DESC DEPT;

DEPTNO - 부서번호, 기본키

DNAME -  부서이름

LOC - 지역

 

• SAL GRADE 테이블 구조 확인 : DESC SALGRADE;

GRADE - 등급, 기본키

LOSAL - 최저 급여

HISAL - 최대 급여

 

 

4.3) Sub Query 가 필요한 경우

 

• 2개 이상의 테이블에서 하나의 테이블에 해당하는 컬럼만 조회하는 경우

ENAME이 MILLER 인 사원의 DNAME을 조회

 

-- ENAME이 MIILER 인 사람의 DNAME을 조회 -- 2개의 쿼리 이용
SELECT DEPTNO
FROM EMP
WHERE ENAME = 'MILLER' ; -- 부서 번호 확인 쿼리


SELECT DNAME
FROM DEPT 
WHERE DEPTNO = 10; -- 부서 번호로 이름을 찾는 쿼리



-- SUB QUERY 를 이용하여 1줄로 해결
-- 괄호 안의 SUB QUERY를 이용해 값을 찾아오고, 그 값으로 DNAME을 찾는다.
SELECT DNAME
FROM DEPT 
WHERE DEPTNO = (SELECT DEPTNO
				FROM EMP
				WHERE ENAME = 'MILLER');

 

4.4) 다중 행 Sub Query

 

• Sub Query 의 결과가 2개 이상의 행인 경우
• =, !=, >, >=, <, <= 연산자를 사용할 수 없음
• =, !=, > , >=, <, <= 은 단일 행 연산자라서 하나의 데이터와 비교
• IN, NOT IN, ANY(SOME), ALL, EXIST 연산자는 다중 행 연산자

 

-- ENAME이 MIILER 인 사람의 DNAME을 조회 -- 2개의 쿼리 이용
SELECT DEPTNO
FROM EMP
WHERE ENAME = 'MILLER' ; -- 부서 번호 확인 쿼리


SELECT DNAME
FROM DEPT 
WHERE DEPTNO = 10; -- 부서 번호로 이름을 찾는 쿼리



-- SUB QUERY 를 이용하여 1줄로 해결
-- 괄호 안의 SUB QUERY를 이용해 값을 찾아오고, 그 값으로 DNAME을 찾는다.
SELECT DNAME
FROM DEPT 
WHERE DEPTNO = (SELECT DEPTNO
				FROM EMP
				WHERE ENAME = 'MILLER');
			

-- EMP 테이블에서 SAL 의 평균보다 더 많은 SAL 을 받는 직원의 ENAME과 SAL 을 조회
SELECT ENAME , SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL) AS "평균"
				FROM EMP
				)
			

-- EMP 테이블에서 ENAME 이 MILLER 인 사원과 동일한 직무(JOB)를 가진 사원의 ENAME과 JOB을 조회
SELECT ENAME , JOB
FROM EMP
WHERE JOB = (SELECT JOB
			FROM EMP
			WHERE ENAME = "MILLER") AND ENAME != 'MILLER';
		

-- EMP 테이블에서 DEPT 테이블의 LOC가 DALLAS 인 사원의 ENAME 과 SAL을 조회
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS')



-- EMP 테이블에서 DEPTNO 별 SAL의 최대값과 동일한 SAL을 갖는 사원의 EMPNO, ENAME, SAL을 조회

SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO; -- DEPTNO가 3개이므로, 최대값도 3개 (CALI~머시기 값 3)


SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL = (SELECT MAX(SAL)
			FROM EMP
			GROUP BY DEPTNO);  
		
		
		
-- 서브 쿼리의 결과가 2개 이상인 경우, 그 중의 하나의 값과 일치하면 된다
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
			FROM EMP
			GROUP BY DEPTNO);
			

-- EMP 테이블에서 DEPTNO가 30인 데이터들의 SAL 보다 큰 데이터의 ENAME과 SAL을 조회
-- DEPTNO 가 30인 데이터는 2개 이상이므로 > 로는 비교가 불가능
		
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT MAX(SAL)
			FROM EMP
			WHERE DEPTNO = 30);

-- 데이터 들 보다 더 커야되면 ALL, 
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL
			FROM EMP
			WHERE DEPTNO = 30);
		
		
-- 데이터 중 1개보다 크게 되면 ANY
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ANY(SELECT SAL
			FROM EMP
			WHERE DEPTNO = 30);
            
-- EMP 테이블에서 SAL이 2000이 넘는 데이터가 있으면 ENAME 과 SAL을 조회
-- 2000이 넘는 데이터가 존재하므로, 2000이 넘는? X 아니고 그냥 전부다 가져옴.
SELECT ENAME, SAL
FROM EMP
WHERE EXISTS (SELECT 1 FROM EMP WHERE SAL > 2000);

 

5. JOIN

 

• 2개 테이블의 조합을 만들어내는 연산
• SET OPERATOR는 2개 테이블의 구조가 일치해야 하지만 JOIN은 2개 테이블에 동일한 의미를 갖는 컬럼만 있으면 수행이 가능

(동일한 의미라는 것은 물리적으로는 자료형이 같아야 한다라는 의미)

• JOIN을 수행해야 하는 상황은 조회하고자 하는 컬럼이 2개 이상의 테이블에 존재하는 경우

JOIN은 2개 테이블의 조합을 만들어 내기 때문에 메모리 부담도 크고 속도도 느리므로 JOIN 없이 해결할 수 있다면 JOIN 없이 문제를 해결해야 한다.

 

JOIN 시 동일한 의미를 나타내는 COLUMN들을 잘 찾아야 함.

 

1) 표준 JOIN

• Cartesian Product

FROM 절에 2개의 테이블 이름을 나열하고 JOIN 조건을 지정하지 않은 경우

2개 테이블의 모든 조합이 생성(행의 개수는 양쪽 테이블 행의 개수를 곱한 것이고 열의 개수는 양족 테이블의 열의 개수를 더한 것과 같음)

ANSI JOIN에서는 이를 CROSS JOIN 이라고 합니다.

-- Cartesian Product (교차 곱)
-- FROM 절에 테이블 이름이 2개 이상이고 JOIN 조건이 없는 경우
-- EMP 테이블은 8열 14행, DEPT 테이블은 3열 4행
-- 결과는 11열 56행 

SELECT *
FROM EMP, DEPT;

• EQUI JOIN(동등 조인)

FROM 절에 2개 이상의 테이블 이름을 기재하고 WHERE 절에서 2개 테이블의 공통된 컬럼의 값이 같다라고 JOIN 조건을 명시한 경우

-- EQUI JOIN(동등 조인)
-- FROM 절에 테이블 이름이 2개 이상이고 
-- WHERE 절에 2개 테이블의 공통된 컬럼의 값이 같다라는 조인 조건이 있는 경우

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

 

특정 컬럼만 조회하는 경우 양쪽에 동일한 컬럼 이름을 가진 경우에는 앞에 테이블 이름을 명시해야 한다.

 

테이블 이름에 다른 이름을 부여하고자 할 때는 테이블 이름 뒤에 공백을 두고 다른 이름을 부여하면 된다.

다른 이름을 부여하는 거시음로 기존 테이블 이름은 사용하는 것이 안된다.

-- 테이블 이름에 새로운 이름 부여
SELECT ENAME, e.DEPTNO, DNAME, LOC
FROM EMP e, DEPT d
WHERE e.DEPTNO = d.DEPTNO;


-- FROM에서 부여하는 것은 새로운 이름을 부여하는 것으로 기존 이름은 이제 사용할 수 없다.
SELECT ENAME, e.DEPTNO, DNAME, LOC
FROM EMP e, DEPT d
WHERE e.DEPTNO = DEPT.DEPTNO; -- 에러 발생!

 

• HASH JOIN

-  EQUI JOIN에서만 사용가능한 방식으로 선행 테이블의 데이터를 해시 테이블로 만들어서 후행 테이블의 데이터와 JOIN을 하는 방식

 

-  행의 개수가 작은 테이블과 행의 개수가 많은 테이블을 JOIN 할 때 불필요하게 많이 비교하는 것을 방지하기 위해서 사용한다.

 

-  속도는 가장 빠르지만 가장 많은 비용을 소모

 

-  SELECT 절에 해시를 이용한다고 설정을 해야 하는데 /*+ ORDERED USE_HASH(E) */ 을 추가해주면 된다.

 

-  HASH JOIN을 할 때는 행의 개수가 적은 테이블을 선행 테이블로 만들어 주어야 한다.

 

-  MySQL 버전에 따라 지원하는 방법이 다른데, MySQL 8.0 이전 버전은 지원하지 않습니다.

8.0.20 버전에서는 EQUI-JOIN이 아니더라도 HASH JOIN을 지원

 

 

• NON EQUI JOIN(비 동등 조인)

조인 조건이 = 가 아닌 경우

2개 테이블의 동일한 의미를 갖는 컬럼을 비교할 때 = 대신에 다른 연산자를 사용하는 경우

 

• SELF JOIN(자체 조인?)

동일한 테이블을 가지고 JOIN을 하는 경우

하나의 테이블에 동일한 의미를 갖는 컬럼이 2개 이상 존재하는 경우 수행 가능.

EMP 테이블에는 EMPNO 가 사원 번호이고, MGR이 관리자 사원 번호이다. 이런 경우 SELF JOIN이 가

 

-- HASH JOIN
SELECT /*+ ORDERED USE_HASH(d) */
	ENAME, e.DEPTNO, DNAME, LOC
FROM  EMP e, DEPT d
WHERE d.DEPTNO = e.DEPTNO;




-- NON EQUI JOIN
-- EMP 테이블의 SAL은 급여입니다.
-- SALGRADE 테이블의 LOSAL은 최저 급여이고 GRADE는 등급
-- EMP 테이블에서 ENAME 과 SAL을 조회하고 SAL에 해당하는 GRADE를 조회하고자 하는 경우

SELECT ENAME, SAL, GRADE
FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL; -- 비동등 조인 =이 아닌 조인


-- EMP 테이블에서 ENAME 과 관리자 ENAME을 조회
-- 앞의 emp는 현재 종업원이 되고 뒤의 EMP는 관리자가 됨
-- 종업원의 관리자 사원 번호와 일치하는 관리자의 사원번호를 찾아서 이름을 조회
-- 
SELECT employee.ename, manager.ename
FROM EMP employee, EMP manager
WHERE employee.mgr = manager.empno;

 

 

2) ANSI JOIN

 

• 미국 표전 협회에서 정한 JOIN 방식으로 대다수의 RDBMS 가 지원
• CROSS JOIN

FROM 절에 테이블 이름을 나열할 때 중간에 CROSS JOIN을 추가하면 Cartesian Product 수행

 

• INNER JOIN

조인 조건을 FROM 다음의 ON 절에 기재

테이블 이름을 나열할 때 INNER JOIN을 중간에 추가

 

2개 테이블의 조인 컬럼이 같은 경우 ON 대신에 USING(컬럼이름)을 이용해서 JOIN이 가능

 

• NATURAL JOIN

2개 테이블의 조인 컬럼이 같은 경우 INNER JOIN 대신에 NATURAL JOIN 이라고 기재하고 조인 조건을 생략하는 것이 가능(똑같은 컬럼이 한번만 나온다는 차이점 존재)

 

 

• OUTER JOIN

한 쪽에만 존재하는 데이터도 JOIN에 참여

LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있는데 MySQL에서는 FULL OUTER JOIN은 지원하지 않습니다.

MySQL 에서는 FULL OUTER JOIN을 하고자 하는 경우 LEFT OUTER JOIN의 결과와 RIGHT OUTER JOIN의 결과를 UNION 해서 생성합니다!

 

-- ANSI CROSS JOIN
SELECT *
FROM EMP CROSS JOIN DEPT; -- 56개열


-- EMP 테이블 과 DEPT 테이블의 INNER JOIN
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO -- 14 개열, 기준 열이름

SELECT *
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO -- 같은 결과, 기준열 이름

-- 2개의 컬럼이름이 같은 경우, USING 사용 가능
SELECT *
FROM EMP INNER JOIN DEPT
USING(DEPTNO)

-- 각 테이블에 같은 컬럼이 존재할 시NATURAL JOIN
SELECT *
FROM EMP NATURAL JOIN DEPT; -- 동일한 컬럼을 한번만 출력함.

-- OUTER JOIN
SELECT DISTINCT DEPTNO
FROM EMP;

SELECT DEPTNO
FROM DEPT;

-- EMP에 존재하는 모든 DEPTNO 가 JOIN에 참여
SELECT *
FROM EMP LEFT OUTER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO; -- 14개의 데이터 (EMP 꺼는 전부 참여)

-- DEPT 에 존재하는 모든 DEPTNO가 JOIN에 참여
-- DEPT 에는 존재하지만, EMP에는 존재하지 않던 40이 JOIN에 참여
-- 이 경우 40은 자신의 데이터 외엔 전부 NULL을 갖게 됨.
SELECT *
FROM EMP RIGHT OUTER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO; -- 15개로 늘어남. DEPT는 10, 20, 30 에다가 40 도 있어서 1개가 늘어남


-- MySQL 은 FULL OUTER JOIN을 지원하지 않음.
-- SELECT *
-- FROM EMP FULL OUTER JOIN DEPT
-- ON EMP.DEPTNO = DEPT.DEPTNO;



-- 각 테이블에만 존재하는 열을 JOIN에 참여시키고 싶을 때
-- FULL OUTER JOIN을 UNION으로 해결
SELECT *
FROM EMP LEFT OUTER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
UNION
SELECT *
FROM EMP RIGHT OUTER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

 

오늘은 여기까지 하겠습니다.

 

다음엔 DATA BASE의 모델링을 하는 부분에 대해서 알아보겠습니다.

 

지금까지는 DB의 조회에 대해서 알아보았습니다.
분석쪽을 가시고 싶으신 분들은 지금까지의 조회하는 내용을 잘 알아두시길 바랍니다!