7.1 Join queries

Join

관계형 데이터베이스에서 여러 테이블의 데이터를 결합하여 리포트를 생성하는 경우가 많다. 이러한 다중 테이블 리포트는 주로 join문으로 작성된다.

 

Join은 SELECT문으로, 왼쪽 테이블(left table)과 오른쪽 테이블(right table)의 데이터를 하나의 결과로 결합하는 방식이다. Join은 일반적으로 왼쪽 테이블과 오른쪽 테이블의 열(columns)을 비교하여 두 테이블을 결합하며, 이때 비교 연산자로 =을 사용한다. 이때, 비교되는 열들은 비교 가능한 데이터 타입을 가져야 한다.

 

대부분의 경우, Join은 한 테이블의 외래 키(foreign key)를 다른 테이블의 기본 키(primary key)와 비교하여 수행된다. 그러나 Join은 비교 가능한 데이터 타입을 가진 모든 열을 비교하여 수행할 수도 있다.

---

Prefixes and aliases

가끔 join 테이블에 동일한 이름의 열이 포함될 수 있다. 쿼리에 중복된 열 이름이 나타나면 접두사를 사용하여 각 열을 구분해야 한다. 접두사는 테이블 이름 뒤에 점(.)을 붙인 형식이다. Ex: Department.Name

 

접두사를 사용하면 열 이름이 복잡해질 수 있다. 이 경우 별칭(alias)을 사용하여 쿼리나 결과 테이블을 간단하게 만들 수 있다. 별칭은 열 이름 뒤에 AS 키워드를 선택적으로 추가하여 나타낸다. Ex: Department.Name AS Team

 

밑의 그림에서 Name 열이 두 테이블에 모두 나타나는 경우 접두사를 사용하여 구분해야 한다. Department.Name은 Team으로, Employee.Name은 Supervisor로 별칭을 부여하여 결과 열 이름을 더 간단하게 만들 수 있습니다.

Figure 7.1.1: Prefixes and aliases.

SELECT Department.Name AS Team
       Employee.Name AS Supervisor
FROM Department, Employee
WHERE Manager = ID;

---

1) Complete the following query to join Country to City on the Code columns.

SELECT Language, Population
FROM Country, City
WHERE /*  INSERT ANSWER  */

▼View solution

Country.Code=City.Code

2) Complete the following query to generate a result with columns Town and Language. Town is the name of the city where the language is spoken.

SELECT /*  INSERT ANSWER  */, Language
FROM Country, City
WHERE Country.Code = City.Code;

▼View solution

City.Name AS Town

---

Inner and full joins

Join절은 일치하지 않는 행을 join 쿼리에서 어떻게 처리할지를 결정한다. 두 가지 일반적인 join절은 다음과 같다:

• INNER JOIN: 왼쪽 테이블과 오른쪽 테이블에서 일치하는 행만 선택한다.
• FULL JOIN: 일치 여부에 관계없이 왼쪽과 오른쪽 테이블의 모든 행을 선택한다.
  FULL JOIN 결과 테이블에서는 일치하지 않는 왼쪽 테이블 행에 대해 오른쪽 테이블 열에 NULL 값이 표시되며, 오른쪽 테이블의 일치하지 않는 행도 마찬가지로 NULL 값이 표시된다.

Join절은 FROM절과 ON절 사이에 위치합니다:

• FROM절: 왼쪽 테이블을 지정한다.
• INNER JOIN 또는 FULL JOIN 절: 오른쪽 테이블을 지정한다.
• ON절: join할 열을 지정한다.
• WHERE절: 필요에 따라 ON 절 뒤에 사용하여 조건을 추가할 수 있다.

join절은 표준 SQL 구문으로 대부분의 관계형 데이터베이스에서 지원된다. 그러나 MySQL에서는 INNER JOIN을 지원하지만 FULL JOIN은 지원하지 않는다. MySQL join 구문에 대한 자세한 내용은 '더 알아보기' 섹션의 링크를 참조하라.

Captions ▼

1. 내부 join은 INNER JOIN 키워드로 작성된다.
2. 내부 join 결과에는 일치하지 않는 행이 나타나지 않는다.
3. 전체 join은 FULL JOIN 키워드로 작성된다. 두 테이블의 일치하지 않는 행이 결과에 포함된다.

---

1) What is the result of the following query?

SELECT FacultyName, DepartName
FROM Faculty
INNER JOIN Department
ON Faculty.Code = Department.Code;

▼View answer

2) What is the result of the following query?

SELECT FacultyName, DepartmentName
FROM Faculty
FULL JOIN Department
ON Faculty.Code = Department.Code;

▼View answer

---

Left and right joins

일부 경우, 데이터베이스 사용자는 왼쪽 또는 오른쪽 테이블의 일치하지 않는 행만 보고 싶을 수 있다. 이를 위해 관계형 데이터베이스는 왼쪽 join과 오른쪽 join을 지원한다:

• LEFT JOIN: 왼쪽 테이블의 모든 행을 선택하고, 오른쪽 테이블에서는 일치하는 행만 선택한다.
• RIGHT JOIN: 오른쪽 테이블의 모든 행을 선택하고, 왼쪽 테이블에서는 일치하는 행만 선택한다.

외부 조인(outer join)은 일치하지 않는 행을 선택하는 모든 join을 의미하며, 여기에는 왼쪽 join, 오른쪽 join, 전체 join이 포함된다.

MySQL은 LEFT JOIN과 RIGHT JOIN을 모두 지원한다.

Captions ▼

1. 왼쪽 join은 LEFT JOIN 키워드로 작성된다. 왼쪽 테이블인 Department의 일치하지 않는 행이 결과에 나타난다.
2. 오른쪽 join은 RIGHT JOIN 키워드로 작성된다. 오른쪽 테이블인 Employee의 일치하지 않는 행이 결과에 나타난다.

---

 

Alternative join queries

inner join은 JOIN 키워드 없이 작성할 수 있다. outer join은 JOIN 키워드 대신 UNION 키워드를 사용하여 작성할 수 있다. UNION은 두 개의 SELECT절 결과를 하나의 결과 테이블로 결합한다:

• left join의 경우, 하나의 SELECT는 일치하는 행을 반환하고, 다른 하나는 왼쪽 테이블의 일치하지 않는 행을 반환한다.
• right join의 경우, 하나의 SELECT는 일치하는 행을 반환하고, 다른 하나는 오른쪽 테이블의 일치하지 않는 행을 반환한다.
• full join의 경우, 세 개의 SELECT 절이 필요하다. 하나의 SELECT는 일치하는 행을 반환하고, 다른 하나는 왼쪽 테이블의 일치하지 않는 행을 반환하며, 세 번째는 오른쪽 테이블의 일치하지 않는 행을 반환한다. 이 세 개의 결과는 두 개의 UNION 키워드로 병합된다.

JOIN 키워드를 사용하는 것은 좋은 습관이다. UNION으로 작성된 join 쿼리는 복잡하고 이해하기 어렵다. LEFT JOIN, RIGHT JOIN, FULL JOIN은 join의 동작을 명확히 하고 쿼리를 단순화한다.

사용 예시

RIGHT JOIN

SELECT FacultyName, DepartmentName 
FROM Faculty, Department
WHERE Faculty.Code = Department.Code
UNION
SELECT NULL, DepartmentName
FROM Department
WHERE Department.Code NOT IN
   (SELECT Code FROM Faculty WHERE Code IS NOT NULL);

오른쪽 테이블은 Department입니다. 첫 번째 SELECT 절은 일치하는 행을 반환하고, 두 번째 SELECT 절은 일치하지 않는 Department의 행을 반환합니다. UNION 키워드는 일치하는 행과 일치하지 않는 행을 하나의 결과 테이블로 병합합니다. Code가 Department의 기본 키이므로, 두 번째 WHERE 절에서 OR Department.Code IS NULL 조건은 필요하지 않습니다.

 

LEFT JOIN

SELECT FacultyName, DepartmentName 
FROM Faculty, Department
WHERE Faculty.Code = Department.Code
UNION
SELECT FacultyName, NULL
FROM Faculty
WHERE Faculty.Code IS NULL;

왼쪽 테이블은 **Faculty**입니다. 첫 번째 **SELECT** 절은 일치하는 행을 반환하고, 두 번째 **SELECT** 절은 일치하지 않는 **Faculty**의 행을 반환합니다. **UNION** 키워드는 일치하는 행과 일치하지 않는 행을 하나의 결과 테이블로 병합합니다. **Faculty.Code**가 외래 키이므로, 두 번째 **WHERE** 절에서 **OR Faculty.Code NOT IN (SELECT Code FROM Department WHERE CODE IS NOT NULL)** 조건은 필요하지 않습니다.

 

INNER JOIN

SELECT FacultyName, DepartmentName 
FROM Faculty, Department
WHERE Faculty.Code = Department.Code;

이 쿼리는 Faculty와 Department의 코드가 일치하는 행만 반환합니다. inner join은 UNION 키워드 없이 작성됩니다.

 

FULL JOIN

SELECT FacultyName, DepartmentName 
FROM Faculty, Department
WHERE Faculty.Code = Department.Code
UNION
SELECT FacultyName, NULL
FROM Faculty
WHERE  Faculty.Code IS NULL
UNION
SELECT NULL, DepartmentName
FROM Department
WHERE Department.Code NOT IN 
   (SELECT Code FROM Faculty WHERE CODE IS NOT NULL);

첫 번째 SELECT 절은 일치하는 행을 반환하고, 두 번째 SELECT 절은 일치하지 않는 Faculty의 행을 반환하며, 세 번째 SELECT 절은 일치하지 않는 Department의 행을 반환합니다. UNION 키워드는 일치하는 행과 일치하지 않는 행을 하나의 결과 테이블로 병합합니다. Department.Code는 기본 키이고 Faculty.Code는 외래 키이므로, 두 번째와 세 번째 WHERE 절은 간단하게 작성될 수 있습니다.