공통행이 없는 두 테이블의 JOIN

https://www.hackerrank.com/challenges/the-report/problem?isFullScreen=true 

The Report | HackerRank

 

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Grades contains the following data:

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

Write a query to help Eve.

Sample Input

Sample Output

Maria 10 99
Jane 9 81
Julia 9 88 
Scarlet 8 78
NULL 7 63
NULL 7 68

Note

Print "NULL"  as the name if the grade is less than 8.

Explanation

Consider the following table with the grades assigned to the students:

So, the following students got 8, 9 or 10 grades:

• Maria (grade 10)
• Jane (grade 9)
• Julia (grade 9)
• Scarlet (grade 8)

 

위 문제는 GRDAE가 8 이상인 학생이름과 (미만인 NULL)  MIN,MAX_MARK 사이에 있는 MARKS를 구하는 문제이다.

 

정답 

 

SELECT CASE 
    WHEN B.GRADE >=8 THEN NAME
    ELSE 'NULL'
END, B.GRADE, MARKS FROM STUDENTS AS A
INNER JOIN GRADES AS B 
ON A.MARKS BETWEEN B.MIN_MARK AND B.MAX_MARK
ORDER BY GRADE DESC,NAME,MARKS

 

주어진 테이블은 두개이기 때문에 JOIN을 이용하는 문제인 것은 인지했지만 두 테이블에 공통행이 없는 것을 확인했다.

지금껏 공통행을 기준으로만 JOIN을 할 수 있는 줄 알았지만 이 문제를 통해 다른 방법이 있다는 것을 알게 되었다.

 

정답 코드를 보면 우선 CASE WHEN을 이용해 GRADE가 8이상인 학생이름만 표시했고, INNER JOIN까진 기존 코드 형식과 같다. 하지만 조건절 ON을 보면 마치 WHERE절을 이용하는 것처럼 작성이 되어 있다.  이처럼 공통행이 없어도 위와 같은 조건절을 이용해 두 테이블을 JOIN할 수 있다.

 

 

* JOIN절에서 ON과 WHERE은 JOIN하는 범위가 다르다.

SELECT A.ANIMAL_ID,A.ANIMAL_TYPE,A.NAME FROM ANIMAL_INS AS A
LEFT JOIN ANIMAL_OUTS AS B
ON A.ANIMAL_ID=B.ANIMAL_ID
WHERE A.SEX_UPON_INTAKE LIKE '%Intact%' AND B.SEX_UPON_OUTCOME NOT LIKE '%Intact%'
ORDER BY ANIMAL_ID

이전에 작성했던 코드를 가져온 것인데 위 코드는 ANIMAL_ID를 기준으로 JOIN한 후 WHERE절에 맞는 값을 가져오는 것이다.

결과 값은 다음과 같다. 

ANIMAL_ID	ANIMAL_TYPE	NAME
A382192	Dog	Maxwell 2
A410330	Dog	Chewy

 

SELECT A.ANIMAL_ID,A.ANIMAL_TYPE,A.NAME FROM ANIMAL_INS AS A
LEFT JOIN ANIMAL_OUTS AS B
ON (A.ANIMAL_ID=B.ANIMAL_ID AND A.SEX_UPON_INTAKE LIKE '%Intact%' AND B.SEX_UPON_OUTCOME NOT LIKE '%Intact%')
ORDER BY ANIMAL_ID

이 코드는 WHERE절을 ON절에 같이 옮긴 것이다. 이 코드는 ANIMAL_ID가 같고, 위 WHERE절 조건과 같은 열을 기준으로 병합을 하는 것이다. 결과는 다음과 같다.

ANIMAL_ID	ANIMAL_TYPE	NAME
A349996	Cat	Sugar
A350276	Cat	Jewel
A350375	Cat	Meo
A352555	Dog	Harley
A352713	Cat	Gia

·

·

·

위 두 코드의 결과는 확연히 달라지므로 주어진 문제 지문을 잘 읽고 이해해야한다.