11. Set and Subquery

Set

Intersection of Queries

• Query: 천만 불 이상의 수입을 가지는 영화 감독이면서 여자 스타인 사람들의 이름과 주소를 찾아라

(SELECT name, address  FROM MovieStar  WHERE gender = 'F')
INTERSECT
(SELECT name, address  FROM MovieExec  WHERE netWorth > 10000000);

Except and Union of Queries

(SELECT name, address FROM MovieStar)
EXCEPT (SELECT name, address FROM MovieExec);

(SELECT title, year FROM Movie)
UNION
(SELECT movieTitle AS title, movieYear AS year  FROM StarsIn);

Subquery

• Join과 Subquery는 거의 같음

• Query: Star Wars의 프로듀서를 찾아라

• Movie(title, year, length, inColor, studioName, producerCNo)MovieExec(name, address, certNo, netWorth)

SELECT name FROM MovieExec
WHERE certNo = (SELECT producerCNo FROM Movie
				WHERE title = 'Star Wars');

SELECT name FROM MovieExec
WHERE certNo = 101;

SELECT name FROM Movie, MovieExec
WHERE title = 'Star Wars' AND producerCNo = certNo;

Conditions Involving Relations

s는 튜플, R은 릴레이션

1. EXISTS is true if R is not empty
   • (NOT EXISTS R)

2. s IN R is true if s is equal to one of the values in R
   • (s NOT IN R)

3. s > ALL R is true if s is greater than every value in unary relation R
   • (NOT s > ALL R)

4. s > ANY R is true if s is greater than at least one value in unary relation R
   • (NOT s > ANY R)

Tuple Representation

• SQL의 튜플은 스칼라 값으로 구성된 리스트로 표현됨
  • (123, 'foo')
  • (name, address, networth)

Tuple Comparison

• (title, year) = (movieTitle, movieYear)은 title = movieTitle AND year = movieYear 과 같음

• (title, year) > (movieTitle, movieYear)은 title > movieTitle OR (title = movieTitle AND year > movieYear)과 같음
  • title = movieTitle 조건을 넣어준 이유는 null 값을 제외하기 위함

 

• Query: Harrison Ford 스타 영화의 프로듀서를 모두 찾아라

SELECT name FROM MovieExec
WHERE certNo IN
	(SELECT producerCNo
    	 FROM Movie
         WHERE (title, year) IN
     		(SELECT movieTitle, movieYear
        	 FROM StarsIn
        	 WHERE starName = 'Harrison Ford'));

SELECT name FROM MovieExec, Movie, StarsIn
WHERE title = movieTitle AND
	year = movieYear AND
    certNo = producerCNo AND
    starName = 'Harrison Ford';

SELECT name FROM MovieExec, Movie, StarsIn
WHERE (title, year) = (movieTitle, movieYear) AND
	certNo = producerCNo AND
    starName = 'Harrison Ford';

Correlated Subqueries

• Query: 두개 이상의 영화에 사용된 title을 찾아라

SELECT title FROM Movie AS Old
WHERE year < ANY (SELECT year FROM Movie
	WHERE title = Old.title);

• nested loop로 생각하면 됨

1. select, from을 통해 첫 번째 튜플을 읽어오고 이를 Old로 바인딩함. 따라서 Old.year < ANY 가 됨

2. 그리고 ANY 구문이 실행되어 Subquery가 실행됨

3. Subquery 내의 year과 titlel은 Movie에서 읽어온 튜플의 값이고 Old.title은 외부에서 읽어온 튜플의 값이 됨

• 이를 scoping rule이라고 부름

• 함수와 같은 스코프들이 지역 변수부터 시작하여 외부로 찾아나가는 개념와 같음

• 위 쿼리를 조인 형태로 표현하면 다음과 같음

SELECT movie1.title FROM Movie movie1, Movie movie2
WHERE movie1.title = movie2.title AND
	movie1.year < movie2.year;

Subqueries in FROM Clauses

• Query: Harrison Ford stars가 출연한 영화의 감독을 모두 찾아라

SELECT name FROM MovieExec, (SELECT producerCNo
	FROM Movie, StarsIn
    WHERE title = movieTitle AND
    year = movieYear AND
    starName = 'Harrison Ford'
    ) Prod
WHERE certNo = Prod.producerCNo;

• FROM 절에서 Subquery를 작성하여 결과로 나온 테이블을 괄호 뒤에 이름을 붙여 사용할 수 있음

SQL Join Expressions in SQL2

• ×(카티션 프로덕트)

  // 최대한 지양해야 하는 쿼리
  SELECT * FROM Movie CROSS JOIN StarsIn;

• ⋈(자연 조인)

  // 조건이 없음
  // 같은 어트리뷰트에 대해서 조인함
  // 스키마가 변경될 여지가 있으므로 지양해야 함
  // JOIN, USING을 사용하는 것이 대안
  SELECT * FROM MovieStar NATURAL JOIN MovieExec;

• ⋈_{\theta}(세타 조인)

  SELECT * FROM Movie JOIN StarsIn ON title = movieTitle AND year = movieYear;

• ⟗(완전 외부 조인)

  SELECT * FROM MovieStar NATURAL FULL OUTER JOIN MovieExec;
  
  SELECT * FROM Movie FULL OUTER JOIN StarsIn ON title = movieTitle AND year = movieYear;

• ⟕(왼쪽 외부 조인)

  SELECT * FROM MovieStar NATURAL LEFT OUTER JOIN MovieExec;
  
  SELECT * FROM Movie LEFT OUTER JOIN StarsIn ON title = movieTitle AND year = movieYear;

• ⟖(오른쪽 외부 조인)

  SELECT * FROM MovieStar NATURAL RIGHT OUTER JOIN MovieExec;
  
  SELECT * FROM Movie RIGHT OUTER JOIN StarsIn ON title = movieTitle AND year = movieYear;

• 예시

SELECT * FROM MovieStar NATURAL FULL OUTER JOIN MovieExec;

SELECT * FROM MovieStar FULL OUTER JOIN MovieExec USING(name, address);

 

 

Uploaded by Notion2Tistory v1.1.0