4. Join과 Set 연산 (제약조건 등)

 

 

 

 

 

 

▶  1. SET 연산 (집합 연산)

SET 연산은 두 SELECT 결과를 집합 연산자처럼 다루는 SQL 문법입니다.

 

1) MySQL에서 지원되는 집합 연산

UNION	중복 제거 합집합	✅ 지원
UNION ALL	중복 포함 합집합	✅ 지원
INTERSECT	교집합	❌ 미지원 (우회 가능)
MINUS 또는 EXCEPT	차집합	❌ 미지원 (우회 가능)

 

2) 실습예제 (SET)

 

-- UNION
SELECT emp_name FROM employees
UNION
SELECT emp_name FROM retired_employees;

-- UNION ALL
SELECT emp_name FROM employees
UNION ALL
SELECT emp_name FROM retired_employees;

 

❌ MySQL에서 INTERSECT, MINUS(또는 EXCEPT) 미지원 이유:

 

MySQL은 전통적으로 성능 최적화를 위해 집합 연산을 단순화하여 UNION과 UNION ALL만을 공식 지원합니다.
INTERSECT, EXCEPT(MINUS)는 ANSI SQL 표준이지만 MySQL에서는 명시적으로 구현되어 있지 않으며, 아래처럼 우회해야 합니다.

 

-- INTERSECT 우회 방법 (교집합)

SELECT emp_name FROM employees

WHERE emp_name IN ( SELECT emp_name FROM retired_employees );

 

-- MINUS (EXCEPT) 우회 방법 (차집합)

SELECT emp_name FROM employees

WHERE emp_name NOT IN ( SELECT emp_name FROM retired_employees );

 

 

▶ 2. JOIN (조인)

JOIN은 둘 이상의 테이블을 공통 컬럼(키)을 기준으로 결합하여 데이터를 조회하는 SQL 문입니다.

 

1) 종류 및 문법 (MySQL 기준)

INNER JOIN	SELECT ... FROM A INNER JOIN B ON A.id = B.id;	A와 B 모두에 존재하는 데이터만
LEFT JOIN	SELECT ... FROM A LEFT JOIN B ON A.id = B.id;	A는 모두 포함, B는 일치하는 것만
RIGHT JOIN	SELECT ... FROM A RIGHT JOIN B ON A.id = B.id;	B는 모두 포함, A는 일치하는 것만
FULL OUTER JOIN	❌ MySQL 기본 미지원 (대신 UNION으로 우회 가능)	A와 B 모든 데이터 포함

2) 실습예제 (JOIN)

 

-- 테이블 생성
CREATE TABLE employees (
  emp_id INT, emp_name VARCHAR(50)
);

CREATE TABLE salaries (
  emp_id INT, salary INT
);

-- 데이터 삽입
INSERT INTO employees VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO salaries VALUES (1, 5000), (2, 6000);

-- INNER JOIN
SELECT e.emp_name, s.salary
FROM employees e
INNER JOIN salaries s ON e.emp_id = s.emp_id;

3) Oracle과 MySQL 비교 (JOIN)

항목	Oracle	MySQL
FULL OUTER JOIN	지원	❌ 직접 지원 안함
INTERSECT, MINUS	공식 지원	❌ 직접 미지원 (우회 필요)
집합 연산 최적화	고급 기능 많음	기본적인 UNION 중심
서브쿼리 최적화	매우 발달	비교적 단순함

 

▶ 3. 제약조건(Constraints)

제약조건은 테이블에 저장되는 데이터의 정확성 및 일관성을 보장하기 위한 규칙입니다.

 

1) 제약조건의 종류 및 설명

PRIMARY KEY	테이블의 고유 식별자, 중복 불가 & NULL 불가	학생번호, 주민번호 등
FOREIGN KEY	다른 테이블의 기본키를 참조	주문 → 고객
UNIQUE	중복 불가 (NULL 허용)	이메일 주소
NOT NULL	NULL 입력 금지	이름, 연락처
CHECK	특정 조건을 만족해야 함	나이 > 0
DEFAULT	기본값 지정	상태 → '대기'

 

2) 제약조건 확인 방법

 

MySQL은 Oracle과 달리 USER_CONSTRAINTS 같은 뷰는 없지만, information_schema 데이터베이스의 여러 테이블을 통해 확인할 수 있습니다.

 

제약조건 확인용 주요 시스템 테이블	설명
information_schema.TABLE_CONSTRAINTS	테이블별 제약조건 목록
information_schema.KEY_COLUMN_USAGE	제약조건에 포함된 컬럼 정보
information_schema.REFERENTIAL_CONSTRAINTS	외래 키(FK) 제약조건 정보
information_schema.COLUMNS	각 컬럼의 NOT NULL 여부, DEFAULT 등

 

3) 실습예제 (제약조건)

 

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  dept_id INT,
  salary DECIMAL(10,2) CHECK (salary >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

 

# 예시 1: 테이블의 제약조건 목록 보기

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME

FROM information_schema.TABLE_CONSTRAINTS

WHERE TABLE_SCHEMA = 'your_database_name'

AND TABLE_NAME = 'employees';

 

# 예시 2: 특정 제약조건의 컬럼 확인

 

SELECT CONSTRAINT_NAME, COLUMN_NAME

FROM information_schema.KEY_COLUMN_USAGE

WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'employees';

 

# 예시 3: 외래키 상세 정보

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME

FROM information_schema.REFERENTIAL_CONSTRAINTS

WHERE CONSTRAINT_SCHEMA = 'your_database_name';

 

# 예시 4: 제약조건 추가 및 삭제 (ALTER TABLE)

 

-- 제약조건 추가

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

 

-- 제약조건 삭제

 

ALTER TABLE employees DROP PRIMARY KEY;

ALTER TABLE employees DROP FOREIGN KEY fk_dept;

ALTER TABLE employees DROP INDEX email; -- UNIQUE 제약조건은 INDEX로 관리

 

☞ 주의: MySQL은 CHECK 제약조건을 8.0.16 이상부터 공식 지원합니다. 그 이하 버전에서는 무시됩니다.

 

 

4) Oracle과 MySQL 비교 (제약조건)

항목	Oracle	MySQL
CHECK 제약조건	기본 지원	MySQL 8.0.16 이후부터 지원
USER_CONSTRAINTS, ALL_CONSTRAINTS	사용 가능	❌ 없음 (information_schema 사용)
제약조건 이름 자동생성	O	O
제약조건 삭제 방식	DROP CONSTRAINT	DROP FOREIGN KEY, DROP INDEX

 

5) 참고 (제약조건 SQL 예시 종합)

-- 현재 DB에서 모든 제약조건 조회

 

SELECT * FROM information_schema.TABLE_CONSTRAINTS

WHERE TABLE_SCHEMA = DATABASE();

 

 

-- 특정 테이블의 외래키만 조회

 

SELECT * FROM information_schema.KEY_COLUMN_USAGE

WHERE TABLE_NAME = 'employees' AND REFERENCED_TABLE_NAME IS NOT NULL;