[SQL] 19. JOIN

JOIN

INNER JOIN

• JOIN의 조건을 만족하는 행만 결과값으로 출력되는 JOIN의 방식

 

equi JOIN

• 보편적으로 알고 있는 같은 값을 연결하는 JOIN의 방식
• ' = '로 동일한 값들 간 연결하여 출력하는 JOIN 

SELECT employee_id, last_name, d.department_id, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id ORDER BY employee_id;

 

 

NON-equi JOIN

• 같지 않은 값을 가진 행들을 연결하는 형태의 JOIN

 

CREATE TABLE students

(id INT PRIMARY KEY AUTO_INCREMENT, score INT);

INSERT INTO students (score)

VALUES (90), (60), (88), (33), (72), (100);

SELECT * FROM students;

id|score|

--+-----+

 1|   90|

 2|   60|

 3|   88|

 4|   33|

 5|   72|

 6|  100|

 

CREATE TABLE grades (id VARCHAR(10), min_score INT, max_score INT); INSERT INTO grades VALUES ('A', 90, 100), ('B', 80, 89), ('C', 70, 79), ('D', 60, 69), ('F', 0, 59); SELECT * FROM grades;

id|min_score|max_score|

--+---------+---------+

A |       90|      100|

B |       80|       89|

C |       70|       79|

D |       60|       69|

F |        0|       59|

 

• BETWEEN 연산자로 구간 비교로 조건을 만족하는 행들간 연결된 결과를 출력한다.

SELECT s.id stu_id, score, g.id grade FROM students s JOIN grades g ON score BETWEEN min_score AND max_score;

stu_id|score|grade|

------+-----+-----+

     1|   90|A    |

     2|   60|D    |

     3|   88|B    |

     4|   33|F    |

     5|   72|C    |

     6|  100|A    |

 

• 위의 JOIN으로 만들어낸 결과를 동일하게 CASE로 구현할 수는 있으나, 이 방식은 관련 정보를 한군데의 위치에서 관리하는 형태가 아니므로 여러 위치에서 사용되는 경우 무결성의 문제가 생길 가능성이 있다.

SELECT s.id stu_id, score, CASE WHEN score BETWEEN 90 AND 100 THEN 'A' WHEN score BETWEEN 80 AND 89 THEN 'B' WHEN score BETWEEN 70 AND 79 THEN 'C' WHEN score BETWEEN 60 AND 69 THEN 'D' WHEN score BETWEEN 0 AND 59 THEN 'F' ELSE '등급 외' END grade FROM students s;

stu_id|score|grade|

------+-----+-----+

     1|   90|A    |

     2|   60|D    |

     3|   88|B    |

     4|   33|F    |

     5|   72|C    |

     6|  100|A    |

     7|  110|등급 외 |

OUTER JOIN

• JOIN 조건을 만족하지 않아 결과에서 누락된 행들을 포함하여 같이 출력할 수 있는 JOIN의 문법

LEFT OUTER JOIN

• JOIN 키워드 기준으로 왼쪽의 테이블에서 JOIN의 조건을 만족하지 않던 행을 NULL행과 연결하여 출력해준다.

SELECT employee_id, last_name, d.department_id, department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

employee_id|last_name  |department_id|department_name |

-----------+-----------+-------------+----------------+

        100|King       |           90|Executive       |

        101|Kochhar    |           90|Executive       |

        102|De Haan    |           90|Executive       |

        103|Hunold     |           60|IT              |

…

        176|Taylor     |           80|Sales           |

        177|Livingston |           80|Sales           |

        178|Grant      |        NULL |           NULL |

        179|Johnson    |           80|Sales           |

        180|Taylor     |           50|Shipping        |

        181|Fleaur     |           50|Shipping        |

        182|Sullivan   |           50|Shipping        |

…

RIGHT OUTER JOIN

• JOIN의 오른쪽 테이블의 JOIN되지 않았던 행들을 NULL행과 연결하여 출력

SELECT employee_id, last_name, d.department_id, department_name FROM employees e RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;

employee_id|last_name  |department_id|department_name     |

-----------+-----------+-------------+--------------------+

        200|Whalen     |           10|Administration      |

        201|Hartstein  |           20|Marketing           |

        202|Fay        |           20|Marketing           |

        114|Raphaely   |           30|Purchasing          |

        115|Khoo       |           30|Purchasing          |

…

        113|Popp       |          100|Finance             |

        205|Higgins    |          110|Accounting          |

        206|Gietz      |          110|Accounting          |

           |           |          120|Treasury            |

           |           |          130|Corporate Tax       |

           |           |          140|Control And Credit  |

           |           |          150|Shareholder Services|

…  

 

 

FULL OUTER JOIN

• MySQL에서는 지원하지 않는 문법으로 JOIN의 두 테이블에서 JOIN 되지 않는 행들을 NULL과 JOIN하여 모두 출력해준다.

…

FROM employees e FULL OUTER JOIN departments d

…

 

 

FULL OUTER JOIN (ALTER)

• MySQL에서는 다음과 같이 LEFT OUTER JOIN, RIGHT OUTER JOIN을 UNION으로 조합하여 FULL OUTER JOIN을 구현하는 것이 가능하다.

SELECT employee_id, last_name, d.department_id, department_name FROM employees e RIGHT OUTER JOIN departments d ON e.department_id = d.department_id UNION SELECT employee_id, last_name, d.department_id, department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

 

employee_id|last_name  |department_id|department_name     |

-----------+-----------+-------------+--------------------+

        200|Whalen     |           10|Administration      |

        201|Hartstein  |           20|Marketing           |

        202|Fay        |           20|Marketing           |

        114|Raphaely   |           30|Purchasing          |

        115|Khoo       |           30|Purchasing          |

        116|Baida      |           30|Purchasing          |

…

           |           |          280|Storm               |

           |           |          290|HOME                |

           |           |          300|Coffe               |

           |           |          310|Nap                 |

           |           |          320|GoHome              |

           |           |          330|Finance             |

        178|Grant      |             |                    |

…

 

 

EXISTS

• JOIN과 유사한 문법이지만 JOIN과 같이 직접 테이블을 연결하지 않고 조건을 만족하는 행이 있는지 여부만 확인하여 출력 여부를 정해주는 조건식
• 서브쿼리에서 결과가 존재하는 행이 있는 경우 메인 쿼리의 행을 출력하는 조건
• 사원이 소속된 부서의 ID와 부서 테이블의 부서ID 값이 서로 같다면 행을 출력
• 소속된 사원이 없는 부서의 경우 사원 테이블에서 동일한 부서 ID값을 찾을 수 없기 때문에 결과가 출력되지 않는다

 

SELECT department_id, department_name FROM departments d WHERE EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id );

• NOT 연산자를 조합하게 되면, 서브쿼리의 조건을 만족을 하는 행을 출력하지 않고 만족하지 않는 행을 출력한다

SELECT department_id, department_name FROM departments d WHERE NOT EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id );

사원이 소속된 부서의 ID와 부서 테이블의 부서ID 값이 서로 같다면 행을 출력소속된 사원이 없는 부서의 경우 사원 테이블에서 동일한 부서 ID값을 찾을 수 없기 때문에 결과가 출력되지 않는다

예제2

• • 1~12까지의 고객의 정보가 customers테이블에 있다.
  • 1~8까지의 고객은 주문 정보가 orders테이블에 존재한다
  • 주문의 정보를 출력하지 않지만 주문을 한적이 없는 고객을 찾아야 한다면 JOIN대신 아래와 같이 exists로 대신할 수 있다.
  • exists 연산자는 서브쿼리에서 출력되는 행이 존재하는 경우 참을 돌려주므로 아래와 같이 고객의 id값이 orders에서 주문을 한 고객의 id와 일치하는 값을 가진 행을 출력하도록 서브쿼리를 작성하는 경우 메인쿼리의 고객테이블에 주문 내역에 따른 고객의 정보만 출력할 수 있게된다

 

 

SELECT customer_id, first_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id );

customer_id|first_name|

-----------+----------+

          9|Mason     |

         10|Harper    |

         11|James     |

         12|Olivia    |