SQL 기초 - JOIN

 

1. 겹치는 열이 하나일 경우

 

https://school.programmers.co.kr/learn/courses/30/lessons/133025

프로그래머스

 

SELECT FLAVOR
FROM FIRST_HALF, ICECREAM_INFO
WHERE (TOTAL_ORDER>3000) & (INGREDIENT_TYPE=fruit_based)
ORDER BY TOTAL_ORDER ASC ;

 

SQL 실행 중 오류가 발생하였습니다.
Column 'FLAVOR' in field list is ambiguous

 

"FROM FIRST_HARF, ICECREAM_INFO" 이렇게 할 경우 기준 없이 그냥 붙음.

 

SELECT FIRST_HALF.FLAVOR
FROM FIRST_HALF
INNER JOIN ICECREAM_INFO 
ON FIRST_HALF.FLAVOR = ICECREAM_INFO.FLAVOR
WHERE (TOTAL_ORDER>3000) AND (INGREDIENT_TYPE='fruit_based')
ORDER BY TOTAL_ORDER DESC ;

 

또는 

 

SELECT FLAVOR
FROM FIRST_HALF JOIN ICECREAM_INFO USING(FLAVOR)
WHERE (TOTAL_ORDER>3000) AND (INGREDIENT_TYPE='fruit_based')
ORDER BY TOTAL_ORDER DESC ;

 

2. 겹치는 열이 2개 이상

 

https://school.programmers.co.kr/learn/courses/30/lessons/164673

프로그래머스

 

 

SELECT TITLE, A.BOARD_ID, REPLY_ID, B.WRITER_ID, B.CONTENTS,
DATE_FORMAT(B.CREATED_DATE, '%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD AS A, USED_GOODS_REPLY AS B
WHERE A.BOARD_ID = B.BOARD_ID AND A.CREATED_DATE LIKE '2022-10%'
ORDER BY B.CREATED_DATE, TITLE ASC ;