Multi-table queries

Foreign key

테이블에서 다른 테이블을 참조할 때 필요한 기능

두 테이블의 데이터 간 연결을 설정하고 강제 적용하여 외래 키 테이블에 저장될 수 있는 데이터를 제어하는 데 사용되는 열이다. 외래 키 참조에서는 한 테이블의 기본 키 값을 가지고 있는 열을 다른 테이블의 열이 참조할 때 두 테이블 간에 연결이 생성된다. 이때 두 번째 테이블에 추가되는 열이 외래 키가 된다.

 

Foreign key and update operation

- 기본 키 값이 다른 테이블의 외래 키로 연결되어 있으면 기본 키 테이블의 행을 삭제하거나 기본 키 값을 변경할 수 없다.

- 외래 키 값에 새 데이터를 추가하는 것은 불가능하고, 기본 키 값에는 가능하다.

 

Join

나누어진 테이블을 하나의 테이블로 연결해주는 기능

조인을 사용하면 테이블 간의 논리적 관계를 기준으로 둘 이상의 테이블에서 데이터를 검색할 수 있다. 조인은 SQL Server에서 특정 테이블의 데이터를 사용하여 다른 테이블의 행을 선택하는 방법을 나타낸다.

조건이 한 테이블의 column만으로 해결하기 어려울 때, 즉 requirement 자체가 두 개 이상의 테이블에 나눠져있을 때 각각의 테이블에서 처리해서 그 결과를 relationship을 통해 연결시켜준다.

 

Several equivalent ways to write a basic join in SQL :

Product (PName, Price, Category, Manufacturer)

Company (CName, StockPrice, County)

1. where 사용

SELECT Pname, Price
FROM Product, Company 
WHERE Manufacturer = CName
       AND Country='Japan'
    AND Price <= 200

2. join 사용

SELECT PName, Price
FROM Product
JOIN Company ON Manufacturer = Cname
		   AND Country='Japan'
WHERE Price <= 200

Intersect

테이블의 교집합을 구해주는 연산 (중복된 레코드 제외)

* select절의 attribute를 기준으로 intersect

* 성립조건 : 데이터 타입 일치, attribute 개수 동일

SELECT R.A
FROM R, S
WHERE R.A = S.A
  INTERSECT
SELECT R.A
FROM R, T
WHERE R.A = T.A

UNION

테이블의 합집합을 구해주는 연산 (중복된 레코드 제외)

SELECT R.A
FROM R, S
WHERE R.A = S.A
  UNION
SELECT R.A
FROM R, T
WHERE R.A = T.A

UNION ALL

테이블의 합집합을 구해주는 연산 (중복된 레코드 포함)

SELECT R.A
FROM R, S
WHERE R.A = S.A
  UNION ALL
SELECT R.A
FROM R, T
WHERE R.A = T.A

MINUS

테이블의 차집합을 구해주는 연산

SELECT R.A
FORM R, S
  MINUS
SELECT R.A
FROM R, T
WHERE R.A = T.A

 

INTERSECT : Still some subtle problems

SELECT는 AND라는 조건을 처리하거나 최종적인 결과를 표현하기 위해 사용된다. 이 두 가지가 일치했을 때 intersect를 사용할 수 있다.

# Headquarters of companies which make gizmos in US AND China

Company (name, hq_city)
Product (pname, make, factory_loc)

SELECT hq_city
FROM Company, Product
WHERE maker = name
   AND factory_loc = 'US'
INTERSECT
SELECT hq_city
FROM Company, Product
WHERE maker = name
   AND factory_loc = 'China'

현재 SELECT의 attribute가 hq_city로 AND의 대상이 hq_city로 되어있다. 이 문제는 hq_city가 일치하는 것을 찾고 싶었던 것이 아니다. US와 China에 있는 company를 찾아 headquater를 뽑고 싶은 것이다.

One Solution : Nested Queries

하나의 쿼리 안에 또 다른 쿼리가 있는 형태 (IN 사용)

위 두 가지 성질을 분리해야 할 때 사용한다. nested query를 통해서 먼저 1차적으로 결과를 찾아내고, 그 결과를 select를 통해서 뽑아낸다.

SELECT DISTINCT hq_city
FROM Company, Product
WHERE maker = name
  AND name IN (
  	      SELECCT maker
              FROM Product
              WHERE factory_loc = 'US'
         AND name IN (
               FROM maker
               FROM Product
               WHERE factory_loc = 'China')

+ ALL (집합 전체), ANU (집합 일부), EXISTS (리턴값 존재하는 지, 안 하는 지)

 

 

* 집합 연산에서는 column의 개수가 같아야 한다.