[Oracle] join on, case when, 무조건 조인

case when 조건식 then 결과 else 나머지 end(끝 마무리)

join 조인할 테이블 on 조건식

무조건 조인
(where과 같이 조건없이 조인하는 것
각 테이블의 row X row 만큼 row가 생긴다.)

.
.
.
.

조건 조인 join on

SELECT nm
	  ,subject
      ,score
      ,grade
      ,st_rng
      ,et_rng 
FROM ljm_score JOIN ljm_CREDITS ON ST_RNG <= score AND score <= ET_RNG
ORDER BY nm, grade;

---

join하고 on에 조건식 추가

.
.
.
.
.

무조건 조인으로 위와 똑같은 결과 만들었다.
(일단 전체 소환해서 그 중에서 정보를 뽑아내는 방법이기 때문에 위쪽과 비교했을 때 성능이 떨어진다) 조건을 걸어서 할 수 있는 작업이면 조건을 거는게 좋다

(무조건 조인에서는 테이블안에 있는 row의 개수X개수 만큼 출력된다)

SELECT s.NM
	  ,s.SUBJECT
	  ,max(s.SCORE)
	  ,CASE WHEN s.score BETWEEN 91 AND 100 THEN 'A' WHEN s.score BETWEEN 81 AND 90 THEN 'B' WHEN s.score BETWEEN 71 AND 80 THEN 'C' WHEN s.score BETWEEN 61 AND 70 THEN 'D' WHEN s.score BETWEEN 0 AND 60 THEN 'F' END Score
	  ,max(CASE WHEN s.score BETWEEN 91 AND 100 THEN 91 WHEN s.score BETWEEN 81 AND 90 THEN 81 WHEN s.score BETWEEN 71 AND 80 THEN 71 WHEN s.score BETWEEN 61 AND 70 THEN 61 WHEN s.score BETWEEN 0 AND 60 THEN 0 END) ST_ENG
	  ,max(CASE WHEN s.score BETWEEN 91 AND 100 THEN 100 WHEN s.score BETWEEN 81 AND 90 THEN 90 WHEN s.score BETWEEN 71 AND 80 THEN 80 WHEN s.score BETWEEN 61 AND 70 THEN 70 WHEN s.score BETWEEN 0 AND 60 THEN 60 END) ET_RNG
FROM ljm_score s,
(SELECT *
FROM ljm_credits) c
GROUP BY nm, subject, CASE WHEN s.score BETWEEN 91 AND 100 THEN 'A' WHEN s.score BETWEEN 81 AND 90 THEN 'B' WHEN s.score BETWEEN 71 AND 80 THEN 'C' WHEN s.score BETWEEN 61 AND 70 THEN 'D' WHEN s.score BETWEEN 0 AND 60 THEN 'F' END
ORDER BY nm, Score;

---

.
.
.
.

무조건 조인에서는 테이블안에 있는 row의 개수(12)X개수(5) = 60 만큼 출력된 결과

SELECT a.nm nm
	  ,a.subject subject
	  ,a.score score
	  ,CASE WHEN  0 <= score AND score <= 60 THEN 'F' WHEN 61 <= score AND score <= 70 THEN 'D' WHEN 71 <= score AND score <= 80 THEN 'C' WHEN 81 <= score AND score <= 90 THEN 'B' WHEN 91 <= score AND score <= 100 THEN 'A' END grade
	  ,CASE WHEN a.score BETWEEN 91 AND 100 THEN 91 WHEN a.score BETWEEN 81 AND 90 THEN 81 WHEN a.score BETWEEN 71 AND 80 THEN 71 WHEN a.score BETWEEN 61 AND 70 THEN 61 WHEN a.score BETWEEN 0 AND 60 THEN 0 END ST_ENG
	  ,CASE WHEN a.score BETWEEN 91 AND 100 THEN 100 WHEN a.score BETWEEN 81 AND 90 THEN 90 WHEN a.score BETWEEN 71 AND 80 THEN 80 WHEN a.score BETWEEN 61 AND 70 THEN 70 WHEN a.score BETWEEN 0 AND 60 THEN 60 END ET_RNG
FROM ljm_score a,
(SELECT *
FROM ljm_credits) b
ORDER BY nm,score DESC;