기본 SQL 문법 (3) Multi-table Query

FOREIGN KEY

다른 테이블의 Primary Key 필드를 참조하는 필드를 말한다.

참조하는 데이터의 “참조 무결성(referential integrity)”을 확인하기 위해 사용된다. 다시 말해, 참조하는 데이터가 유효한 값인지 확인하기 위해 사용된다.

FOREIGN KEY 생성
1. 다른 테이블을 생성한다.
2. Students 테이블에 cid라는 Foreign Key 를 추가한다.
```
 --- Course 테이블의 cid 를 참조하는 FOREIGN KEY 필드인 cid 를 Students 에 추가합니다.
        
 --- Students 테이블 생성시 추가하는 방법입니다.
 CREATE TABLE Students (
     ...,
     cid int unsigned NOT NULL,
     FOREIGN KEY (cid) REFERENCES Course (cid)
 );
        
 --- Students 테이블 생성 이후에 추가하는 방법입니다.
 ALTER TABLE Students ADD COLUMN cid int unsigned NOT NULL;
 UPDATE Students SET cid = 10;             --- 주의사항(3) 참고
 ALTER TABLE Students ADD **FOREIGN KEY (cid) REFERENCES Course (cid);**
```
  - 주의사항
    - 참조하기 전에 참조되는 테이블(Course)이 만들어져있어야 한다.
    - 참조하는 컬럼(Students.cid)과 참조되는 컬럼(Course.cid)의 데이터 타입과 길이가 같아야 한다. (ex. signed int ≠ int unsigned) 참고
    - “참조 무결성” 원칙에 따라, 참조하는 칼럼과 참조되는 칼럼의 값이 항상 일관돼야 한다. 즉, 참조되는 칼럼에 없는 값을 참조하는 칼럼이 가질 수 없으며, 참조되는 테이블에서 값을 변경시켰다면 참조하는 테이블에도 변경사항이 적용돼야 한다. 참고
  - 제약조건
    ALTER TABLE Students ADD FOREIGN KEY (cid) REFERENCES Course (cid) ON DELETE CASCADE; ON UPDATE {NO ACTION|CASCADE|SET NULL|SET DEFAULT};
    - ON DELETE CASCADE : 참조하는 키가 포함된 행을 삭제하려고 하면 해당 Foreign Key 가 포함되어 있는 모든 행도 삭제
    - ON UPDATE CASCADE : 참조하는 키 값이 포함된 행에서 키 값을 업데이트 하면 해당 Foreign Key 를 구성하는 모든 값도 키에 지정된 새 값으로 업데이트되도록 지정

FOREIGN KEY 삭제

  ALTER TABLE Students DROP FOREIGN KEY Students_ibfk_1;
  ALTER TABLE Students DROP cid;

테이블 JOIN하기

여러 테이블을 조합해 하나의 결과 테이블을 만든다. 테이블들이 적어도 하나의 필드column를 공유하고 있어야 한다. 결과 테이블은 (주어진 조건에 맞는) 두 테이블의 튜플들의 가능한 모든 조합을 포함한다. 즉, JOIN의 의미는

두 테이블의 cross product 를 구해 ex. {a,b} x {1,2} = {a,1} {a,2} {b,1} {b,2}
Selection/조건을 적용해 필터링하고
Pojection을 적용해 결과 테이블을 얻는다는 의미이다.

Untitled

다음 두 테이블을 가정한다.

테이블 생성 쿼리

  CREATE TABLE Course (
    cid int unsigned NOT NULL PRIMARY KEY,
    name varchar(20)
  );
    
  INSERT INTO Course VALUES
  (10, 'Database'),
  (11, 'OS'),
  (12, 'Network'),
  (90, 'Algorithm');

  CREATE TABLE Students ( 
  		sid int NOT NULL AUTO_INCREMENT,
  		name char(10) NOT NULL DEFAULT '',
  		department varchar(20),
  		cid int unsigned NOT NULL,
  		PRIMARY KEY (sid),
  		FOREIGN KEY (cid) REFERENCES Course(cid)
  );
    
  INSERT INTO Students VALUES
  (100, 'Fred', 'Computer Science', 10),
  (NULL, 'David', 'Electronics', 11),
  (NULL, 'John', 'Electronics', 12),
  (NULL, 'Jake', 'Computer Science', 10),
  (NULL, 'George', 'Computer Science', 11);

Untitled

(INNER) JOIN

일반적인 조인. 지정한 필드(조인 컬럼)의 값이 두 테이블 모두에 있는 경우에만 조회된다.

  SELECT * 
  FROM Students 
  INNER JOIN Course                              --- INNER 생략 가능합니다.
  ON Students.cid = Course.cid;
  --- cid 값이 동일한 튜플끼리 전부 조합해서 
  --- Students 테이블과 Course 테이블을 결합해줍니다.
    
  SELECT *
  FROM Students, Course
  WHERE Students.cid = Course.cid;
  --- 결과는 위와 동일합니다.
  --- 표준 SQL 방식과는 별도로 MySQL에서만 사용하는 방식입니다.

즉, 다음 경우에 Course 테이블의 cid가 90인 튜플은 Students에 cid가 90인 튜플이 없으므로 결과 테이블에 나타나지 않는다.

Untitled =>

OUTER JOIN

기준 테이블에만 데이터가 존재하면 조회된다. 즉, 조인 테이블에 데이터가 있든 없든 기준 테이블의 데이터는 모두 조회된다. 그리고 조인 테이블에 데이터가 없다면 NULL값이 채워진다.
기준 테이블을 무엇으로 정할지에 따라 LEFT RIGHT FULL 이 정해진다. 참고로, 오라클에는 (FULL) OUTER JOIN이 있지만 MySQL에는 없기 때문에 LEFT JOIN+RIGHT JOIN 로 대체한다.
```
  SELECT * 
  FROM Students
  RIGHT OUTER JOIN Course             --- OUTER 생략 가능합니다.
  ON Students.cid = Course.cid;
```
CROSS JOIN

카티션 곱(Cartesian product) (또는 중첩 for문)의 개념이다. 특정 기준 없이 두 테이블 간에 가능한 모든 튜플 조합의 경우의 수를 보여준다. ON 절이 따로 필요 없다. 결과의 개수는 n(A) * n(B) 이다.
```
  SELECT * 
  FROM Students
  CROSS join Course
    
  --- 결과는 위와 동일합니다.
  SELECT * FROM Students, Course
```
SELF JOIN

자기 자신의 테이블과 (INNER) JOIN 한다. 각 테이블을 구별하기 위해 별칭(Alias)를 만들어줘야 한다.
```
  SELECT *
  FROM Course C1
  INNER JOIN Course C2       --- AS 생략한 형태입니다.
```

Nested Query

복잡한 쿼리를 쉽게 표현하기 위해서 만들어졌다. SQL 문 안에 또 다른 SQL 문을 포함하고 있는 구조이다. 내부 쿼리가 먼저 (단 한번) 실행되고, 그 결과를 바탕으로 외부 쿼리가 실행된다.

SELECT *
FROM 한양대학생 AS S
WHERE S.이름 IN (
		SELECT K.이름
		FROM 대한민국국민 AS K
		WHERE K.나이 = 20);

EXISTS / NOT EXISTS

EXIST 안의 조건에 부합하는 튜플이 “존재”해야만 전체 결과를 출력한다.

  SELECT *
  FROM Students
  WHERE EXISTS (
  		SELECT * FROM Course
  		WHERE Course.cid = 10 AND Students.cid = Course.cid);

ALL / ANY

  SELECT name
  FROM Product
  WHERE price > ALL (
  		SELECT price
  		FROM Product
  		WHERE maker = ‘Gizmo-Works’)

View

자주 쓰이는 쿼리 결과를 임시 저장할 수 있게 한다. 테이블의 형태이지만 데이터베이스에 저장되지 않는다.

CREATE VIEW Class_Info AS
SELECT Students.sid, Course.cid
FROM Students, Course
WHERE Students.cid = Course.cid;
//
SELECT Class_Info.sid, Course.cid 
FROM Course,
( SELECT Students.sid, Course.cid
	FROM Students, Course
	WHERE Students.cid = Course.cid) AS Class_Info
WHERE Course.cid = Class_Info.cid;
--- 이 View는 해당 쿼리 안에서만 유효합니다.
//
WITH Class_Info(sid, icid) AS
( SELECT Students.sid, Course.cid
	FROM Students, Course
	WHERE Students.cid = Course.cid),
--- VIEW와 비슷하지만 여러 개의 테이블을 만들 수 있습니다.

Untitled

주의사항
- 같은 이름의 필드가 존재할 수 없다. ex. 위 구문에서 SELECT * 를 하게 되면 만들어지는 view에 cid 필드가 두 개 존재하며(Students.cid, Course.cid) 에러가 발생한다.

📌 질문
Q. inner join과 outer join 차이
Q. having과 where 차이
Q. group by의 역할
Q. JOIN vs. Sub-query?