SQL공부하기 3일차
1. INNER JOIN 공부하기
SELECT
A. CATEGORY --사용하는 일부 COL만 가져오기
A. ITEM
A. DATE
A.SECTOR
B.DATE
B.CLOSE
B.LOW
B. HIGH--사용하는 일부 COL만 가져오기 / JOIN하는 COL은 두번째 테이블에서 가져오지 않아야 함
FROM TABLE NAME AS A
INNER JOIN TABLE NAME AS B ON A.CATEGORY = B. CATEGORY
WHERE A.CATEGORY = '---'
AND B.DATE >= '2022-00-00' AND B.DATE < '2022-00-00'
----------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
A. CAETORY
C.CAT
C.CAT
FROM TABLE NAME AS A
INNER JOIN TABLE NAME_ AS B ON A.NUM = B. NUM
INNER JOIN TABLE NAME --- AS C ON B. CAT = B. CAT
WHERE A. CATEGORY = N'RETAIL'
ORDER BY C.NUM
2.OUTER JOIN 공부하기
LEFT , RIGHT, FULL 어느쪽 테이블 기준으로 조인하는지
SELECT
A.NUM AS A_NUM
B. NUM AS B_NUM
FROM TABLE NAME AS A
LEFT OUTER JOIN TABLE NAME AS B ON A.NUM = B.NUM
SELECT
A.NUM AS A_NUM
B. NUM AS B_NUM
FROM TABLE NAME AS A
RIGHT OUTER JOIN TABLE NAME AS B ON A.NUM = B.NUM
LEFT OUTER JOIN TABLE NAME AS C ON B.NAME = C. NAME
WHERE A.NUM IS NULL
---LEFT & RIGHT 각각 다른 결과 도출
SELECT
A.NUM AS A_NUM
B. NUM AS B_NUM
FROM TABLE NAME AS A
FULL OUTER JOIN TABLE NAME AS B ON A.NUM = B.NUM
WHERE A.NUM IS NULL OR B.NUM IS NULL
3. CROSS JOIN 공부하기
SELECT
A.NUM
B.NAME
FROM TABLE NAME AS A
CROSS JOIN TABLE NAME2 AS B
--각 데이터에 모든 조합의 수가 필요할 시 CROSS JOIN (EX: 123,ABC -> 1A 1B 1C 2A 2B 2C 3A 3B 3C)
4. SELF JOIN 공부하기
SELECT
A.DATE AS A_DATE
A.[CLOSE] AS A_CLOSE
B.DATE AS B_DATE
B.[CLOSE] AS B_CLOSE
B.[CLOSE]-A.[CLOSE] --CLOSE에서 빼준 것이 새로운 COL으로 생성됨
FROM TABLENAME AS A
LEFT OUTER JOIN TABLE NAME AS B ON A. DATEADD( DAY, -1, B.DATE) -- B에서 하루씩 뺀날짜 기준 으로 나옴
AND A.NAME = B. NAME
WHERE A. NAME = "---"
AND A.DATE >= '2022-00-00' AND A.DATE <'2022-00-00'
AND B.DATE >= '2022-00-00' AND B.DATE <'2022-00-00'
'공부이야기' 카테고리의 다른 글
| SQL공부하기 4일차: WHERE (IN, ANY), FROM, CTE, UNION (1) | 2023.07.25 |
|---|---|
| SQL공부하기 2일차 - 데이터베이스, 테이블 생성, ALTER, CREATE, DROP, INSERT, PARENT&CHILD (1) | 2023.07.20 |
| SQL공부하기 1일차 - WHERE, COUNT, 와일드카드, GROUP BY, ORDER BY (1) | 2023.07.20 |