SQL join on 여러개 적용시키는 방법

SQL에서 **JOIN** 문은 여러 테이블을 결합하여 데이터를 조회할 때 사용됩니다. `ON` 조건을 활용하여 두 테이블 간의 관계를 정의하며, 이때 `ON` 조건에 여러 조건을 추가해 복잡한 관계를 설정할 수도 있습니다. 아래에서 `JOIN` 문에 `ON` 조건을 여러 개 붙이는 방법과 이를 활용한 예제, 주의사항 등을 자세히 설명하겠습니다.

---

## 1. SQL JOIN의 기본 개념
`JOIN`은 두 개 이상의 테이블을 하나로 합쳐 데이터를 조회하는 데 사용됩니다. 주로 사용되는 JOIN 유형은 다음과 같습니다:

1. **INNER JOIN**: 두 테이블 간 공통된 데이터만 반환.
2. **LEFT OUTER JOIN**: 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 일치 데이터를 반환.
3. **RIGHT OUTER JOIN**: 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 일치 데이터를 반환.
4. **FULL OUTER JOIN**: 양쪽 테이블의 모든 데이터 반환.
5. **CROSS JOIN**: 모든 조합을 반환.
6. **SELF JOIN**: 같은 테이블을 두 번 참조하여 JOIN.

`ON` 절은 `JOIN`에서 연결 조건을 명시합니다.

 

---

## 2. `ON` 조건에 여러 조건을 사용하는 방법

`ON` 절에 여러 조건을 연결하기 위해 **AND** 또는 **OR** 논리 연산자를 사용할 수 있습니다. 

### 2.1 기본 문법
```sql
SELECT 컬럼명
FROM 테이블1
JOIN 테이블2
ON 조건1 [AND/OR 조건2 AND/OR 조건3 ...];
```

`AND`와 `OR`의 사용 방식에 따라 조건을 조합할 수 있습니다.

---

## 3. 예제: `ON` 조건에 여러 조건 붙이기

### 3.1 두 테이블 간 단순 조인
두 테이블 간 연결 조건이 하나 이상일 때 `ON` 절에 여러 조건을 사용합니다.

#### 예제 데이터
**employees** 테이블:
| emp_id | name      | department_id | location_id |
|--------|-----------|---------------|-------------|
| 1      | Alice     | 10            | 1           |
| 2      | Bob       | 20            | 2           |
| 3      | Charlie   | 30            | 3           |

**departments** 테이블:
| department_id | department_name | location_id |
|---------------|-----------------|-------------|
| 10            | HR              | 1           |
| 20            | IT              | 2           |
| 30            | Finance         | 4           |

 

#### SQL 쿼리: 두 조건으로 조인
```sql
SELECT e.name, d.department_name, e.location_id
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
   AND e.location_id = d.location_id;
```

#### 결과:
| name      | department_name | location_id |
|-----------|-----------------|-------------|
| Alice     | HR              | 1           |
| Bob       | IT              | 2           |

위 쿼리에서는 두 조건이 모두 만족할 때만 데이터를 반환합니다.

---

### 3.2 `OR`을 사용하는 경우
`OR` 연산자를 사용하면 조건 중 하나라도 만족하면 데이터를 반환합니다.

#### SQL 쿼리:
```sql
SELECT e.name, d.department_name, e.location_id
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
   OR e.location_id = d.location_id;
```

#### 결과:
| name      | department_name | location_id |
|-----------|-----------------|-------------|
| Alice     | HR              | 1           |
| Bob       | IT              | 2           |
| Charlie   | Finance         | 3           |

`OR` 조건은 더 많은 결과를 반환할 수 있습니다.

---

### 3.3 복잡한 조건 조합
`ON` 절에서는 괄호를 사용하여 복잡한 조건을 그룹화할 수 있습니다.

#### 예제
```sql
SELECT e.name, d.department_name, e.location_id
FROM employees e
JOIN departments d
ON (e.department_id = d.department_id AND e.location_id = d.location_id)
   OR e.location_id = 3;
```

#### 결과:
| name      | department_name | location_id |
|-----------|-----------------|-------------|
| Alice     | HR              | 1           |
| Bob       | IT              | 2           |
| Charlie   | Finance         | 3           |

이 경우 `AND`와 `OR` 조건을 함께 사용하여 보다 복잡한 필터링을 구현합니다.

---

## 4. 실무 활용 예제

### 4.1 복합키 조건 사용
외래 키 관계가 여러 열에 걸쳐 있을 경우, 두 테이블을 JOIN할 때 다중 조건을 사용해야 합니다.

#### 예제
**orders** 테이블:
| order_id | customer_id | product_id | order_date |
|----------|-------------|------------|------------|
| 1        | 101         | A1         | 2024-01-01 |
| 2        | 102         | B2         | 2024-01-02 |

**order_details** 테이블:
| order_id | product_id | quantity | price |
|----------|------------|----------|-------|
| 1        | A1         | 2        | 10    |
| 2        | B2         | 1        | 20    |

#### SQL 쿼리:
```sql
SELECT o.order_id, o.customer_id, d.product_id, d.quantity, d.price
FROM orders o
JOIN order_details d
ON o.order_id = d.order_id
   AND o.product_id = d.product_id;
```

#### 결과:
| order_id | customer_id | product_id | quantity | price |
|----------|-------------|------------|----------|-------|
| 1        | 101         | A1         | 2        | 10    |
| 2        | 102         | B2         | 1        | 20    |

---

### 4.2 조건에 계산식 포함
JOIN 조건에 계산식을 포함하여 보다 동적인 조건을 구현할 수 있습니다.

#### 예제
```sql
SELECT e.name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
   AND e.location_id + 1 = d.location_id;
```

---

## 5. 주의사항

### 5.1 성능 최적화
`ON` 조건이 복잡할수록 JOIN 연산이 느려질 수 있습니다. 인덱스를 활용하고 쿼리를 최적화하세요.

### 5.2 논리적 오류 방지
`AND`와 `OR`를 혼합할 때 괄호를 사용하여 의도를 명확히 표현하세요.

---

## 6. 정리

`ON` 조건에 여러 조건을 추가하는 방법은 SQL에서 관계를 정확히 정의하고 데이터를 정교하게 필터링하는 데 매우 중요합니다. `AND`와 `OR`의 조합, 계산식, 복합키 등 다양한 방법을 활용해 데이터를 효율적으로 처리할 수 있습니다.