[MYSQL] 프로그래머스 코딩테스트 SQL Kit 정리(JOIN)

[프로그래머스 SQL Kit]

https://the-story-of-development-growth.tistory.com/27

[MYSQL] 프로그래머스 코딩테스트 SQL Kit 정리 (SELECT,SUM/MAX/MIN , GROUP BY, IS NULL )

SELECT, SUM/MIN/MAX, GROUP BY, IS NULL 부분 정리는 앞에~~~

 

어려웠던 JOIN 문 정리!

---

 

<JOIN>

JOIN 절이란?

두 개 이상의 테이블에서 관련성이 있는 컬럼에 기초하여 행을 결합하는데 사용

 

1. 기본 JOIN 사용 쿼리 문

SELECT [ROW] FROM TABLE_A LEFT JOIN TABLE_B ON TABLE_A.KEY=TABLE_B.KEY

TABLE_A에서 TABLE_B와 붙일 건데, TABLE_A.KEY와 TABLE_B.KEY의 값이 같으므로 이를 조합한다.

ON 뒤에는 조건

=> JOIN은 COLUMN을 나누는 것이 아니라 ROW를 나누는 것

JOIN은 모든 컬럼을 조회할 수 있으며, 두개의 테이블을 연결시킨 ROW값을 나타내는 것이다. 

 

[생활 코딩 JOIN 강의 中]

 

TABLE 1 ) topic 과 TABLE 2 ) author 테이블을 JOIN 할 것이다.

 

topic 테이블과 author 테이블을 topic 테이블의 author_id와 author 테이블의 id를 같다는 기준으로 JOIN 했다.

하지만 다음과 같이 author_id와 id가 중복되는 부분까지 테이블에 나타났고, 

SELECT * FROM topic LEFT JOIN author ON topic.author_id=author.id

(SELECT * FROM topic LEFT JOIN author ON topic.author_id=author.id)문을 사용했을 때 출력 값

author_id와 id는 중복되므로 위 문에서 사용하지 않을 것이다 

따라서 원하는 행만 뽑아서 출력할 것이다.

중복 제거

SELECT topic.id,title,discription,created,name,profile FROM topic LEFT JOIN author ON topic.author_id=author.id;

*TIP* 위의 행 id가 어떤 id인지 헷갈려서 topic_id로 변경해주고 싶다면?

topic.id AS topic_id 라고 해주면, id의 이름이 topic_id로 변경 됌

SELECT topic.id AS topic_id,title,discription,created,name,profile FROM topic LEFT JOIN author ON topic.author_id=author.id;

 

 

 

[SQL JOIN문 형태]

1. A

SELECT * FROM TableA A LEFT JOIN TableB B ON A.KEY=B.KEY

2. A-B

SELECT * FROM TableA A LEFT JOIN TableB B ON A.KEY=B.KEY WHERE B.KEY IS NULL

3. A,B의 합집합

SELECT*FROM TableA A FULL OUTER JOIN TableB B 
ON A.KEY=B.KEY

4. A,B의 합집합에 교집합을 뺀 형태 

SELECT * FROM TableA A
FULL OUTER JOIN TableB B
ON A.KEY=B.KEY
WHERE A.KEY IS NULL OR B.KEY IS NULL

A나 B가 겹치는 부분이 NULL

5. B-A

SELECT * FROM TableA A RIGHT JOIN TableB B ON A.KEY=B.KEY
WHERE A.KEY IS NULL

6. B

SELECT * FROM TableA A RIGHT JOIN TableB B
ON A.KEY=B.KEY

7. A와 B의 교집합

SELECT*FROM TableA A INNER JOIN TableB B ON A.KEY=B.KEY

 

---

 

1. 없어진 기록 찾기  

 

조건: 데이터 유실로 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물 기록 출력

-> ANIMAL_OUTS 기록은 있는데 ANIMAL_INS 기록은 없는 아이

• Allie의 ID 는 ANIMAL_INS 에 없으므로 Allie의 데이터 유실,
• Gia의 ID는 ANIMAL_INS에 있으므로, Gia의 데이터는 유실되지 않음
• Spice의 ID는 ANIMAL_INS에 없으므로, Spice의 데이터는 유실

위의 따라서 Allie와 Spice 두 군데의 ANIMAL_ID와 NAME을 출력시킨다.

SELECT ANIMAL_OUTS.ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS
LEFT JOIN ANIMAL_INS ON ANIMAL_OUTS.ANIMAL_ID=ANIMAL_INS.ANIMAL_ID
WHERE ANIMAL_INS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_OUTS.ANIMAL_ID

1. ANIMAL_OUTS에서 ANIMAL_ID와 NAME을 출력할 것이다.

2. ANIMAL_INS와 ANIMAL_OUTS를 ANIMAL_ID를 기준으로 JOIN할 것이다.

3. 조건문은 ANIMAL_INS의 ANIMAL_ID가 존재하지 않으면(NULL일 경우)에 출력할 것이다.

4. 결과는 ANIMAL_OUTS의 ANIMAL_ID를 오름차순(기본)으로 출력할 것이다. 

 

---

 

2. 있었는데요 없었습니다.

 

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL 문을 작성

결과는 보호 시작일이 더 빠른 순으로 조회  

SELECT ANIMAL_INS.ANIMAL_ID, ANIMAL_INS.NAME
FROM ANIMAL_INS
LEFT JOIN ANIMAL_OUTS ON ANIMAL_INS.ANIMAL_ID=ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.DATETIME>ANIMAL_OUTS.DATETIME
ORDER BY ANIMAL_INS.DATETIME

결과는 보호 시작일이 더 빠른 순으로 조회하므로 ANIMAL_INS가 기준이 된다.

1. 출력 값은 ANIMAL_INS의 ANIMAL_ID와 NAME이 나온다.

2. ANIMAL_OUTS 테이블과 JOIN하고, ANIMAL_ID를 기준으로 ANIMAL_INS와 ANIMAL_OUTS를 JOIN시킨다.

3. 조건은 ANIMAL_INS보다 ANIMAL_OUTS의 DATETIME이 빨라야 하므로 WHERE절은 ANIMAL_INS.DATETIME>ANIMAL_OUTS로 나타낸다.

출력값의 DATETIME 비교

앞에 것이 ANIMAL_INS의 DATETIME이고, 뒤에 것이 ANIMAL_OUTS의 DATETIME!! -> 비교해보면, ANIMAL_OUTS 입양일이 더 빠른 것을 확인할 수 있음 

---

 

3. 오랜 기간 보호한 동물(1)

 

조건: 아직 입양을 못 간 동물 중 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성 

이때, 결과는 보호 시작일 순으로 합니다.

(ANIMAL_INS는 보호소에 들어온 아이, ANIMAL_OUTS는 보호소에서 입양으로 나간 아이)

해석: ANIMAL_OUTS를 못간 애들 중 가장 오래 보호소에 있었던 동물 ? => ANIMAL_INS와 ANIMAL_OUTS가 있다면 AIMAL_INS의 차집합 만큼? 

가장 오래된 애 3마리 출력

SELECT ANIMAL_INS.NAME,ANIMAL_INS.DATETIME
FROM ANIMAL_INS
LEFT JOIN ANIMAL_OUTS ON ANIMAL_INS.ANIMAL_ID = ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_OUTS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_INS.DATE
LIMIT 3

1. 아직 입양을 못 간 동물을 기준으로 하기 때문에 보호소에 있는 ANIMAL_INS를 기준으로 한다.

2. ANIMAL_INS 내에 있는 NAME과 DATETIME을 출력

3. ANIMAL_OUTS 와 ANIMAL_ID를 KEY(기준)으로 해서 JOIN한다.

4. 우리는 입양 가지 않은 동물을 구해야 하므로 ANIMAL_OUTS의 값이 없어야한다.

   따라서 ANIMAL_OUTS.ANIMAL_ID는 NULL값이어야 한다.

5. 가장 오래 보호소에 있는 순이므로 보호소에 들어온 ANIMAL_INS의 DATE값이 오래되어야한다. 

   오름차순으로 정렬해야하므로 ORDER BY ANIMAL_INS.DATE를 기준으로 한다.

6. 가장 오래된 3마리를 구하라 했으므로 상위 3마리를 고른다. LIMIT 3

---

4. 보호소에서 중성화한 동물 

 

조건 : 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화 되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물종, 이름을 조회하는 아이디 순으로 조회하는 SQL문을 작성해주세요

해석: ANIMAL_INS의 SEX_UPON_INTAKE와 ANIMAL_OUTS의 SEX_UPON_OUTCOME이 다른 경우(일치하지 않은 경우)를 출력하면 된다.

출력해야하는 컬럼들은 아이디, 생물 종, 이름 조회이므로 ANIMAL_ID, ANIMAL_TYPE, NAME인데, ORDER BY를 ANIMAL_ID로 정렬한다.

근거 ANIMAL_INS.SEX_UPON_INTAKE = ANIMAL_OUTS.SEX_UPON_OUTCOME일 경우

보호소에 들어올때와 나갈 때가 같으므로, 들어올 때부터 중성화되어 있다는 뜻

(보호소 내에서 전부 중성화가 되었다는 가정 :  샘플 코드에서 OUTS의 SEX_UPON_OUTCOME은 다 중성화 되어 있음)

SELECT ANIMAL_OUTS.ANIMAL_ID,ANIMAL_OUTS.ANIMAL_TYPE, ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS
LEFT JOIN ANIMAL_INS ON ANIMAL_INS.ANIMAL_ID=ANIMAL_OUTS.ANIMAL_ID
WHERE ANIMAL_INS.SEX_UPON_INTAKE!=ANIMAL_OUTS.SEX_UPON_OUTCOME
ORDER BY ANIMAL_OUTS.ANIMAL_ID

 

 

 

 

 

---