10. Introduction to SQL (4) - Set Operation

10. 1 Basic

1. union: 합집합(or)

2. intersect: 교집합(and)

3. except: 차집합(but not)

- Find courses that ran in Fall 2009 or in Spring 2010

(select course_id from section where sem = ‘Fall’ and year = 2009)
 union
(select course_id from section where sem = ‘Spring’ and year = 2010)

 

- Find courses that ran in Fall 2009 and in Spring 2010

(select course_id from section where sem = ‘Fall’ and year = 2009)
 intersect
(select course_id from section where sem = ‘Spring’ and year = 2010)

 

 - Find courses that ran in Fall 2009 but not in Spring 2010

(select course_id from section where sem = ‘Fall’ and year = 2009)
 except
(select course_id from section where sem = ‘Spring’ and year = 2010)

 

- 자동으로 중복을 제거한다

  중복을 제거하고 싶지 않다면 union all, intersect all, except all을 사용한다.

10. 2 null

- 5 + null  returns null

- 급여가 null인 모든 강사를 찾으시오.

: select name
  from instructor
  where salary is null

- is not null도 존재. null이 아닐때 만족.

 

- OR: (unknown or true)   = true,
       (unknown or false)  = unknown
       (unknown or unknown) = unknown

- AND: (true and unknown)  = unknown,   
         (false and unknown) = false,
         (unknown and unknown) = unknown

- NOT:  (not unknown) = unknown