오랜만에 SQL을 직접 작성하다 보니까, 생각보다 2번부터 헷갈렸다.
USED_GOODS_BOARD와 USED_GOODS_REPLY을 조인해야 한다.
이때 문제에서 주된 테이블은 USED_GOODS_REPLY
이다.
USED_GOODS_REPLY
테이블은 모두 존재해야 하기 때문에 USED_GOODS_REPLY
이 LEFT 테이블이 되고, LEFT 조인을 한다.
✔️ YEAR()와 MONTH() 사용
SELECT board.TITLE,
board.BOARD_ID,
reply.REPLY_ID,
reply.WRITER_ID,
reply.CONTENTS,
DATE_FORMAT(reply.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
FROM USED_GOODS_REPLY AS REPLY
LEFT JOIN USED_GOODS_BOARD AS BOARD
on board.BOARD_ID = reply.BOARD_ID
where YEAR(board.CREATED_DATE) = 2022 and MONTH(board.CREATED_DATE) = 10
order by reply.CREATED_DATE, board.TITLE;
✅ TIP
칼럼 이름이 겹치지 않는 경우에는 굳이 별칭을 명시할 필요는 없다.
SELECT TITLE,
board.BOARD_ID,
REPLY_ID,
reply.WRITER_ID,
reply.CONTENTS,
DATE_FORMAT(reply.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
FROM USED_GOODS_REPLY AS reply
LEFT JOIN USED_GOODS_BOARD AS board
on board.BOARD_ID = reply.BOARD_ID
where YEAR(board.CREATED_DATE) = 2022 and MONTH(board.CREATED_DATE) = 10
order by reply.CREATED_DATE, board.TITLE;
✔️ DATE_FORMAT() 사용
SELECT TITLE,
board.BOARD_ID,
REPLY_ID,
reply.WRITER_ID,
reply.CONTENTS,
DATE_FORMAT(reply.CREATED_DATE, '%Y-%m-%d') as CREATED_DATE
FROM USED_GOODS_REPLY AS reply
LEFT JOIN USED_GOODS_BOARD AS board
on board.BOARD_ID = reply.BOARD_ID
where DATE_FORMAT(board.CREATED_DATE, '%Y-%m') = '2022-10'
order by reply.CREATED_DATE, board.TITLE;
SELECT A.FLAVOR
from FIRST_HALF A
inner join ICECREAM_INFO B
on A.FLAVOR = B.FLAVOR
where A.TOTAL_ORDER>3000 and B.INGREDIENT_TYPE='fruit_based'
order by A.TOTAL_ORDER DESC;
SELECT 조회할 컬럼
FROM <테이블1>, <테이블2>
[WHERE 검색 조건]
SELECT 조회할 컬럼
FROM <테이블1>
INNER JOIN <테이블2>
ON 테이블1.컬럼 = 테이블2.컬럼
[WHERE 검색 조건]
SELECT 조회할 컬럼
FROM <테이블1(LEFT 테이블)>
<LEFT | RIGHT> OUTER JOIN <테이블2(RIGHT 테이블)>
ON <조인 조건>
[WHERE 검색 조건]
SELECT 조회할 컬럼
FROM <테이블1>
CROSS JOIN <테이블2>
[참고]
https://doh-an.tistory.com/30
https://www.w3schools.com/sql/sql_join.asp
https://hongong.hanbit.co.kr/sql-%EA%B8%B0%EB%B3%B8-%EB%AC%B8%EB%B2%95-joininner-outer-cross-self-join/