[2주차] SQL JOIN & 고급문법

JOIN

기본 문법

SELECT A.*, B.*
FROM raw_data.table1 A ____ JOIN raw_data.table2 B  --(INNER, FULL, LEFT, RIGHT, CROSS)
ON A.key1 = B.key1 and A.key2 = B.key2

default: inner join

INNER JOIN

- 양쪽 테이블에서 매치가 되는 레코드들만 리턴함

- 양쪽 테이블의 필드가 모두 채워진 상태로 리턴됨

FULL JOIN

- 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들을 리턴함

- 매칭되는 경우에만 양쪽 테이블들의 모든 필드들이 채워진 상태로 리턴됨

LEFT JOIN

- 왼쪽 테이블(Base)의 모든 레코드들을 리턴함 2. 오른쪽 테이블의 필드는 왼쪽 레코드와 매칭되는 경우에만 채워진 상태로 리턴됨

RIGHT JOIN

- 방향만 바꿔서 사용하면 돼서 left를 많이 사용

CROSS JOIN

- 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합을 리턴함

SELF JOIN

- 동일한 테이블을 alias를 달리해서 자기 자신과 조인함

JOIN시 고려해야할 점

- 먼저 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 체크

- 조인하는 테이블들간의 관계를 명확하게 정의 ex) 1대1, 1대다, 다대1

 

고급 문법

UNION(합집합)

- 여러개의 테이블들이나 SELECT 결과를 하나의 결과로 합쳐줌

- UNION은 중복을 제거

- UNION ALL은 모두 출력

EXCEPT(빼기)

- 하나의 SELECT 결과에서 다른 SELECT 결과를 빼주는 것이 가능

INTERSECT (교집합)

- 여러 개의 SELECT문에서 같은 레코드들만 찾아줌

COALESCE(Expression1, Expression2, …)

- 첫번째 Expression부터 값이 NULL이 아닌 것이 나오면 그 값을 리턴하고 모두 NULL이면 NULL을 리턴한다.

- NULL값을 다른 값으로 바꾸고 싶을 때 사용한다.

NULLIF(Expression1, Expression2)

- Expression1과 Expression2의 값이 같으면 NULL을 리턴한다

DELETE FROM table_name (not DELETE * FROM)

- 테이블에서 모든 레코드를 삭제

- DELETE FROM은 속도가 느림

- WHERE 사용해 특정 레코드만 삭제 가능

- DELETE FROM raw_data.user_session_channel WHERE channel = ‘Google’

TRUNCATE table_name

- 테이블에서 모든 레코드를 삭제

- TRUNCATE이 전체 테이블의 내용 삭제시에는 여러모로 유리

- 하지만 두가지 단점이 존재

1. TRUNCATE는 WHERE을 지원하지 않음

2. TRUNCATE는 Transaction을 지원하지 않음

WINDOW

- function(expression) OVER ( [ PARTITION BY expression] [ ORDER BY expression ] )

자주 사용되는 함수

- ROW_NUMBER, FIRST_VALUE, LAST_VALUE

- Math functions: AVG, SUM, COUNT, MAX, MIN, MEDIAN, NTH_VALUE

SUB Query (CTE)

- SELECT를 하기 전에 임시 테이블을 만들어서 사용하는 것이 가능

- 임시 테이블을 별도의 CREATE TABLE로 생성하는 것이 아니라 SELECT 문의 앞단에서 하나의 SQL 문으로 생성

- 여러 테이블을 만들때는 with 처음에 한번만 쓰고 ,로 연결

-- 한번만 사용할때
WITH channel AS (
 select DISTINCT channel from raw_data.user_session_channel
)

-- main
SELECT *
FROM channel c
JOIN temp t ON c.userId = t.userId

--여러개 사용할때

WITH channel AS (
 select DISTINCT channel from raw_data.user_session_channel
),
temp AS (select ...)

-- main
SELECT *
FROM channel c
JOIN temp t ON c.userId = t.userId