DBMS - Day6 JOIN, 활용 예제

AS `별명`

: 별명은 대개 단수로 지정한다.

SELECT `province`.`code` AS `지역번호`,
       `province`.`name` AS `지역명`
FROM `region`.`provinces` AS `province`;

• 별명이 적용되어 SELECT 된 것을 확인 할 수 있다.

전의 수업에서 만든 provinces 와 pops를 함께 보여주고 싶다.
그렇다면 두 개의 공통점을 찾아야한다.
Diagrams을 열어서 보자. 연결이 되어있다.

• provinces 의 index와 pops의 province_index가 일치한다.

이때 공통되는 것들끼리 묶을 수 있다. 이때 등장하는게 JOIN개념이다.

LEFT | RIGHT | INNER JOIN

LEFT | RIGHT | INNER JOIN `스키마`. `참고할 테이블` AS `참고할 테이블 별명` ON `조건` 

• 참고할 테이블
  : 우리가 가지고 않은 데이터를 가지고와야 할 테이블을 의미한다. (= pops)

SELECT `province`.`code` AS `지역번호`,
       `province`.`name` AS `지역명`,
       `pop`.`pop` AS `2021년 인구`
FROM `region`.`provinces` AS `province`
LEFT JOIN `region`.`pops` AS `pop` ON `province`.`index` = `pop`.`province_index` AND `pop`.`year` ='2021';

• 여기서 조건을 (ON) 걸어줬는데
  province의 index와 pop의 province_index가 공통이 되는 것만 SELECT해달라는 의미이다. AND 2021년인것만!

• 한 테이블을 SELECT 할 때 여러개의 JOIN 사용가능하다.

< example >

SELECT `province`.`code` AS `지역번호`,
       `province`.`name` AS `지역명`,
       `pop`.`pop` AS `2020년 인구`,
       `pops`.`pop` AS `2021년 인구`,
       CAST(`pops`.`pop` AS SIGNED ) - CAST(`pop`.`pop` AS SIGNED) AS `대비`
FROM `region`.`provinces` AS `province`
LEFT JOIN `region`.`pops` AS `pop` ON `province`.`index` = `pop`.`province_index` AND `pop`.`year` ='2020'
LEFT JOIN `region`.`pops` AS `pops` ON `province`.`index` = `pops`.`province_index` AND `pops`.`year` ='2021';

• COUNT( x ) : x의 개수를 반환한다. x는 열(Cloumn)이거나 리터럴(Literal)일 수 있음.
• CAST( x AS y ) : 값 x를 y형태로 변환한다. 가령, CAST(some_col AS SINED).
  주로 UNSINED인 값들을 연산할 때 음수가 나올 가능성이 있다면 사용.
• CONVERT( x,y ) : CAST( x AS y ) 와 동일
• CEILING( x ) : 값 x에 대해 올림한다.
• FLOOR( x ) :값 x에 대해 내림한다.
• ROUND( x ) : 값 x에 대해 반올림한다.
• ROUND( a, b ) : 값 a에 대해 소숫점 b자리 까지 반올림한다.
• CONCAT( a, b, ... ) : 주어진 값들을 이어 붙인다.

UNSIGNED 인 값 끼리 계산하였을 때 결과가 음수로 나오면 오류가 남.
=> CAST 사용.

< 활용 예제 >

SELECT `province`.`code` AS `지역번호`,
       `province`.`name` AS `지역명`,
       `pop2019`.`pop` AS `2019년 인구`,
       `pop2020`.`pop` AS `2020년 인구`,
       `pop2021`.`pop` AS `2021년 인구`,
       CAST(`pop2021`.`pop` AS SIGNED ) - CAST(`pop2019`.`pop` AS SIGNED) AS `대비 (19~21)`,
       CONCAT(ROUND(((CAST(`pop2021`.`pop` AS SIGNED ) - CAST(`pop2019`.`pop` AS SIGNED)) / CAST(`pop2019`.`pop` AS UNSIGNED) * 100),2), '%') AS `대비율 (19~21)`
FROM `region`.`provinces` AS `province`
LEFT JOIN `region`.`pops` AS `pop2019` ON `province`.`index` = `pop2019`.`province_index` AND `pop2019`.`year` = '2019'
LEFT JOIN `region`.`pops` AS `pop2020` ON `province`.`index` = `pop2020`.`province_index` AND `pop2020`.`year` ='2020'
LEFT JOIN `region`.`pops` AS `pop2021` ON `province`.`index` = `pop2021`.`province_index` AND `pop2021`.`year` ='2021'
ORDER BY `대비 (19~21)`ASC
LIMIT 1;;

인구가 가장 감소한 지역을 뽑으면 대비율 말고 대비로 해야 서울이 나온다.
왜냐하면 %가 붙게되면 DB에서는 더 이상 숫자로 취급하지 않는기 때문이다.
%면 문자열로 취급받기 때문에 "-0.30"인 강원도가 뜨게 된다.
왜냐하면 문자열로 인식하기에 아스키코드에 제일 먼저 뜨는 강원도가 나오게 되는 것이다.