수업 15일차

galoo·2023년 7월 17일
dbmysql
0
✔ MySQL
Select
Scala Function
Grouping
GROUP BY
Window Function
PIVOT
기타
SET OPERATOR - 집합 연산자
SUB QUERY
JOIN

HelloVision Dx Data School

목록 보기
15/72
post-thumbnail

✔ MySQL

  • Database Server가 구동 중인지 확인하자
  • 나는 Docker Desktop을 열어서 구동을 확인했다.
  • 이때, container에서 MySQL이 RUN 상태가 아니라면 DBeaver에 아무리 들어가도 연결이 안된다.
  • 마지막으로 SQL 편집기를 열고 쿼리를 작성해서 실행해보자.
    - 항상 첫 번째 명령은 사용할 데이터베이스를 선택하는 것이다.(MongoDB, MySQL)

Select

  • 쿼리문 순서 확인하기

5 : SELECT [Distinct] - 조회할 열 이름이나 연산식 나열 (필수)
1 : FROM - 조회할 테이블 이름 나열 (필수)
2 : WHERE - 행 단위로 조회할 조건
3 : GROUP BY - 그룹화 할 열 이름이나 연산식 나열
4 : HAVING - 그룹화 한 후 행 단위로 조회할 조건
6 : ORDER BY - 정렬할 열 이름이나 연산식 또는 컬럼의 별명/인덱스
LIMIT [OFFSET] - 조회할 행 개수
OFFSET 오프셋

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

Scala Function

  • 행 단위로 적용해서 리턴하는 함수로 SELECT 절과 WHERE절 그리고 HAVING 또는 ORDER BY 절에 사용된다.

  • 시스템 정보 함수 : 시스템과 관련된 함수

USER() : 결과물에서 뒤에 나오는 것은 ip이다.
DATABASE()
ROW_COUNT()
VERSION()
SLEEP(초)
MySQL은 매개변수가 없는 함수는 이름만으로도 사용이 가능함

CAST - 형 변환 함수

  • 숫자를 문자로 변환 : SELECT CAST(숫자 AS 문자자료형)
  • 문자를 숫자로 변환 : SELECT CAST(문자 AS 숫자자료형)

NULL 관련 함수

  • IFNULL(인자1, 인자2) : 첫 번째 값이 NULL이면 두 번째 인자를 RETURN, NULL 아니면 첫 번째 값을 RETURN
    - 굉장히 중요한 함수에요
  • NULLIF(인자1,인자1) : 두 개의 값이 같으면 NULL, 그렇지 않으면 첫 번째 값을 리턴
  • COALESCE(데이터나열) : NULL이 아닌 첫 번째 데이터를 리턴
-- EMP 테이블에서 ENAME과 SAL, COMM 그리고 SAL+COMM을 조회
SELECT ENAME, SAL, COMM, SAL+COMM FROM EMP;
-- NULL과 연산을 하면 NULL이 나오기 때문에 다르게 해줘야 한다.
-- COMM이 NULL이면 0으로 하자.
SELECT ENAME, SAL, COMM, SAL+IFNULL(COMM, 0) FROM EMP;

제어 흐름 함수

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

  • SELECT CASE

SELECT
	CASE 데이터
    WHEN 값1 THEN 데이터가 값1 일때의 값
    WHEN 값2 THEN 데이터가 값2 일때의 값
    ...
    ELSE 일치하는 값이 없을 때의 값
END;
  • 이런 제어 흐름은 DB에서도 가능하고, 프로그래밍에서 가능합니다.

Grouping

집계 함수

  • SUM
  • AVG
  • COUNT
  • MAX
  • MIN
  • VARIANCE : 분산
  • STDDEV : 표준 편차

SCALA 함수랑 다른게 뭐야?

  • 집계 함수는 NULL인 데이터는 제외하고 연산합니다.
  • GROUP BY 에 영향을 받는 애들입니다.
  • GROUP BY가 없다면 전체를 대상으로 합니다.

COUNT

  • COUNT를 제외하고는 컬럼 이름OR연산식을 대입해 결과를 리턴
  • COUNT는 컬럼 이름이나 연산식 대신에 *을 대입하는 경우도 있음
-- EMP 테일블의 데이터 개수를 조회해보자.
-- EMP 테이블에서 EMPNO가 NULL이 아닌 데이터 개수 조회
SELECT COUNT(EMPNO) FROM EMP;
-- NULL이 아닌 COMM 데이터 개수를 조회한 것
SELECT COUNT(COMM) FROM EMP;
-- 그래서 COUNT는 아래와 같이 쓰는 것이 허용됨
-- 모든 컬럼이 NULL이 아닌 데이터의 개수
SELECT COUNT(*) FROM EMP;

SUM & AVG

  • 합계와 평균을 구해주는 함수
  • NULL을 제외하고 계산
  • NULL을 어떻게 처리할 것인지 고민해야 합니다.
-- EMP 테이블에서 SAL의 평균
SELECT ROUND(AVG(SAL),2) FROM EMP;
-- EMP 테이블에서 COMM의 평균은?
-- 4개 데이터는 NOT NULL, 10개는 NULL
SELECT ROUND(AVG(COMM),2) FROM EMP;
-- 2개의 결과가 너무 다르다.(550, 157)
SELECT ROUND(AVG(IFNULL(COMM,0)),0) FROM EMP;

MAX & MIN

  • 최대값과 최소값을 구해주는 함수
  • 크기 비교가 가능하기에 날짜와 문자열에도 사용이 가능

GROUP 함수는 GROUP BY절 다음에서만 사용이 가능함
- 이때, 다음이라는 것은 기본 형식에서 읽히는 순서를 뜻함

SELECT ENAME FROM EMP WHERE AVG(SAL)>100;

SQL Error [1111][HY000]: Invalid use of group function

GROUP BY

  • 그룹화 하기 위한 절
  • 컬럼 이름이나 연산식을 기재
  • GROUP BY가 있는 경우에는 SELECT 절에서는 GROUP BY절에 사용한 컬럼이나 연산식 그리고 그룹 함수만 조회가 가능함
    - Maria DB는 이 경우 다른 컬럼도 출력 가능
  • 2개 이상의 컬럼으로 그룹화 가능
  • 그룹 함수는 그룹 함수끼리 아니면 그룹화한 컬럼이나 연산식과만 조회가 가능함
-- ENAME과 데이터 개수를 조회해보자. - 에러
-- ENAME은 14개, COUNT(*)는 1개여서 못함
-- MARIADB는 그럼 ENAME에서 맨앞 1개를 출력해서 맞춤
-- SELECT ENAME, COUNT(*) FROM EMP;
-- EMP 테이블에서 DEPTNO별로 SAL의 평균을 조회해보자.
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO;
-- SELECT ~ FROM 에 있는 데이터의 개수는 같아야 하며,
-- GROUP화된 값들만 들어갈 수 있다.
-- 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 인원수;

Having

  • GROUP BY 이후의 조건을 설정해서 행 단위로 추출하기 위한 절
-- emp 테이블에서..
select DEPTNO, count(*) from EMP group by DEPTNO ;
-- emp 테이블에서 deptno가 5번 이상 나오는 경우, 
deptno 와 sal의 평균 조회
-- 그룹화가 되기 전에 그룹 함수를 사용해서 에러남
-- select deptno, count(*) from EMP where count(deptno)>=5 
group by deptno;
-- group 함수를 이용한 조건은 having 저레 기재해야 합니다.
select deptno, avg(sal) from EMP group by deptno 
having count(deptno)>=5;
-- tStaff 테이블에서 depart별로 salary 평균이 340이 
-- 넘는 부서의 depart와 salary 평균 조회
select depart, avg(salary) from tStaff group by 
depart having avg(salary)>340;
-- tStaff 테이블이서 depart가 인사과나 영업부인 
-- 데이터의 depart의 salary의 최대값 조회
-- 잘못 만든 예시 (결과는 맞긴 함)
select depart 부서 , max(salary) "최대 급여" from tStaff
group by depart having depart in("영업부","인사과");
-- 이렇게 해도 된다.
select depart 부서 , max(salary) "최대 급여" from tStaff
where depart in("영업부", "인사과") group by depart
-- 과연 어떤 것으로 써야 하는 것일까? where? having?
-- where로 쓰는 것이 좋다. where, having은 필터링이고
-- 필터링은 먼저 하는 것이 좋다.
-- 집계함수를 쓸 때만 having에서 거르는 것이다.

조건을 볼 때, 집계함수가 쓰이는지 확인하고, 안쓰이면 where을, 쓰이면 having을 써서 filtering을 해주자.

Window Function

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

순위 조회

RANK, NTILE, DENSE_RANK, ROW_NUMBER
순위함수이름() OVER([PARTITION BY 파티션을 만들 컬럼]
ORDER BY 정렬할 컬럼 이름 또는 인덱스 또는 연산식);

파티션을 생략하면, 전체 DATA가 되는 것이다.
PARTITION을 설정하면 그룹 별로 순위를 정함

  • ROW_NUMBER는 일련 번호 순 - 동일한 순위가 없음
  • DENSE_RANK는 동일한 순위가 나옴, 동순위가 있어도 다음 순위를 이어서 출력
  • RANK는 동일한 순위가 나오지만, 동순위 있다면 다음 순위를 건너 뛰고 출력함
  • NTILE은 숫자를 설정하면 그 만큼으로 분할해서 출력
-- EMP 테이블에서 SAL이 많은 순서부터 일련번호를 부여해서
-- ENAME과 SAL을 조회하고 싶다.
-- 순위가 없음
SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC;
-- 순위를 넣자.
SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) AS 순위,
ENAME, SAL FROM EMP;
-- 이번엔 동일한 값은 동순위를 넣어주자. 
SELECT DENSE_RANK() OVER(ORDER BY SAL DESC) AS 순위,
ENAME, SAL FROM EMP;
-- 동순위 다음에는 건너 뛴 순위가 나오게 해줘
SELECT RANK() OVER(ORDER BY SAL DESC) AS 순위,
ENAME, SAL FROM EMP;

SQLD에서 은근 잘 냅니다. 순위문제

-- NTILE은 뭐야(N등으로 나눈거임)
SELECT NTILE(3) OVER(ORDER BY SAL DESC) AS 순위, ENAME,
SAL FROM EMP;
-- PARTITION은 뭐야
-- EMP 테이블에서 DEPTNO별로 SAL많은 순서부터 동일 값은 
동순위 부여해서 ENAME과 SAL 조회
SELECT DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY
SAL DESC) AS "순위", DEPTNO 부서코드,ENAME, SAL FROM EMP;

DEPTNO별로 순위를 나눈거네 PARTITION은
NTILE은 전체를 그냥 해버린거고

LEAD & LAG

  • 이전이나 이후 행을 가리키고자 할 때 사용합니다.
  • LEAD(컬럼이름, 정수) : 정수 번째 다음 행을 RETURN
  • LAG(컬럼이름, 정수) : 정수 번째 이전 행을 RETURN
    이전 행이나 이후 행과의 차이를 알고자 할 때 이용합니다.
100 -
200 100
250 50
300 50

이런 차이를 구하는 것을 할 때 쓴다.

-- EMP 테이블에서 SAL을 내림차순 정렬한 다음, 
-- 다음 테이터와의 SAL의 차이를 알고자 하는 경우
SELECT ENAME, SAL,IFNULL(SAL- (LEAD(SAL,1) 
OVER(ORDER BY SAL DESC)),0) AS "이전 데이터와의 차이" FROM EMP;
-- EMP 테이블에서 SAL을 내림차순 정렬한 다음, 
-- 이전 테이터와의 SAL의 차이를 알고자 하는 경우
SELECT ENAME, SAL,IFNULL(SAL-(LAG(SAL,1) 
OVER(ORDER BY SAL DESC)),0) AS "이전 데이터와의 차이" FROM EMP;

FIRST_VALUE & LAST_VALUE

  • 처음과 마지막 데이터를 의미(숫자 입력 X, 컬럼 이름만 입력)

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;

기타

JSON

  • JavaScript Object Notation
  • JS 의 객체 표현 방법(python도 동일)
  • 서로 다른 시스템 간의 데이터를 주고 받는 방법

이름 : mino, galoo
주소 : 서울, 서울
나이 : 26, 5

  • XML(eXtensible Markup Language): 확장 마크업 언어
    - HTML은 태그의 해석을 브라우저가 하는 것이고, XML은 태그의 해석을 브라우저가 하지 않는 것.
    - 데이터 표현 방법으로 태그를 사용하는 방식인데, 예전에 많이 사용
       <persons>
 		<person>
       	<name>mino</name>
       	<address>서울</address>
         <age>26</age>
       </person>
 		<person>
       	<name>mino2</name>
       	<address>서울</address>
         <age>26</age>
       </person>
 	</persons>
  • JSON
    - 이게 쓰는게 조금 더 복잡함
    - 프로그래밍 언어가 더 잘알아들음
    [{"name":"mino","address":"서울","age":26},...]

대괄호 : 배열, 중괄호 : dict 기억하자

사용방법

  • JSON_OBJECT("열이름", 실제 열 이름, "열이름", 실제 열 이름, ...) AS '별명' 으로 조회 가능
select JSON_OBJECT("ENAME",ENAME, "SAL",SAL) 
AS 'JSON 조회' FROM EMP;

출력 : {"SAL": 800.0, "ENAME": "SMITH"}

JSON 문자열 관련 함수

  • JSON_VSLID(JSON문자열)
    - JSON 문자열의 유효성 검사
  • JSON_SEARCH(JSON문자열, 옵션, 검색할 문자열)
    - 문자열 검색
  • JSON_EXTRA(JSON문자열, 데이터 검색 조건)
    - 문자열 추출
  • JSON_INSERT(JSON문자열, 컬럼 이름, 데이터)
    - 데이터 삽입
  • JSON_REPLACE(JSON문자열, 컬럼 이름, 데이터)
    - 데이터 수정
  • JSON_REMOVE(JSON문자열, 컬럼 이름)
    - 데이터 제거
  • NoSQL이 JSON 형식으로 데이터를 표현
  • 최근에는 RDBMS에서 JSON 형식을 지원하기 시작함
    - RDBMS와 NoSQL의 경계가 없어지고 있다.

RDBMS 강한 트랜잭션 - 삽입 삭제 갱신 강함, 느림
NoSQL 약한 트랜잭션 - 삽입 삭제 갱신 위험, 조회가 빠름

SET OPERATOR - 집합 연산자

개요

  • 2개의 테이블을 이용해서 하나의 테이블을 만드는 연산자
  • 2개의 테이블의 구조가 일치해야 합니다.
    - 컬럼의 개수가 같아야 하며, 각 컬럼의 자료형이 일치해야 한다.

종류

  • UNION : 합집합(중복되는 데이터 한 번만 출력)
  • UNION ALL : 합집합(중복되는 데이터 모두 출력)
  • INTERSECT : 교집합
  • EXCEPT(MINUS) : 차집합

작성 방법

SELECT 구문
SET연산자
SELECT구문
[ORDER BY절]

가이드 라인

  • 컬럼의 이름은 첫 번째 SELECT 구문의 컬럼 이름이 출력됨
  • ORDER BY는 마지막에 한 번만 기술해야 한다.
  • BLOB, CLOB, BFILE, LONG 자료형은 사용할 수 없음
    - 큰 데이터를 표현하기 위한 자료형이기에, 데이터가 크면 비교를 하는데 시간이 많이 걸리기 때문이다.
-- EMP 테이블에서 DEPTNO를 조회 : 10, 20, 30
SELECT DISTINCT DEPTNO FROM EMP;
-- DEPT 테이블에서 DEPTNO를 조회 : 10, 20, 30, 40
SELECT DISTINCT DEPTNO FROM DEPT;
-- EMP테이블과 DEPT 테이블에서 DEPTNO의 합집합을 구해보자.
SELECT DEPTNO FROM EMP UNION SELECT DEPTNO FROM DEPT;
-- EMP테이블과 DEPT 테이블에서 DEPTNO의 합집합을 구해보자. 
-- 다 나오게 하자
SELECT DEPTNO FROM EMP UNION ALL SELECT DEPTNO FROM DEPT;
-- 교집합은?
SELECT DEPTNO FROM EMP INTERSECT SELECT DEPTNO FROM DEPT;
-- DEPT에는 존재하지만, EMP에는 존재하지 않는 DEPTNO는?
SELECT DEPTNO FROM DEPT EXCEPT SELECT DEPTNO FROM EMP;

SUB QUERY

개요

  • 하나의 SQL구문 안에 포함된 SQL 구문
  • 포함하고 있는 SQL을 MAIN QUERY 라고 하고, 포함된 QUERY를 SUB QUERY라고 한다.
  • SUB QUERY는 WHERE절이나 FROM 절에 주로 사용되고 반드시 괄호로 감싸야 합니다.
    - WHERE절에 사용된 SUB QUERY를 SUB QUERY라고 하고, FROM절에 사용된 SUB QUERY를 INLINE VIEW라고 합니다.
  • QUERY의 결과가 하나의 행 : 단일행 SUB QUERY
  • QUERY의 결과가 2개 이상 : 다중 행 SUB QUERY 하고 합니다.
  • SUB QUERY는 MAIN QUERY가 실행되기 전에 한 번만 실행됩니다.

테이블 구조 확인

  • EMP 테이블 구조 확인 : DESC EMP;
    - EMPNO : 사원번호, 기본키
    - ENAME : 사원이름
    - JOB : 직무
    - MGR : 관리자 사원번호
    - HIREDATE : 입사일
    - SAL : 급여
    - COMM : 보너스
    - DEPTNO : 부서번호
  • DEPT 테이블 구조 확인 : DESC DEPT;
    - DEPTNO : 부서번호, 기본키
    - DNAME : 부서이름
    - JOC : 지역

SUB QUERY가 필요한 경우

  • 2개 이상의 테이블에서 하나의 테이블에 해당하는 컬럼만 조회하는 경우
  • ENAME이 MILLER인사원의 DNAME을 조회
  • 조회하려는 것이 하나의 테이블안에 있다면 SUB QUERY
-- ENAME이 MILLER 인 사람의 DNAME을 조회
SELECT DEPTNO FROM EMP WHERE ENAME='MILLER';
SELECT DNAME FROM DEPT WHERE DEPTNO=10;
-- 여러 번 QUERY 날리는거 안좋다.
SELECT DNAME FROM DEPT WHERE DEPTNO=
(SELECT DEPTNO FROM EMP WHERE ENAME='MILLER');

다중 행 SUB QUERY

  • SUB QUERY의 결과가 2개 이상의 행인 경우
  • =, !=, >=, <=, <,> 연산자를 사용할 수 없음
    - 해당 연산자들은 단일 행 연산자라서 하나의 데이터와 비교
  • IN, NOT IN, ANY(SOME), ALL, EXIST는 사용 가능함
-- EMP 테이블에서 DEPTNO별 SAL의 최대값과 동일한 SAL을 갖는
-- 사원의 EMPNO, ENAME, SAL을 조회하자
SELECT EMPNO, ENAME, SAL FROM EMP WHERE 
SAL=(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

SQL Error [1242][21000]: Subquery returns more than 1 row

-- SUB QUERY 결과가 2개 이상인 경우, 그 중의 하나의 값과 일치하면 됩니다.
SELECT EMPNO, ENAME, SAL FROM EMP WHERE 
SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
-- EMP 테이블에서 DEPTNO가 30인 데이터들의 SAL보다 
큰 데이터의 ENAME과 SAL 조회
SELECT ENAME, SAL FROM EMP WHERE SAL > 
(SELECT SAL FROM EMP WHERE DEPTNO=30);

SQL Error [1242][21000]: Subquery returns more than 1 row

  • 모든 데이터보다 커야 하는 경우는 ALL을
  • 데이터 중 1개보다만 크면 되는 경우는 ANY를 같이 사용
-- EMP 테이블에서 DEPTNO가 30인 데이터들의 SAL보다 
-- 큰 데이터의 ENAME과 SAL 조회
SELECT ENAME, SAL FROM EMP WHERE SAL > 
ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);
-- 하지만 그냥 MAX 가져오면 되는거 아냐?
SELECT ENAME, SAL FROM EMP WHERE SAL > 
(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);
-- 이렇게 되면 DEPTNO30인 애들 데이터들 중에 가장 
-- 작은거보다 크다라는 것이다.
SELECT ENAME, SAL FROM EMP WHERE SAL > 
ANY(SELECT SAL FROM EMP WHERE DEPTNO=30);
-- MIN으로 대체 가능
SELECT ENAME, SAL FROM EMP WHERE SAL > 
(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=30);
-- EMP 테이블에서 SAL 이 2000이 넘는 데이터가 있으면 ENAME과 SAL을 조회
SELECT ENAME, SAL FROM EMP WHERE SAL > 
EXISTS (SELECT 1 FROM EMP WHERE SAL>2000)

저기서 SELECT 1은 뭐야? 왜 아무거나 써도 되는건가?

JOIN

  • 2개의 테이블의 조합을 만들어내는 연산
  • SET OPERATOR는 2개 테이블의 구조가 일치해야 하지만, JOIN은 2개 테이블에서 동일한 의미를 갖는 컬럼만 있으면 수행이 가능함
    - 동일한 의미를 갖는다 : 물리적으로 자료형이 일치해야 한다.
    - 그렇다고 해서 부서번호와 몸무게가 숫자라고 조인하는게 아니다. 의미가 중요하다.
  • JOIN을 수행해야 하는 상황은 조회하고자 하는 컬럼이 2개 이상의 테이블에 존재하는 경우

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

다시 oracle 샘플 데이터 테이블 구조 확인

  • EMP 테이블 구조 확인 : DESC EMP;
    - EMPNO : 사원번호, 기본키
    - ENAME : 사원이름
    - JOB : 직무
    - MGR : 관리자 사원번호
    - HIREDATE : 입사일
    - SAL : 급여
    - COMM : 보너스
    - DEPTNO : 부서번호
  • DEPT 테이블 구조 확인 : DESC DEPT;
    - DEPTNO : 부서번호, 기본키
    - DNAME : 부서이름
    - JOC : 지역
  • SALGRADE 테이블 구조 확인 : DESC DEPT;
    - GRADE : 등급, 기본키
    - LOSAL : 최저 급여
    - HISAL : 최대 급여

표준 JOIN

  • CARTESIAN PRODUCT
    - FROM 절에 2개의 테이블 이름을 나열하고 JOIN 조건을 지정하지 않은 경우
    - 2개 테이블에 모든 조합이 생성(행의 개수 = 테이블간 행의 개수의 곱, 열의 개수= 테이블의 열의 개수를 더한 것)
    - ANSI JOIN에서는 CROSS JOIN이라고 합니다.
    - SELECT * FROM EMP, DEPT;

  • EQUI JOIN(동등 조인)
    - FROM 절에 2개 이상의 테이블 이름을 기재하고 WHERE 절에서 2개 테이블의 공통된 컬럼의 값이 같다라고 JOIN 조건을 명시한 경우
    - SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO =DEPT.DEPTNO ;
    - 특정 컬럼만 조회하는 경우, 양쪽에 동일한 컬럼 이름을 가진 경우에는 앞에 테이블 이름을 명시해야 합니다.
    - SELECT EMP.DEPTNO FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;
    - 테이블 이름에 다른 이름을 부여하고자 할 때는 테이블 이름 뒤에 공백을 두고 다른 이름을 부여하면 됩니다. 하지만, 다른 이름을 부여하는 것이기 때문에 기존 테이블 이름은 사용하는 것이 안됩니다.
    - SELECT ENAME, E.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;

  • HASH JOIN
    - EQUI JOIN 에서만 사용 가능한 방식으로 선행 테이블의 데이터를 해시 테이블로 만들어서 후행 테이블의 데이터와 JOIN을 하는 방식
    - 행의 개수가 작은 테이블과 행의 개수가 많은 테이블을 JOIN할 때, 불필요하게 많이 비교하는 것을 방지하기 위해서 사용합니다.
    - 일반적으로 속도는 가장 빠르지만, 가장 많은 비용을 소모합니다. 메모리 용량을 많이 차지한다는 것입니다.
    - SELECT 절에 HASH를 이용한다고 설정을 해야 한다.
    - /*+ ORDERED US_HASH(E) */를 추가하면 된다.
    - HASH JOIN을 할 때는 행의 개수가 적은 TABLE을 선행으로 두어야 한다.

-- HASH JOIN
-- EMP DEPT 중 어떤 것이 데이터가 적을까?DEPT!
SELECT /*+ ORDERED USE_HASH(E) */ ENAME, DNAME, LOC 
FROM DEPT D, EMP E 
WHERE D.DEPTNO =E.DEPTNO ;

MySQL 버전에 따라서 지원하는 방법이 다르다.
8.0 이전 버전에서는 지원하지 않습니다.
8.0.20 버전 이상부터는 EQUI JOIN이 아니더라도 HASH JOIN 지원함

  • NONE EQUI JOIN (비동등 조인)
    - JOIN 조건이 = 가 아닌 경우 입니다.
    - 2개 테이블의 동일한 의미를 갖는 컬럼을 비교할 때 = 대신에 다른 연산자를 사용하는 경우
-- EMP 테이블에는 SAL은 급여이다.
-- SALGRADE에서는 LOSAL은 최저급여 HISAL은 최대급여이고 GRADE는 등급
-- EMP 테이블에서 ENAME과 SAL을 조회하고 SAL에 해당하는 
-- GRADE를 조회하는 경우
-- LOSAL HISAL이라서 범위이다. 결국 동등 연산이 아닐 수 있다
SELECT ENAME, SAL, GRADE
FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;

이때 보면 =를 넣은 것이 아닌 BETWEEN이 들어간 것을 확인할 수 있다.

  • SELF JOIN(자체조인?)
    - 동일한 테이블을 가지고 JOIN을 하는 경우
    - 하나의 테이블에 동일한 의미를 갖는 컬럼이 2개 이상 존재하면 가능함
    - EMP TABLE 가능함 EMPNO:사원 번호&MGR:관리자 사원 번호
-- EMP 테이블에서 ENAME이 MILLER인 사원의 관리자 ENAME을 조회하고 싶어
SELECT EMPL.ENAME ,MAN.ENAME 
FROM EMP EMPL,EMP MAN 
WHERE EMPL.MGR=MAN.EMPNO AND EMPL.ENAME='MILLER';

ANSI JOIN

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

  • CROSS JOIN
    - FROM 절에 테이블 이름을 나열할 때, 중간에 CROSS JOIN을 추가하면 CARTESIAN PRODUCT 수행
    - SELECT * FROM EMP CROSS JOIN DEPT;

  • INNER JOIN
    - 조인 조건을 FROM 다음의 ON 절에 기재합니다.
    - TABLE 이름을 나열할 때, 중간에 INNER JOIN 추가
    - SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO =DEPT.DEPTNO ;
    - 2개 테이블의 조인 컬럼이 같은 경우에는 ON 대신 USING(컬럼) 사용 가능
    - USING(컬럼) 에서 해당 컬럼은 한 번만 나옴
    -SELECT * FROM EMP INNER JOIN DEPT USING(DEPTNO);

  • NATURAL JOIN
    - 2개 테이블의 조인 컬럼이 같은 경우, INNER JOIN 대신에 NATURAL JOIN이라 기재하고 조인 조건을 생략하는 것이 가능
    - SELECT * FROM EMP NATURAL JOIN DEPT;
    - 동일한 컬럼을 한번 출력합니다.

  • OUTER JOIN
    - 한 쪽에만 존재하는 데이터도 JOIN에 참여
    - LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있지만, MySQL에서는 FULL은 지원하지 않는다.
    - MySQL에서는 FULL OUTER JOIN을 하고자 할 때, LEFT OUTER JOIN의 결과와 RIGHT OUTER JOIN의 결과를 UNION해서 생성함

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행
-- DEPT에 존재하는 모든 DEPTNO가 JOIN에 참여
-- DEPT에 존재하지만 EMP에는 존재하지 않던 DEPTNO=40 이 참여
-- 40인 행에서 자신이 가지고 있는 데이터 말고는 다 NULL 이겠지
SELECT * 
FROM EMP RIGHT OUTER JOIN DEPT 
ON EMP.DEPTNO =DEPT.DEPTNO ; -- 15행
-- MySQL은 FULL OUTER JOIN을 지원하지 않습니다.
SELECT *
FROM EMP FULL OUTER JOIN DEPT
ON EMP.DEPTNO =DEPT.DEPTNO; -- 이 쿼리는 오류가 납니다.
-- UNION 으로 FULL OUTER JOIN 해결하기
(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 )
profile
galoo
밀가루 귀여워요
이전 포스트

수업 14일차

다음 포스트

수업 16일차

1개의 댓글

comment-user-thumbnail
happy
2023년 7월 18일

정보가 많아서 도움이 많이 됐습니다.

답글 달기
Powered by GraphCDN, the GraphQL CDN