SQL(2) - Null value, Aggregate Function, Nested Sebqueries

Null value

 

SQL문에서 Null이란 '값이 없음' 또는 '알 수 없는 값' 으로 두가지 의미를 가집니다.

 

 

만약 5 + null 이라는 식이 있다면 리턴값은 5가 아니라 5와 모르는 값의 합을 반환해야 하므로 다시 Null을 반환하게 됩니다. 

 

 

ID = null 이라는 식에서도 (=)기호는 대입이 아니라 비교 연산자이기 때문에 존재하는 값들끼리 비교를 하지 않고 특정 값을 null과 비교 하게되면 오류를 발생시킵니다. 

 

 

만약 특정 tuple의 atrribute값이 null값인지 확인하고 싶다면 is null이라는 키워드를 사용하면 됩니다.

위 sql문은 월급이 기재가 되어있지 않은 교수들의 이름을 반환합니다.

 

 

하지만 위의 sql의 교수(instructor) 테이블에서 salary가 null값인 tuple이 존재한다면 80000과 비교연산자를 만났을 때 where절에서 null에 비교연산자를 수행했을 때 어떤 값을 반환하는지 알아볼 필요가 있습니다.

 

 

sql에서는 null값의 boolean type을 unknown으로 지정하고 다른 boolean값과 비교 연산을 수행했을 때 결과를 정의했습니다. 그 결과는 다음과 같습니다.

or 연산 : unknown과 true가 만나면 true 다른 경우엔 모두 unknown

and 연산 : unknown과 false가 만나면 false 다른 경우엔 모두 unknown

not 연산 : noknown과 not이 만나면 unknown

 

 

where 절은 true에 해당하는 tuple만 반환하기 때문에 unknown 값을 가진 tuple은 False와 반환되지 않습니다. 

 

 

 

Aggregation

aggregation은 relation내 특정 attribute의 집계 정보 또는 요약 정보를 말합니다.

 

 

대표적으로 평균, 최소값, 최대값, 총합, 갯수 등이 있습니다.

 

 

예를 들어 같은 전공 수업을 듣는 학생들을 나타내는 relation에서

각 학생의 성적 attribute가 있다면 평균값, 최소값, 최대값 등을 밑의 표현식으로 구할 수 있습니다.

위 값들은 전부 incremetal 하다는 특징이 있어서 tuple이 추가되어도 기존에 존재하는 값에 새로 들어온 tuple의 값을 추가하여 계산할 수 있습니다.

 

 

만약 컴퓨터 전공 수업반에 새로 추가된 학생이 있다면 그 학생의 성적과 기존에 존재하는 최소 성적값과 비교하여 전체 relation에 대한 최소값을 계산하는 비용을 고려하지 않아도 됩니다.

 

select min record

from student

where department_name = 'computerSience'

 

이러한 특성을 decomposable이라고 하고 반대로, 최척화가 어렵고 비용이 많이드는 계산들의 특성을

non - decomposable이라고 합니다.

 

 

만약 Attribute에 대해 Sum이나 Count 연산을 실행하는데 Null값이 존재한다면 그 값을 무시하고 계산하여 결과를 출력합니다.

반대로 attribute에 Null값 밖에 존재하지 않는다면 sum은 null, count는 0을 출력합니다.

Group by

group by 라는 표현으로 특정 attribute 값을 가지는 tuple들을 그룹화 시킬 수 있습니다.

 

 

위 sql문은 교수(intructor) relation에서 학과(dept_name)가 같은 교수끼리 그룹화하고(group by) 각 그룹의 속한 교수들의 연봉 평균(avg as avg_salary)을 구합니다.

 

결과는 다음과 같습니다.

 

 

Having

 

 

위의 결과에서 만약 avg_salary가 42000 보다 이상인 dept_name만 조회하고 싶다면

group by 밑줄에 where avg_salary > 42000 이라고 적으면 될 것 같지만  

where는 from 바로 뒷 순서에서 작동하기 때문에 group by 보다 더 빠른 순서에 실행됩니다.

 

 

그래서 where 보다는 그룹화가 끝난 relation에서의 조건을 비교하고 값을 추출하는 표현이 필요한데, 그것이 바로 habving 입니다.

 

이렇게 하면 그룹화가 끝난 relation에서 속성 값들을 비교하고, 적합한 relation을 조회할 수 있습니다.

 

Nested Subquery

 

nested subquery는 쿼리에 쓰이는 relation을 만들기 위한 쿼리 속 쿼리입니다.

 

 

 

위 sql은 instructor 테이블에서 생물학과 교수 보다 더 많은 연봉을 받는 교수들의 이름을 relation으로 반환합니다.

rename으로 S라는 relation을 만든 후에 비교 조건으로 사용합니다. 이것을 nested subquery로 표현하면

 

subquery는 where 절 뒤에 괄호로 쓰여진 쿼리입니다.

rename이 아니라 새로운 쿼리로 relation을 만들고, 그 relation에 메인 쿼리를 실행합니다.

 

 

위 sql의 서브 쿼리에서는 생물학과 교수들의 연봉 relation을 만들고 메인 쿼리에서는 그 relation의 생물학과 교수들의 salary값보다 더 큰 salary값을 가진 교수들의 이름을 반환합니다.

 

 

some 키워드는 서브 쿼리의 salary와 instruct의 salary비교 했을 때 메인 쿼리의 where절이 만족하는 어떤 값이 하나라도 존재한다면 true이고,

all 키워드는 서브 쿼리의 모든 salary와 instruct의 salary비교 했을 때 메인 쿼리의 where절이 만족해야 true를 반환합니다.

 

 

some과 all 같은 키워드들을 set comparison 이라고 합니다.

추가적으로 set membership, set cardinality에 해당하는 특징이 존재합니다.

 

 

set membership에는 in, not in이 존재하고 다음과 같이 사용합니다. 메인 쿼리에 해당하는 tuple 중 in 뒤에 오는 필드에 존재하는 값들만 반환됩니다. in은 메인쿼리와 서브쿼리의 교집합, not in 은 차집합이라고 할 수 있습니다.

 

SELECT *

FROM TABLE 

WHERE COLUMN IN (select * from school where wheigt < 80) 

// 메인 쿼리에서 weight가 80이하인 tuple만 반환

 

SELECT *

FROM TABLE 

WHERE COLUMN NOT IN (select * from school where wheigt < 80) 

// 메인 쿼리에서 weight가 80이상인 tuple만 반환

 

 

set cardinality에는 exist, not exist가 존재하고 다음과 같이 사용합니다.

서브 쿼리에 해당하는 tuple들이 실제 존재하는 값들이라면 메인 쿼리를 정상적으로 수행합니다.

 

SELECT *

FROM TABLE 

WHERE COLUMN EXIST (select * from alpha where name = 'a') 

//alpha 테이블에 a가 존재한다면 메인 쿼리 정상 수행

 

SELECT *

FROM TABLE 

WHERE COLUMN NOT EXIST (select * from alpha where name = 'a')

//alpha 테이블에 a가 존재하지 않는다면 메인 쿼리 정상 수행