[MySQL] 외래키, 조인, 집합 연산자, 서브 쿼리

1. 외래키

1) 외래키

관계형 데이터베이스에서 외래 키는 한 테이블의 필드 중 다른 테이블의 행을 식별할 수 있는 키이다.

2) 외래키 생성 및 옵션

mysql> CREATE TABLE [TABLE명] (
  [COLUMN명] [데이터타입] [옵션] [제약조건], ...
  CONSTRAINT [제약설정명]
    FOREIGN KEY ([외래키명])
    REFERENCES [참조TABLE] ([참조필드명])
    ON DELETE [옵션]
    ON UPDATE [옵션]
  )

옵션	설명
cascade	부모 데이터 삭제, 변경 시 자식 데이터도 동시에 적용된다.
set null	부모 데이터 삭제 시 해당되는 자식 데이터의 컬럼을 NULL로 처리한다.
set default	부모 데이터 삭제 시 자식 데이터의 컬럼은 기본 값으로 갱신한다.
restrict	자식 테이블에 데이터가 남아 있는 경우 부모 테이블의 삭제 또는 변경이 불가하다.

3) 외래키 삭제 및 추가

// 외래키 삭제
mysql> ALTER TABLE [TABLE명] DROP FOREIGN KEY [제약설정명];

// 외래키 추가
mysql> ALTER TABLE [TABLE명] ADD
  CONSTRAINT [제약설정명]
    FOREIGN KEY ([외래키 필드명])
    REFERENCES [참조테이블] ([참조 필드명])
    ON DELETE [옵션]
    ON UPDATE [옵션]

 

 

 

2. 조인

1) 조인

데이터베이스 내의 여러 테이블의 레코드를 조합하여 하나의 열로 표현한 것이다. 두 개 이상의 테이블에서 연관성을 가지고 있는 데이터들을 따로 분류하여 새로운 가상의 테이블을 생성한다. 서로 다른 테이블에서 각각의 공통 값을 이용함으로써 필드를 조합한다.

2) 조인 종류

3조인 종류

3) 조인 사용법

조인에는 크게 크로스 조인, 내부 조인, 외부 조인, 셀프 조인으로 나뉜다.

// 내부 조인 (명시적/암시적 표현)
mysql> SELECT [COLUMN명] ... FROM [TABLE-A명] INNER JOIN [TABLE-B명] ON [조건]; 
mysql> SELECT [COLUMN명] ... FROM [TABLE-A명], [TABLE-B명] WHERE [조건]; 

// 외부 조인 (왼쪽/오른쪽)
mysql> SELECT [COLUMN명] ... FROM [TABLE-A명] LEFT JOIN [TABLE-B명] ON [조건];
mysql> SELECT [COLUMN명] ... FROM [TABLE-A명] RIGHT JOIN [TABLE-B명] ON [조건];

※ MySQL은 FULL OUTER JOIN을 지원하지 않기 때문에 UNION을 활용해서 구현해야한다.

※ 셀프 조인은 자기 자신을 조인하는 기법이다.

 

 

 

3. 집합 연산자

1) 집합 연산자

두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법이다. 여러 개의 질의의 결과를 연결하여 하나로 결합하는 방식을 사용하며 각 테이블의 조회 결과를 하나의 테이블에 합쳐서 반환한다. 조건으로는 SELECT절의 컬럼 수가 동일해야하며 동일 위치에 존재하는 컬럼의 데이터 타입이 상호 호환이 가능해야한다(MySQL은 자동 형 변환이 되기에 동일하지 않아도 된다).

2) UNION

MySQL은 UNION만 사용이 가능하다. 

연산자	설명
UNION	여러 SQL문의 결과에 대한 합집합이며, 중복된 행은 하나의 행으로 표현된다.
UNION ALL	여러 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표현된다.
INTERSECT	여러 SQL문의 결과에 대한 교집합으로 중복된 행은 하나의 행으로 표현된다.
EXCEPT	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합으로 모든 중복된 행은 하나의 행으로 표현된다.

UNION

mysql> SELECT [COLUMN-A명] ... FROM [TABLE-A명] UNION SELECT [COLUMN-B명] ... FROM [TABLE-B명];

 

 

 

4. 서브 쿼리

1) 서브 쿼리

서브 쿼리는 다른 말로 중첩 쿼리라고 하여, 하나의 SQL문안에 포함되어 있는 또 다른 SQL문이다. 알려지지 않은 조건에 근거한 값 들을 검색하는 SELECT 문장을 작성하는데 유용하다. 메인 쿼리가 서브 쿼리를 포함하는 종속적인 관계이다. SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT VALUES, UPDATE SET 위치에 사용가능하다. 서브 쿼리는 반드시 소괄호로 묶어줘야 하며 연산자의 오른쪽에 위치해야한다. 서브 쿼리 내에서는 ORDER BY 문법이 지원되지 않는다.

2) 단일 행 서브 쿼리

서브 쿼리의 실행 결과가 항상 1건 이하인 서브 쿼리를 의미한다. 단일 행 서브 쿼리는 단일 행 비교 연산자(=, >, >=, <, <=, <>, !=)와 함께 사용된다.

mysql> SELECT [COLUMN-A명], ... FROM [TABLE-A명] 
  WHERE [COLUMN-A명] = (
    SELECT [COLUMN-B명] FROM [TABLE-B명]
    WHERE [조건]
  ); 

3) 다중 행 서브 쿼리

서브 쿼리의 실행 결과가 여러 건인 서브 쿼리를 의미한다. 다중 행 서브 쿼리는 다중 행 비교 연산자(IN, NOT IN, ANY, ALL, EXISTS)와 함께 사용된다.

mysql> SELECT [COLUMN-A명], ... FROM [TABLE-A명] 
  WHERE [COLUMN-A명] IN (
    SELECT [COLUMN-B명] FROM [TABLE-B명]
    WHERE [조건]
  ); 

4) 다중 열 서브 쿼리

서브 쿼리의 실행 결과로 여러 컬럼을 반환하는 서브 쿼리를 의미한다. 메인 쿼리의 조건 절에 여러 컬럼을 동시에 비교가 가능하며, 메인 쿼리와 서브쿼리에서 비교하고자 하는 컬럼 개수와 컬럼의 위치가 동일해야한다.

mysql> SELECT [COLUMN-A명], ... FROM [TABLE-A명] 
  WHERE ([COLUMN-A명], [COLUMN-A명]) IN (
    SELECT [COLUMN-B명], [COLUMN-B명] FROM [TABLE-B명]
    WHERE [조건]
  ); 

---

[참고] Database - Practice - 외래키, 조인, 집합 연산자, 서브 쿼리