8. JOIN

8.1. JOIN이란?

• 데이터 정규화는 데이터의 중복을 제거하고 무결성을 확보하기 위한 과정입니다.

• 데이터베이스 설계 시, 데이터의 중복을 최소화하고, 데이터의 무결성과 일관성을 유지하기 위해 여러 테이블로 데이터를 분리합니다.

• 예를 들면, 고객 정보와 주문 정보를 별도의 테이블로 분리하여 관리할 수 있습니다.

• 각 테이블은 그 테이블만의 특정한 주제나 목적에 맞게 데이터를 저장합니다.

• 데이터의 삽입, 수정, 삭제 등의 작업이 테이블 별로 독립적으로 이루어지므로 데이터 관리의 효율이 증가합니다.

• 테이블 별로 접근 권한을 설정할 수 있어, 민감한 데이터를 보호할 수 있습니다.

• 급여나 개인 정보 같은 민감 데이터를 포함하는 테이블의 접근을 제한할 수 있습니다.

8.1.1. PK 와 FK

• Primary Key는 테이블 내에서 각 레코드를 고유하게 구분하는 역할을 합니다.

• 중복된 값을 허용하지 않으며, NULL 값을 가질 수 없습니다.

• 하나의 테이블 내에는 오직 하나의 Primary Key만 존재할 수 있습니다.

• Foreign Key는 다른 테이블의 Primary Key를 참조하는 키로, 관계형 데이터베이스에서 두 테이블 간의 관계를 나타내는 중요한 요소입니다.

• Foreign Key를 통해 참조 무결성(Referential Integrity)을 보장합니다. 이는 Foreign Key가 참조하는 다른 테이블의 Primary Key 값과 일치해야 함을 의미합니다.

• 하나의 테이블은 여러 Foreign Key를 가질 수 있으며, 각각 다른 테이블을 참조할 수 있습니다.

8.1.2. JOIN의 유형

• 이는 가장 기본적인 JOIN 유형으로, 두 테이블 간에 일치하는 레코드만 반환합니다.

• 조건에 일치하지 않는 레코드는 결과에서 제외됩니다.

• 왼쪽 테이블의 모든 레코드와 오른쪽 테이블에서 조건에 일치하는 레코드를 반환합니다.

• 오른쪽 테이블에 일치하는 레코드가 없는 경우, 해당 필드는 NULL로 표시됩니다.

• 오른쪽 테이블의 모든 레코드와 왼쪽 테이블에서 조건에 일치하는 레코드를 반환합니다.

• 왼쪽 테이블에 일치하는 레코드가 없는 경우, 해당 필드는 NULL로 표시됩니다.

• 두 테이블의 모든 레코드를 반환합니다.

• 두 테이블 중 어느 한쪽에만 존재하는 레코드는 다른 테이블의 필드 값이 NULL로 표시됩니다.

• 두 테이블의 모든 가능한 조합을 반환합니다.

• 이 JOIN 유형은 특별한 경우에만 사용되며, 두 테이블 간에 일치 조건 없이 모든 조합의 결과를 원할 때 사용됩니다.

• 테이블을 자기 자신과 JOIN 하는 방법입니다.

• 같은 테이블 내에서 레코드 간의 관계를 찾을 때 유용합니다. 예를 들어, 직원과 상사이 모두 같은 테이블에 있을 경우, SELF JOIN을 사용하여 상사-직원 관계를 찾을 수 있습니다.

8.1.3. JOIN 문법

1. 특징: JOIN 키워드와 함께 조인 조건을 명시적으로 ON 절에서 제공합니다.

2. 구문:

# INNER JOIN
SELECT
			*
FROM Table1
INNER JOIN Table2 
	ON Table1.column = Table2.column;

1. 특징: WHERE 절을 사용하여 두 테이블 간의 조인 조건을 제공합니다.

2. 구문:

# INNER JOIN
SELECT
			*
FROM Table1
	 , Table2
WHERE Table1.column = Table2.column;

1. 구문: ANSI JOIN은 JOIN 키워드와 ON 절을 사용하며, ORACLE JOIN은 , (콤마)와 WHERE 절을 사용합니다.

2. 표준성: ANSI JOIN은 ANSI SQL 표준에 따른 것이므로 다양한 RDBMS에서 호환성을 가집니다. 반면, ORACLE JOIN은 Oracle 데이터베이스에 특화된 방식입니다.

8.2. JOIN 연산

8.2.1. INNER JOIN

# ANSI SQL JOIN
SELECT
			*
FROM Table1
[INNER] JOIN Table2
	ON Table1.column = Table2.column;

# ORACLE SQL JOIN
SELECT
			*
FROM Table1
	 , Table2
WHERE Table1.column = Table2.column;

각 테이블의 “department_id” 값이 같은 행끼리 합쳐져서 하나의 테이블을 반환합니다. 하지만 주의해야 할 점으로는 두 테이블 모두 “department_id” 컬럼이 존재하기 때문에 SELECT에서 어느 테이블의 컬럼을 가져올 것인지 반드시 명시해야 합니다.

또한, 각 테이블에 별칭을 활용하면 복잡한 테이블 이름을 간단한 몇 글자로 줄일 수 있어(ON 절에서 별칭 사용 가능), 전체적인 쿼리의 길이와 복잡성을 줄일 수 있습니다.

# ANSI SQL JOIN
SELECT 
			e.employee_id
		, CONCAT(e.first_name, e.last_name) AS name
		, d.department_id
		, d.department_name
FROM `HR.employees` AS e
JOIN `HR.departments` AS d
	ON e.department_id = d.department_id;

# ORACLE SQL JOIN
SELECT 
			e.employee_id
		, CONCAT(e.first_name, e.last_name) AS name
		, d.department_id
		, d.department_name
FROM `HR.employees` AS e
	 , `HR.departments` AS d
WHERE e.department_id = d.department_id;

# ANSI SQL JOIN
SELECT
			e.employee_id
		, CONCAT(e.first_name, e.last_name) AS name
		, d.department_id
		, d.department_name
FROM `HR.employees` AS e
JOIN `HR.departments` AS d
	ON e.department_id = d.department_id
WHERE e.employee_id > 170;

# ORACLE SQL JOIN
SELECT
			e.employee_id
		, CONCAT(e.first_name, e.last_name) AS name
		, d.department_id
		, d.department_name
FROM `HR.employees` AS e
	 , `HR.departments` AS d
WHERE e.department_id = d.department_id
	AND e.employee_id > 170;

SELECT
			employee_id
		, CONCAT(first_name, last_name) AS name
		, department_id
FROM `HR.employees`
WHERE employee_id = 178;

# ANSI SQL JOIN
SELECT
			e.employee_id
		, CONCAT(e.first_name, e.last_name) AS name
		, d.department_name
		, l.street_address
		, l.city
		, l.state_province
		, c.country_name
FROM `HR.employees` AS e 
JOIN `HR.departments` AS d
	ON e.department_id = d.department_id
JOIN `HR.locations` AS l
	ON d.location_id = l.location_id
JOIN `HR.countries` AS c
	ON l.country_id = c.country_id;




# ORACLE SQL JOIN
SELECT
			e.employee_id
		, CONCAT(e.first_name, e.last_name) AS name
		, d.department_name
		, l.street_address
		, l.city
		, l.state_province
		, c.country_name
FROM `HR.employees` AS e
	 , `HR.departments` AS d
	 , `HR.locations` AS l
	 , `HR.countries` AS c
WHERE e.department_id = d.department_id
	AND d.location_id = l.location_id
	AND l.country_id = c.country_id
ORDER BY 1;

8.2.2. LEFT JOIN

LEFT JOIN, 또는 LEFT OUTER JOIN은 왼쪽 테이블의 모든 레코드와 오른쪽 테이블에서 일치하는 레코드가 반환됩니다. 일치하는 오른쪽 테이블의 레코드가 없는 경우, 결과에는 NULL 값이 포함됩니다.

LEFT JOIN은 주로 왼쪽 테이블의 모든 레코드를 포함한 결과를 원할 때 사용됩니다. 예를 들어, 모든 제품과 해당 제품에 대한 주문(있는 경우)을 보여주고 싶을 때 LEFT JOIN을 사용할 수 있습니다.

 

# ANSI SQL JOIN
SELECT
			*
FROM Table1
LEFT [OUTER] JOIN Table2
	ON Table1.column = Table2.column;

SELECT 
			e.employee_id
		, CONCAT(e.first_name, e.last_name) AS name
		, d.department_id
		, d.department_name
FROM `HR.employees` AS e
LEFT JOIN `HR.departments` AS d
	ON e.department_id = d.department_id;

SELECT
			employee_id
		, CONCAT(first_name, last_name) AS name
		, department_id
FROM `HR.employees`
WHERE employee_id = 178;

SELECT 
			e.employee_id
		, CONCAT(e.first_name, e.last_name) AS name
		, d.department_id
		, d.department_name
FROM `HR.employees` AS e
LEFT JOIN `HR.departments` AS d
	ON e.department_id = d.department_id
WHERE e.employee_id > 170;

8.2.3. RIGHT JOIN

RIGHT JOIN, 또는 RIGHT OUTER JOIN은 오른쪽 테이블의 모든 레코드와 왼쪽 테이블에서 일치하는 레코드를 반환합니다. 일치하는 왼쪽 테이블의 레코드가 없는 경우, 결과에는 NULL 값이 포함됩니다.

LEFT JOIN 과는 반대로 RIGHT JOIN은 오른쪽 테이블의 모든 레코드를 포함한 결과를 원할 때 사용됩니다.

 

# ANSI SQL JOIN
SELECT
			*
FROM Table1
RIGHT [OUTER] JOIN Table2
	ON Table1.column = Table2.column;

SELECT 
      e.employee_id
    , e.department_id AS e_department_id
    , d.department_id AS d_department_id
    , d.department_name
FROM `HR.employees` AS e
RIGHT JOIN `HR.departments` AS d
  ON e.department_id = d.department_id
ORDER BY e.department_id;

8.2.4. FULL OUTER JOIN

FULL OUTER JOIN은 두 테이블 중 하나라도 일치하는 모든 행을 포함하는 결과 집합을 반환합니다. 두 테이블에서 일치하지 않는 열은 NULL 값으로 채워집니다.

앞서 설명한 LEFT JOIN과 RIGHT JOIN을 합친 것과 같은 결과를 반환합니다.

# ANSI SQL JOIN
SELECT
			*
FROM Table1
FULL [OUTER] JOIN Table2
ON Table1.column = Table2.column;

SELECT 
			employee_id
		, first_name
		, last_name
		, department_id
FROM `HR.employees`
WHERE department_id IS NULL;

# 왼쪽 결과 테이블
SELECT 
			DISTINCT department_id
FROM `HR.employees`
ORDER BY 1;

# 오른쪽 결과 테이블
SELECT 
			DISTINCT department_id
FROM `HR.departments`
ORDER BY 1;

# ANSI SQL JOIN
SELECT
			e.employee_id
		, e.first_name
		, e.last_name
		, d.department_id
FROM `HR.employees` AS e
FULL JOIN `HR.departments` AS d ON e.department_id = d.department_id;

(SELECT
		   e.employee_id
  	 , e.first_name
	   , e.last_name
		 , d.department_id
FROM `HR.employees` AS e
LEFT JOIN `HR.departments` AS d ON e.department_id = d.department_id)
UNION DISTINCT
(SELECT
			 e.employee_id
		 , e.first_name
		 , e.last_name
		 , d.department_id
FROM `HR.employees` AS e
RIGHT JOIN `HR.departments` AS d ON e.department_id = d.department_id);

8.2.5. CROSS JOIN

# ANSI SQL JOIN
SELECT
			*
FROM Table1
CROSS JOIN Table2;

# ORACLE SQL JOIN
SELECT
			*
FROM Table1, Table2;

# 왼쪽 결과 테이블
SELECT
			location_id
    , street_address
    , city
FROM `HR.locations`;

# 오른쪽 결과 테이블
SELECT
			job_id
    , job_title
FROM `HR.jobs`;

# ANSI SQL JOIN
SELECT
			l.city
		, j.job_title
FROM `HR.locations` AS l
CROSS JOIN `HR.jobs` AS j;

# ORACLE SQL JOIN
SELECT
			l.city
		, j.job_title
FROM `HR.locations` AS l,
		 `HR.jobs` AS j;

SELECT COUNT(job_title)
FROM `HR.jobs`
WHERE max_salary >= 10000;

# ANSI SQL JOIN
SELECT
			c.country_name
		, l.city
		, j.job_title
FROM `HR.locations` AS l
JOIN `HR.countries` AS c ON l.country_id = c.country_id
CROSS JOIN `HR.jobs` AS j
WHERE j.max_salary >= 10000
ORDER BY c.country_name DESC;
# ORACLE SQL JOIN
SELECT
			c.country_name
		, l.city
		, j.job_title
FROM `HR.locations` AS l,
		 `HR.countries` AS c,
		 `HR.jobs` AS j
WHERE l.country_id = c.country_id AND j.max_salary >= 10000
ORDER BY c.country_name DESC;

8.2.6. SELF JOIN

# ANSI SQL JOIN
SELECT
			*
FROM Table1 AS t1
[INNER|LEFT|RIGHT] JOIN Table2 AS t2 ON t1.column1 = t2.column2;

SELECT
			employee_id
		, first_name
    , email
    , department_id
    , manager_id
FROM `HR.employees`;

# ANSI SQL JOIN
SELECT
      e.employee_id
		, e.first_name
    , e.email
    , e.department_id
    , m.employee_id AS manager_id
    , m.first_name AS manager_name
FROM `HR.employees` AS e
LEFT JOIN `HR.employees` AS m ON e.manager_id = m.employee_id;