#조인

2021년 12월 14일 🔆맑음

프롤로그

오늘은 함수와 조인에 대해 배웠습니다.

함수

<숫자함수>

• ABS(n) : n의 절대값 반환
  EX) ABS(3) → 3, ABS(-3) → 3

• CEIL(n) : n과 같거나 가장 큰 정수 반환
  EX) CEIL(10.123) → 11, CEIL(10.541) → 11

• FLOOR(n) : n보다 작거나 가장 큰 정수 반환
  EX) FLOOR(10.123) → 10, FLOOR(10.541) → 10

• ROUND(n, i) : n을 소수점 기준 ( i+1 ) 번째에서 반올림한 결과 반환
  EX) ROUND(10.154) → 10, ROUND(10.154, 2) → 10.15

<문자함수>

• LOWER(char) : 소문자 변환 후 반환
  EX) LOWER('NEVER SAY GOODBYE') → never say goodbye
• UPPER(char) : 대문자 변환 후 반환
  EX) UPPER('never say goodbye') → NEVER SAY GOODBYE
• CONCAT(char1, char2) : 두 문자를 붙여 반환
  EX) CONCAT('I Have', ' A Dream') → I Have A Dream
• SUBSTR(char, pos, len) : char의 pos번째 문자부터 len 길이만큼 잘라낸 결과를 반환
  EX) SUBSTR('ABCDEFG', 1, 4) → ABCD
• REPLACE(char, search_str, replace_str) : char에서 search_str을 찾아 이를 replace_str로 대체한 결과를 반환
  EX) REPLACE('나는 너를 모르는데', '나', '너') → 너는 너를 모르는데
• LENGTH(char) : 문자열의 길이 반환
  EX) LENGTH('대한민국') → 4, LENGTHB('대한민국') → 12

<날짜함수>

• SYSDATE : 현재일자와 시간 반환
  EX) SYSDATE → 2021-03-16 22:10:56
  EX) SYSTIMESTAMP → 2021-03-16 22:10:56.998000000 +09:00

<변환함수>

• TO_CHAR(숫자 혹은 날짜, format)
  EX) TO_CHAR(SYSDATE, 'YYYY-MM-DD') → 2021-12-14
• TO_NUMBER(expr, format)
  EX) TO_NUMBER('123456') → 123456
• TO_DATE(char, format)
  EX) TO_DATE('20140101', 'YYYY-MM-DD') → 2014/01/01 00:00:00

<NULL 관련 함수>

• NVL(expr1, expr2) : expr1 값이 null일 때 expr2값으로 반환
  EX) 컬럼1값이 NULL일 때
  NVL(컬럼1, 'null입니다.') → null입니다.
• NVL2(expr1, expr2, expr3) : expr1값이 null이 아니면 expr2, null이면 expr3값 반환
  EX) 컬럼1값이 NULL일 때
  NVL2(컬럼1, 'null아니', 'null임') → null임
  

<집계함수>

• count(*) : 쿼리결과 건 수, 로우 수 반환
• sum(expr) : expr의 전체 합계
• avg(expr) : expr의 평균
• min(expr) : expr의 최소값
• max(expr) : expr의 최대값
• variance(expr) : expr의 분산
• stddev(expr) : expr의 표준편차

GROUP BY

• GROUP BY는 특정 컬럼을 기준으로 집계를 하는데 사용
• HAVING절은 GROUP BY로 집계된 값의 조건문으로 사용
• GROUP BY 다음에 HAVING 절이 옴

조인

• 조인 종류
• 오라클 : 등가조인(EQUI), 셀프조인, 카타시안조인, 외부조인(OUTER)
• ANSI : 내부조인(INNER), 셀프조인(SELF), 크로스조인(CROSS), 외부조인(OUTER)
  --ANSI 조인 용어로 외울 것을 추천
  --실무에서는 조인 종류를 따져가면서 말하지 않고 통틀어서 조인이라고 함

<내부조인:등가조인>

• 오라클 등가조인(EQUI JOIN)

  SELECT *
  FROM book b, orders o
  WHERE b.bookid = o.bookid;

• ANSI 내부조인(INNER JOIN)

  SELECT *
  FROM book b INNER JOIN orders o
  ON b.bookid = o.bookid

<셀프조인(SELF JOIN)>

• 오라클 셀프조인

  SELECT *
  FROM emp A, emp B
  WHERE A.mgr = B.empno;

• ANSI 셀프조인

  SELECT *
  FROM emp A JOIN emp B
  ON A.mgr = B.empno;

<크로스조인>

• 오라클 카타시안조인

  SELECT *
  FROM book, orders;

• ANSI 크로스조인

  SELECT *
  FROM book CROSS JOIN orders;

<외부조인(OUTER JOIN)>
1. LEFT OUTER JOIN

• 오라클

  SELECT *
  FROM book b, orders o
  WHERE book.bookid = orders.bookid(+);

• ANSI

  SELECT *
  FROM book b LEFT OUTER JOIN orders o
  ON b.bookid = o.bookid

2. RIGHT OUTER JOIN

• (+)가 왼쪽

3. FULL OUTER JOIN

• ANSI

  SELECT *
  FROM book b FULL OUTER JOIN orders o
  ON b.bookid = o.bookid

<예제>
1) 할인을 받은 고객의 이름, 할인 받은 누적금액을 조회

SELECT c.name, SUM(b.price - o.saleprice)
FROM customer c, orders o, book b
WHERE c.custid = o.custid
AND b.bookid = o.bookid
AND b.price - o.saleprice > 0
GROUP BY c.name;

2) 주문 일자 기준/ 그날 총 판매금액(saleprice), 주문횟수 조회

SELECT orderdate, SUM(saleprice), COUNT()
FROM orders
GROUP BY orderdate;

3) 고객별 주문한 고객이름, 주문한 횟수, 총 판매금액(saleprice) 조회

SELECT c.name, COUNT(), SUM(saleprice)
FROM customer c, orders o
WHERE c.custid = o.custid
GROUP BY c.name;

4) 키트리서점 책 중 주문이 한번도 없었던 책 이름, 출판사, 가격을 조회

SELECT b.bookname, b.publisher, b.price
FROM book b, orders o
WHERE b.bookid = o.bookid(+)
AND o.orderid is null;