[DevCourse] Day 12 - SQL 기초(2)

JOIN

• JOIN : 두개 이상의 테이블을 특정 key를 기준으로 결합하는 것

  • 집합의 개념으로 어떤 JOIN을 사용할지 생각해보면 괜찮을 것 같다

• INNER JOIN : 두 테이블의 key의 값이 서로 같을 경우에만 JOIN 실행

  • 가장 가벼운 JOIN이다

... FROM products INNER JOIN managers on 
    products.category = managers.managing...

• LEFT JOIN : 왼쪽 테이블의 모든 col을 가져오고, 오른쪽 테이블에서 일치하는 행을 가져와 JOIN한다. 일치하는 행이 없는 경우는 NULL값으로 표시된다. 왼쪽 테이블에 중복되는 값이 있는 경우 테이블이 더 커질 수 있다.

• RIGHT JOIN : LEFT JOIN의 반대 JOIN

• FULL OUTER JOIN : 두 테이블의 모든 col을 가져와 JOIN 실행, 일치하지 않는 행의 경우는 NULL값으로 표시한다

  • 무거운 JOIN이다

• CROSS JOIN : 두 테이블의 가능한 모든 조합을 생성한다. 집계를 해야 하는 경우 주로 사용

  • table1의 row가 n, table2의 row가 m이라고 하면 결과 table은 n * m의 row를 가지게 된다 -> 엄청 무거운 JOIN이다

• SELF JOIN : 같은 table을 INNER JOIN을 사용하여 JOIN하는 경우. Alias를 필수적으로 사용해야한다

• 주의사항

  • table의 Alias를 사용하여 편하게 부를 수 있다. 아래의 경우 table 이름이 각각 c, o가 된다

    ...FROM clicks c LEFT JOIN orders o ...

UNION

• UNION : 두개 이상의 SELECT문의 결과를 결합하여 하나로 만들어줌 (각 SELECT문의 col의 수와 각 col의 데이터 타입은 동일해야한다)
  • 중복되는 row를 제거하고, unique한 row만 포함시킨다 (col 전체가 같은 경우)
• UNION ALL : 중복된 row를 제거하지 않는다

WITH, Subquery

• WITH : CTE(Common Table Expression)이라고 부르며, 임시 결과 집합을 생성하여 복잡한 쿼리를 쉽게 작성할 수 있도록 돕는 기능을 한다.
  • Table을 return 하는 함수로 생각할 수 있다
• Subquery : 소괄호"()"로 묶여진 query의 블록.

날짜와 시간 다루기

• 날짜/시간을 저장하는 데이터 타입
  • STRING : 'yyyy-mm-dd', 'yyyy-mm-dd HH:MM:SS'
    • 시간 간의 연산 불가능
  • DATE : yyyy-mm-dd
    • 저장 공간 절약 가능
  • DATETIME : YYYY-MM-DD HH:MM:SS
  • TIMESTAMP : YYYY-MM-DD HH:MM:SS UTC
    • TIME ZONE을 고려 가능, 하지만 DATETIME보다 범위가 좁음
• 날짜/시간 함수
  • NOW(): UTC 기준으로 현재 시간을 가져오는 함수
    • 비슷한 함수들 : CURRENT_TIMESTAMP(), CURTIME(), CURRENT_DATE(), ...
  • SYSDATE() : 함수가 호출된 시간을 반환
  • YEAR()/MONTH()/DAY() : 날짜에서 연도/월/일를 추출
  • HOUR()/MINUTE()/SECOND() : 시간에서 시/분/초를 추출
  • WEEKDAY() : 오늘이 일주일 중 어떤 요일인지 반환
  • MONTHNAME() : 몇월인지 반환
  • STR_TO_DATE : string을 날짜 타입으로 변경
  • DATE_FORMAT : 지정된 형식으로 날짜를 출력
    • %Y, %y, %m, %d, %H, %T, %s
  • ADDDATE()/DATE_ADD() : 특정 시간/날을 더함
  • SUBDATE()/DATE_SUB() : 특정 시간/날을 뺀다
  • CONVERT_TZ() : 타임존을 변경하여 출력
  • TIMEDIFF()/DATEDIFF() : 두 시간/날짜 간의 차이를 반혼
  • TIME_TO_SEC() : 시간을 초 단위로 반환

조건문

• IF(condition, value1, value2) : condition을 충족하면 value1, 아니면 value2를 return
• IFNULL(col, value) : col에 NULL값이 있는 경우 value로 채워서 return
• CASE WHEN condition1 THEN ... : IF와 같은 역할을 수행하지만, 케이스가 많은 경우에 사용하기 좋다

자주 사용하는 함수들

• CAST, CONVERT : data type을 변경할때 사용
• rank() : 특정 등수를 기준으로 랭크를 매기는 함수(동점의 경우 같은 랭크)
• dense_rank() : 동점인 숫자가 있을 때 같은 등수로 처리하지 않음
• percent_rank() : 해당 값보다 더 작은 값의 비율을 리턴
• lead() : 파티션 내에서 다음으로 오는 값을 찾는 함수
• lag() : 그 전에 어떤 값이 왔는지 역으로 찾아가는 함수 (lead()의 반대)