MySQL Join 요약

join은 뭐야? 왜 사용하는 거야?

 

관계형 데이터베이스는 여러 관계 테이블이 같은 컬럼을 linking하여 이뤄져있고 보통 이 컬럼을 외래키(foreign key)라고 한다.

주문과 주문 상세 테이블이 존재한다고 가정하고 완료된 주문을 찾고 싶다면 주문, 주문상세 테이블에 각 한번씩 쿼리를 날려야한다.

이렇게 불필요하게 쿼리를 2번 날리지 않기 위해서 필요한게 join이다.

join은 테이블간의 공통 컬럼값을 기반으로 하나 이상의 테이블간의 데이터를 연결하는 방법이다.

 

 

포스팅 기준 테이블 및 데이터

CREATE TABLE user (
    user_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (user_id)
);

CREATE TABLE user_info (
    user_info_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (user_info_id)
);

INSERT INTO user(name)
VALUES(‘홍길동’),(‘이순신’),(‘김두한’),(‘김연아’),(‘손흥민’);

INSERT INTO user_info(name,age)
VALUES('홍길동’,50),(‘이순신’,60),(‘김두한’,30),(‘김연아’,25);

 

mysql에서 지원하는 4가지 join 타입

 

1. Inner join

1. 조인 조건에 기초하여 테이블을 조인한다.
2. 첫 번째 테이블의 각 행을 두 번째 테이블의 모든 행과 비교한다.
3. 조인 조건을 만족하는 경우 두 테이블의 select된 모든 열이 합쳐진 새로운 행을 생성한 후 결과 집합에 이 행을 포함시킨다.
4. 조인 조건이 = 연산자를 사용하고 두 테이블을 비교하는 열의 이름이 동일한 경우 USING 절을 사용할 수 있다.

문법

[CASE_1]

SELECT column_list
FROM table_1
INNER JOIN table_2 ON join_condition;

[CASE_2]

SELECT column_list
FROM table_1
INNER JOIN table_2 USING (column_name);

실습

[CASE_1]
SELECT 
    u.user_id, 
    u.name AS user_name, 
    ui.user_info_id, 
    ui.name AS user_info_name
FROM
    user u
INNER JOIN user_info ui ON u.name = ui.name;

[CASE_2]
SELECT 
    u.user_id, 
    u.name AS user_name, 
    ui.user_info_id, 
    ui.name AS user_info_name
FROM
    user u
INNER JOIN user_info ui USING(name);

결과

= 연산자 사용

using 사용

다이어그램

 

2. Left join

• 왼쪽 테이블( from table_name )의 각 행에 대해 오른쪽 테이블( left join table_name )의 모든 행과 비교한다.
• 두 행의 값이 일치하지 않는 경우에도 왼쪽 테이블의 행과 오른쪽 테이블( 모든 열이 NULL 값인 )행을 합친 새로운 행을 결과 집합에 포함한다.
• 즉, Left Join의 경우 왼쪽 테이블의 모든 데이터를 선택한다.

문법

[CASE_1]
SELECT column_list 
FROM table_1 
LEFT JOIN table_2 ON join_condition;

[CASE_2]
SELECT column_list 
FROM table_1 
LEFT JOIN table_2 USING (column_name);

실습

[CASE_1]
SELECT 
    u.user_id, 
    u.name AS user_name, 
    ui.user_info_id, 
    ui.name AS user_info_name
FROM
    user u
INNER JOIN user_info ui ON u.name = ui.name;

[CASE_2]
SELECT 
    u.user_id, 
    u.name AS user_name, 
    ui.user_info_id, 
    ui.name AS user_info_name
FROM
    user u
INNER JOIN user_info ui USING(name);

결과

LEFT JOIN 결과

다이어그램

 

3. Right join

• Left Join과 반대라는 점을 제외하면 Left Join과 유사하다.
• 두 행의 값이 일치하지 않는 경우에도 오른쪽 테이블의 행과 왼쪽 테이블( 모든 열이 NULL 값인 )행을 합친 새로운 행을 결과 집합에 포함한다.
• 오른쪽 테이블을 기준으로 행을 비교하고 오른쪽 테이블의 모든 데이터를 선택한다.

문법

[CASE_1]
SELECT column_list 
FROM table_1 
RIGHT JOIN table_2 ON join_condition;

[CASE_2]
SELECT column_list 
FROM table_1 
RIGHT JOIN table_2 USING (column_name);

실습

[CASE_1]
SELECT 
    u.user_id, 
    u.name AS user_name, 
    ui.user_info_id, 
    ui.name AS user_info_name
FROM
    user u
INNER JOIN user_info ui ON u.name = ui.name;

[CASE_2]
SELECT 
    u.user_id, 
    u.name AS user_name, 
    ui.user_info_id, 
    ui.name AS user_info_name
FROM
    user u
INNER JOIN user_info ui USING(name);

결과

다이어그램

4. Cross join

• inner, left, right 조건과는 다르게 cross조인은 조인 조건이 존재하지 않는다.
• 기준 테이블(from)의 각 행과 비교 테이블( cross join )의 모든 행을 합쳐 결과를 설정한다.

문법

SELECT select_list
FROM table_1
CROSS JOIN table_2;

실습

SELECT 
    u.user_id, 
    u.name AS user_name, 
    ui.user_info_id, 
    ui.name AS user_info_name
FROM
    user u
CROSS JOIN user_info ui;

결과

 

5. Self Join

• 행을 동일한 테이블의 다른 행과 비교하는데 사용한다.
• 테이블 별칭(Alias)을 사용하여 단일 쿼리에서 동일한 테이블 이름이 두번 반복하지 않아야 한다.
• 테이블 별칭을 사용하지 않고 쿼리에서 테이블을 두 번 이상 참조하면 오류가 발생한다.

 

예제

[CASE_1] - 이너 조인을 활용한 셀프조인

SELECT 
    CONCAT(m.lastName, ', ', m.firstName) AS Manager,
    CONCAT(u.lastName, ', ', u.firstName) AS 'Direct report'
FROM
    user u
INNER JOIN user m ON 
    m.employeeNumber = u.reportsTo
ORDER BY 
    Manager;
    
[CASE_1] - 레프트 조인을 활용한 셀프조인
SELECT 
    IFNULL(CONCAT(m.lastname, ', ', m.firstname),
            'Top Manager') AS 'Manager',
    CONCAT(u.lastname, ', ', u.firstname) AS 'Direct report'
FROM
    user u
LEFT JOIN user m ON 
    m.employeeNumber = u.reportsto
ORDER BY 
    manager DESC;