[데이타베이스설계] SQL (2)

• 4-1) SQL에서의 집합 연산자
• 4-2) Aggregate 함수
• 4-3) Nested Subqueries
• 4-4) Set

---

 

• 4-1) SQL에서의 집합 연산자

SQL에서는 집합 연산자를 제공한다. 이들은 다른 특수 조건에 대해서 의미있는 데이터 결과를 얻기위해 사용된다.

기본적으로 집합 연산자로부터 나온 결과는 중복 결과를 자동적으로 제거한다. 중복결과를 얻으려면 뒤에 all을 붙인다.

        - UNION

        - UNION ALL (중복결과 허용)

        - INTERSECT

        - INTERSECT ALL (중복결과 허용)

        - EXCEPT

        - EXCEPT ALL (중복결과 허용)

 

• Union operation

두개 혹은 그 이상의 SELECT문의 결과를 결합하기 위해 사용된다. 하지만 중복된 행은 결과에서 제거한다.

union을 적용한 두 테이블은 반드시 같은 개수의 column을 갖고 그 column들은 서로 같은 datatype이여야한다.

UNION

UNION ALL

 

 

• Intersect operation

Intersect 연산자는 두개의 SELECT문에서 공통인 레코드를 반환하기 위해 사용된다.

Intersect로 연결할때 역시 column의 datatype과 개수가 같아야한다.

INTERSECT

 

 

• Except operation (차집합)

두개의 SELECT문에서 첫번째 테이블에만 속하고 두번째 테이블에 속하지 않는 레코드를 결과로 반환한다.

EXCEPT

 

Null Values

tuple에서 null 값을 가질 수 있다. 이 null 값은 value가 아직 안알려져 있거나 존재하지 않음을 뜻한다.

null에 대한 모든 수학적 표현식 결과는 null이다. (ex. 5+null 은 null을 return한다.)

is null predicate는 null 값을 찾는데 사용되는 논리적 문법이다. (ex. select name from insructor where salary is null)

 

 

---

 

• 4-2) Aggregate 함수

 

Aggregate함수는 relation의 column안에 값들에 대해 작동하고 값을 반환한다.

        - avg : 평균값

        - min : 최소값

        - max : 최대값

        - sum : 합

        - count : 개수

aggregate function example

where 조건식을 만족하는 salary의 평균값

semester가 spring이고 year가 2018인 튜플 중에 ID(중복제거) 개수

course 테이블에서 모든 튜플 개수

 

• Group By 절

보통 aggregate 함수랑 (avg, min, max, count, sum) 과 같이 사용된다.

select aggregate(*) from table group by ~

aggregate 함수가 있으면 aggregate 함수에 대한 조건으로 ~로 먼저 묶으라는 뜻이다.

example 1 (query)

example 1 (result 1)

example 1 (result 2)

결과를 보면 result 1은 count에 대해 group by라는 조건이 없어서 모두 count한 결과이고,

result 2는 `age`로 먼저 묶은 후 묶은 결과에 대해 각각 count한 결과이다.

example 2

(키워드 as 는 table 혹은 column에게 별칭을 잠깐 주는 것이다.)

select절에 있는 속성은 그룹별로 먼저 묶은 후 select 절이 수행되어야 한다.

 

• Having 절

Having 절을 사용하면 그룹 결과를 특정 조건에 의해 필터링할 수 있게 한다.

* Having vs Where

        - Having절은 그룹절을 적용한 후 적용되는 조건식이다.

        - Where절은 그룹을 형성하기 전에 적용되는 조건식이다.

 

Null Values 와 Aggregates

Example 1

위 예시에서는 null인 값은 무시한다.

null이 아닌 값이 하나도 없으면 위 예시의 결과는 null이 된다. 즉, null이 아닌값이 하나라도 있으면 수행된다.

count(*)를 제외한 모든 aggregate 연산자는 aggregated된 속성에 null값이 있으면 그 튜플은 무시한다.

(ex. sum(`age`)인데 `age` 속성값이 null인 튜플이 있으면 그 튜플은 제외한다. But, count(*)은 속성들이 다 null이여도 primary key는 null이 될 수 없기 때문에 null인 튜플 무시안하고 어쨋든 다 count한다 라는 뜻 같음..)

 

 

 

---

 

• 4-3) Nested Subqueries

 

SQL에서는 subqueries를 내부에 넣을 수 있는 메카니즘을 제공한다.

Subqueries는 다른 query안에 존재하는 select-from-where 형식이다.

Subqueries는 SQL query에서 다음과 같은 곳에서 존재할 수 있다.

Example 2

        - From 뒤 : r이 subquery로 대체될 수 있다.

        - Where 뒤 : P는 다음 형태로 subquery로 대체된다. (Where B <operation> subquery)

Where 뒤 example

        - select 뒤 : A는 subquery로 대체되고 그러면 single value를 생성한다.

Select 뒤 example

 

*subquery는 일반적으로 다른 테이블과의 비교가 필요할때 즉, query에서 어떤 테이블과 먼저 비교를 하고

다른 테이블과 최종적으로 비교가 필요할때 사용한다. 혹은 같은 테이블 내에서 두 튜플을 비교해야 할때도

table을 두번 부를 수 없기 때문에 subquery를 사용한다.

 

 

 

---

 

• 4-4) Set

 

• Set Membership

SQL에서 set membership은 IN 연산자와 함께 이용될 수 있다.

Set Membership & IN operation Example 1

 

• Set Comparison

SOME

Some example 1

some는 subquery에 있는 row중 최소 하나와 반드시 match 되어야하며

비교연산자와 함께 사용되어야 한다. (ex. Where ~ > SOME(subquery)) - 이는 subquery중 minimum보다 커야함을 뜻

 

같은 테이블내에서 두개의 튜플을 비교할때 : 

1) 테이블을 동시에 두 번 부르는데 as 키워드를 사용하여 각기 다른 참조변수를 사용한다.

Example 1

2) 테이블을 한 번은 외부 query, 나머지 한 번은 내부 subquery를 통해 호출한다.

Example 2

 

ALL

subquery중 하나만이라도 비교연산자를 만족하면 true를 return하는 SOME과 다르게 ALL은 subquery에 있는 모든 row와 비교연산자를 만족시켜야 true를 return한다. 만약 subquery결과로 return된 row가 없으면 true를 반환한다.

Example 1