[ MySQL ] 테이블 조인

Wooju Kang ·2025년 4월 24일
DatabaseRDBMSmysqlsql

[ RDMBS ] MySQL

목록 보기
5/9
post-thumbnail

GIF 출처 : https://logpresso.store/ko/apps/mysql

🖥 Contents

1 ) 조인

2 ) 서브 쿼리

3 ) 공통 테이블 표현식



1 ) 조인

  • 조인 ( Join )
    : 여러 테이블의 컬럼 ( Column ) 정보를 조합하여 새로운 데이터 세트를 구성하는 방식을 조인이라고 한다.

    • 조인의 종류

    ① 내부 조인 ( Inner Join )
    ② 외부 조인 ( Outer Join )
    ③ 교차 조인 ( Cross Join )
    ④ 셀프 조인 ( Self Join )


① 내부 조인 ( Inner Join )


: 내부 조인은 두 테이블의 조건이 모두 맞는 열을 조회한다. 그림과 같이 각 테이블의 교집합 데이터를 조회한다.

< 내부 조인 형식 >

SELECT 
    [ 열 ]
FROM [ 테이블 명 1 ]
   INNER JOIN [ 테이블 명 2 ] ON [ 테이블 명 1.열] = [ 테이블 명 2.열]
WHERE [ 검색 조건 ]

ex )

SELECT
    a.customer_id , a.store_id , a.first_name , a.email , a.address_id 
AS a_address_id,
    b.address_id AS b_address_id, b.address , b.district , b.city_id , b.postal_code , b.phone , b.location 
    // -- 1번
FROM customer AS a
    INNER JOIN address AS b ON a.address_id = b.address_id // -- 2번
WHERE a.first_name = 'ROSA'; // -- 3번

: 순차적으로 설명하면 다음과 같다.

1st. 조회하고자 하는 각 테이블의 컬럼을 SELECT 한다. 이때 , AS 구문을 통해 테이블 이름을 명료화 하고 테이블의 컬럼 중 중복되는 이름의 경우 AS를 통해 변경한다.

2nd. INNER JOIN을 통해 내부 조인임을 명시하며 조인 조건을 나타내는 ON을 통해 조건을 부여한다.

3rd. INNER JOIN을 통해 검색된 내부 조인 결과에서 WHERE문을 통해 세부 조건을 제시한다.

1st 과정에서 컬럼 이름의 중복을 막는 이유는 컬럼의 이름이 유일하지 않으면 DBMS에서는 무슨 테이블의 컬럼을 조회해야 하는지 모르기 때문에 오류가 발생한다. 해당 사항의 경우 address_id라는 컬럼이 customer 테이블인지 address 테이블인지 알지 못한다.

따라서 AS를 통해 a , b로 나누고 각 이름에 따라 '__'를 사용하여 이름을 구분 짓는다.


  • 테이블 3개 이상 조인하기

ex ) 

SELECT
    a.customer_id,a.first_name,a.last_name,
    b.address_id,b.address,b.district,b.postal_code,
    c.city_id,c.city
FROM customer AS a
    INNER JOIN address AS b ON a.address_id = b.address_id
    INNER JOIN city AS c ON b.city_id = c.city_id
WHERE a.first_name = 'ROSA';



② 외부 조인 ( Outer Join )

: 외부 조인은 열의 일치 여부를 고려하지 않고 한쪽 테이블과 다른 쪽 테이블에 조인할 때 사용한다. 예를 들어 상품을 주문한 고객과 상품을 주문하지 않은 고객 데이터를 모두 조회하고 싶은 경우 외부 조인을 사용하면 된다.

외부 조인의 경우 3가지 케이스가 존재한다.

① LEFT OUTER JOIN
② RIGHT OUTER JOIN
③ FULL OUTER JOIN



💫 Case 1 ) LEFT OUTER JOIN

출처 : https://learnsql.com/blog/sql-joins/

: LEFT OUTER JOIN의 경우 왼쪽에 놓인 테이블이 우선 조회 결과에 포함된 뒤 오른쪽 테이블에서 JOIN하고자 하는 기준 컬럼을 비교하여 해당하는 조건의 데이터를 결과에 포함시킨다. 이때 , 해당하는 데이터가 없는 경우 NULL로 처리된다.

이는 모든 OUTER JOIN에서 해당 한다. 따라서 OUTER JOIN은 기준이 되는 데이터에 따라 LEFT , RIGHT , FULL로 나뉜다.

ex )

SELECT
    a.address , a.address_id AS a_address_id ,
    b.address_id AS b , b.store_id
FROM address AS a
    LEFT OUTER JOIN store as b ON a.address_id = b.address_id;


💫 Case 2 ) RIGHT OUTER JOIN

출처 : https://learnsql.com/blog/sql-joins/

: RIGHT OUTER JOIN의 경우 LEFT와 달리 오른쪽 열을 기준으로 우선 조회를 실시한다.

ex ) 

SELECT
    a.address , a.address_id AS a_address_id ,
    b.address_id AS b_address_id , b.store_id
FROM address AS a
    RIGHT OUTER JOIN store AS b ON a.address_id = b.address_id;


💫 Case 3 ) FULL OUTER JOIN

출처 : https://learnsql.com/blog/sql-joins/

: FULL OUTER JOIN의 경우 조건에 일치하는 행 , 일치하지 않는 행 모두를 항목에 표시한다.


ex ) 

SELECT
    a.address , a.address_id AS a_address_id ,
    b.address_id AS b_address_id , b.store_id
FROM address AS a
         LEFT OUTER JOIN store AS b ON a.address_id = b.address_id

UNION // UNION 키워드를 통해 LEFT 와 RIGHT를 통합 

SELECT
    a.address , a.address_id AS a_address_id ,
    b.address_id AS b_address_id , b.store_id
FROM address AS a
    RIGHT OUTER JOIN store AS b ON a.address_id = b.address_id;



③ 교차 조인 ( Cross Join )

출처 : https://learnsql.com/blog/sql-joins/

: 교차 조인 ( Cross Join ) 이란 각 테이블의 모든 경우의 수를 조합한 데이터를 보여주는 조인형식이다. 이를 카르테시안 곱 ( Cartesian Product ) 라고도 한다.


< 교차 조인 기본 형식 >

SELECT [ 열 ] 
FROM [ 테이블 명 1 ] 
   CROSS JOIN [ 테이블 명 2 ] 
WHERE [ 검색 조건 ]

ex ) 

CREATE TABLE Kangwooju_cross1 (num iNT);
CREATE TABLE Kangwooju_cross2 (name VARCHAR(10));
INSERT INTO Kangwooju_cross1 VALUES (1),(2),(3);
INSERT INTO Kangwooju_cross2 VALUES ('Do'),('It'),('SQL');

SELECT
    a.num , b.name
FROM Kangwooju_cross1 AS a
    CROSS JOIN Kangwooju_cross2 AS b
ORDER BY a.num;



④ 셀프 조인 ( Self Join )

출처 : https://lets-viz.com/blogs/sql-self-join/

: 셀프 조인 ( SELF JOIN ) 은 동일한 테이블을 사용하는 특수한 조인으로 기존의 컬럼 변화를 나타낼 때 사용한다 . 기본 형식의 경우 INNER JOIN에서 같은 테이블을 JOIN하는 형식으로 사용하면 된다.


ex )

SELECT
    a.customer_id AS a_customer_id , b.customer_id AS b_customer_id
FROM
    customer AS a
INNER JOIN customer as b ON a.customer_id = b.customer_id;



2 ) 서브 쿼리

  • 서브 쿼리 ( Subqureies )

    : 쿼리 안에 포함되어 있는 또 다른 쿼리를 의미한다. 서브 쿼리를 통해 조인 하지 않은 상태에서 다른 테이블과 일치하는 행을 찾거나 조인 결과를 다시 조인할 때 사용할 수 있다.

    즉 , 쿼리를 통해 나온 결과를 묶어서 쿼리를 조인하거나 다른 작업에 사용할 때 다시 재활용 할 수 있다.

  • 단일 행 서브 쿼리

< 단일 행 서브 쿼리 기본형식 >

SELECT [ 열 ] 
FROM [ 테이블 명 ] 
WHERE [ 열 ] = ( SELECT [ 열 ] FROM [ 테이블 명 ] )

ex ) 

SELECT * FROM customer
WHERE customer_id = (SELECT customer_id FROM customer WHERE first_name = 'ROSA');


  • 다중 행 서브 쿼리

< 다중 행 서브 쿼리 기본형식 ( IN ) > 

SELECT [ 열 ] 
FROM [ 테이블 명 ] 
WHERE [ 열 ] IN ( SELECT [ 열 ] FROM [ 테이블 명 ] )

ex )

SELECT * FROM customer
WHERE customer_id IN (SELECT customer_id FROM customer WHERE first_name IN ('ROSA','ANA'));


< 다중 행 서브 쿼리 기본형식 ( ANY ) > 

SELECT [ 열 ] 
FROM [ 테이블 명 ]
WHERE [ 열 ] = ANY ( SELECT [ 열 ] FROM [ 테이블 명 ] )

ex ) 

SELECT * FROM customer
WHERE customer_id =  ANY (SELECT customer_id FROM customer WHERE first_name IN ('ROSA','ANA'));


< 다중 행 서브 쿼리 기본 형식 ( EXISTS ) > 

SELECT [ 열 ]
FROM [ 테이블 명 ] 
WHERE EXISTS ( SELECT [ 열 ] FROM [ 테이블 명 ] )

ex ) 

SELECT * FROM customer
WHERE EXISTS (SELECT customer_id FROM customer WHERE first_name IN ('ROSA','ANA'));



  • 인라인 뷰 ( Inline View )

: FROM 문에서 사용한 서브 쿼리 결과는 테이블 처럼 사용되어 다른 테이블과 다시 조인할 수 있다. 이때 , FROM 문에 사용하는 서브 쿼리를 인라인 뷰 ( Inline View ) 라고 한다.


< 인라인 뷰 기본형식 > 

SELECT 
     [ 열 ] 
FROM [ 테이블 명 ]  AS [ 치환명 1 ] 
     INNER JOIN ( SELECT [ 열 ] FROM [ 테이블 명 ] WHERE [ 열 ] = [ 값 ] ) AS [ 치환명 2 ] ON [ 치환명 1.열 ] = [ 치환명 2.열 ] 
WHERE [ 열 ] = [ 값 ]

ex ) 

SELECT
    a.film_id , a.title , a.special_features , x.name
FROM film AS a
    INNER JOIN (
        SELECT
            b.film_id , c.name
        FROM film_category AS b
            INNER JOIN category AS c ON b.category_id = c.category_id
        WHERE b.film_id > 10 AND b.film_id < 20 ) // 내부 조인 결과를 하나의 테이블처럼 사용
    AS x ON a.film_id = x.film_id;

: 인라인 뷰는 서브 쿼리의 결과를 마치 테이블 처럼 사용하여 해당 결과에서 원하는 쿼리를 충족하는 데이터를 뽑아낼 수 있어 편리하다.



  • 스칼라 서브 쿼리 ( Scala SubQureies )

: 스칼라 서브 쿼리 ( Scala SubQureies ) 는 SELECT 문에서 사용하는 서브 쿼리를 의미한다.


< 스칼라 서브 쿼리 기본형식 >

SELECT 
    [ 열 ] , ( SELECT < 집계 함수 > [ 열 ] FROM [ 테이블 2 ]
    WHERE [ 테이블 2.열 ] = [ 테이블 1.열 ]) AS a
FROM [ 테이블 1 ]  
WHERE [ 조건 ]

ex ) 

SELECT
    a.film_id , a.title , a.special_features , ( SELECT
                                                     c.name
                                                 FROM film_category AS b
                                                 INNER JOIN category AS c ON b.category_id = c.category_id
                                                 WHERE a.film_id = b.film_id) AS name
FROM film AS a
WHERE a.film_id > 10 AND a.film_id < 20;



3 ) 공통 테이블 표현식

  • 공통 테이블 표현식 ( Common Table Expression : CTE )

    : 공통 테이블 표현식은 실제 데이터베이스에 생성되는 테이블은 아니지만 쿼리 실행 결과를 테이블 처럼 활용하기 위한 논리적 테이블을 만들때 사용되는 표현식이다.

    주로 데이터베이스에 없는 테이블이 필요할 때 사용하며 SELECT 문에만 사용하여 데이터를 조회할 수 있다.

① 일반 CTE

< 일반 CTE 기본형식 >

WITH [ 테이블 ] ( 열 1 , 열 2 , ... ) 
AS
(
   < SELECT 문 > 
)
SELECT [ 열 ] FROM [ 테이블 명 ];

ex ) 

WITH cte_customer (customer_id , first_name , email)
AS
(
    SELECT customer_id , first_name , email FROM customer
    WHERE customer_id >= 10 AND customer_id < 100
)

SELECT * FROM cte_customer;

💫 UNION 문으로 CTE 결합

: UNION 키워드를 통해 각각의 CTE를 결합할 수 있다. 이때 , UNION 문은 중복 데이터를 제거하는 연산을 포함한다. 이에 대한 결과로는 두 CTE의 결과 치를 합치는 것에 대하여 중복을 제거한 것을 반환받는다.

따라서 중복 데이터를 포함하여 SELECT한 결과를 얻어야 하는 경우 UNION ALL 키워드를 사용하는게 좋다.

ex ) 

WITH cte_customer (customer_id , first_name , email)
AS
(
    SELECT customer_id , first_name , email
    FROM customer
    WHERE customer_id >= 10 AND customer_id <= 15

    UNION ALL // 중복 데이터를 포함하여 SEELCT 

    SELECT customer_id , first_name , email
    FROM customer
    WHERE customer_id >= 25 AND customer_id <= 30
)

SELECT * FROM cte_customer;


💫 INTERSECT 문으로 CTE 결합

: INTERSECT 문의 경우 UNION과 달리 두 CTE의 합집합에 대해서 중복을 제거하여 반환한다.

ex ) 

WITH cte_customer (customer_id , first_name , email)
AS
(
    SELECT customer_id , first_name , email
    FROM customer
    WHERE customer_id >= 10 AND customer_id <= 15

    INTERSECT

    SELECT customer_id , first_name , email
    FROM customer
    WHERE customer_id >= 12 AND customer_id <= 20
)

SELECT * FROM cte_customer;


💫 EXCEPT 문으로 CTE 결합

: EXCEPT 문은 결과 값에서 중복을 제거한 유일한 행을 반환한다.

즉 , ( 전체 집합 - INTERSECT 문 결과값 ) 을 반환한다.


ex ) 
WITH cte_customer (customer_id , first_name , email)
AS
(
    SELECT customer_id , first_name , email
    FROM customer
    WHERE customer_id >= 10 AND customer_id <= 15

    EXCEPT

    SELECT customer_id , first_name , email
    FROM customer
    WHERE customer_id >= 12 AND customer_id <= 20
)

SELECT * FROM cte_customer;



② 재귀 CTE

: 재귀 CTE는 CTE 결과를 CTE내부의 쿼리에서 재사용함으로써 반복 실행하는 쿼리 구조를 가진다.


< 재귀 CTE 기본형식 >

WITH RECURSIVE [ CTE_테이블 ] ( 열 1 , 열 2 , ... ) 

AS (
     
     < SELECT * FROM 테이블 A > // -- 1 : 앵커 멤버 
     UNION ALL
     < SELECT * FROM 테이블 B JOIN CTE_테이블 > // -- 2 : 재귀 멤버 
   
   )

SELECT * FROM [ CTE_테이블 ];
  • 앵커 멤버 ( Anchor Member ) : 재귀의 시작점이 되는 멤버
  • 재귀 멤버 ( Recursive Member ) : 재귀의 형식을 보여주는 멤버

ex ) 

WITH RECURSIVE fibonacci_number (n , fibonacci_n , next_fibonacci_n)
AS
(
    SELECT 1 , 0 , 1 // 앵커 멤버 
    UNION ALL
    SELECT n + 1 , next_fibonacci_n , fibonacci_n + next_fibonacci_n
        FROM fibonacci_number WHERE n < 20
        // 재귀 멤버 
)

SELECT * FROM fibonacci_number;

profile
Wooju Kang
배겐드 📡
이전 포스트

[ MySQL ] SQL 기본 문법

다음 포스트

[ MySQL ] SQL 함수

0개의 댓글