[MySQL] SQL ZOO Using Null 답 해설

Using Null - SQLZOO

 

기본 정보

teacher -> id, dept, name, phone, mobile

dept -> id, name

 

#1 NULL은 IS

NULL은 =이 아니라 IS를 사용해야 한다.

SELECT name 
FROM teacher
WHERE dept IS NULL

 

#2 INNER JOIN은 NULL 제외

단순 JOIN문제

SELECT teacher.name, dept.name
FROM teacher 
INNER JOIN dept
ON (teacher.dept=dept.id)

 

#3 LEFT JOIN은 NULL포함

SELECT teacher.name, dept.name
FROM teacher 
LEFT JOIN dept
ON (teacher.dept=dept.id)

 

#4 RIGHT JOIN은 NULL 포함

LEFT JOIN한다음 FROM과 JOIN값을 변경한거랑 똑같음

SELECT teacher.name, dept.name
FROM teacher
RIGHT JOIN dept
ON (teacher.dept=dept.id)

 

#5 COALESCE은 NULL값 대체

COALESCE은 NULL값을 대체 한다.

SELECT teacher.name, COALESCE(teacher.mobile, '07986 444 2266')
FROM teacher

 

#6 COALESCE은 NULL값 대체

COALESCE은 NULL값을 대체 한다.

SELECT teacher.name, COALESCE (dept.name, 'None')
FROM teacher
LEFT JOIN dept
ON (teacher.dept=dept.id)

 

#7 단순 COUNT 

단순 COUNT문제 

COUNT(컬럼명) -> NULL 제외 카운트

COUNT(*) ->NULL 포함 카운트

SELECT COUNT(name), COUNT(mobile)
FROM teacher

 

#8 JOIN COUNT

dept에 teacher을 JOIN시켜주어야 한다.

SELECT dept.name, COUNT(teacher.dept) 
FROM dept
LEFT JOIN teacher 
ON dept.id = teacher.dept
GROUP BY dept.name

 

#9 CASE WHEN 1

dept = 1 OR dept = 2이면 'Sci'

그렇지 않으면 'Art'

SELECT teacher.name, CASE WHEN dept = 1 OR dept = 2
                          THEN 'Sci'
                          ELSE 'Art'
                          END
FROM teacher
LEFT JOIN dept
ON (teacher.dept=dept.id)

 

#10 CASE WHEN 2

dept = 1 OR dept = 2이면 'Sci'

dept = 3 이면 'Art'

그렇지 않으면 'None'

SELECT teacher.name, CASE WHEN dept = 1 OR dept = 2
                          THEN 'Sci'
                          WHEN dept = 3
                          THEN 'Art'
                          ELSE 'None'
                          END
FROM teacher
LEFT JOIN dept
ON (teacher.dept=dept.id)

 

정리하기