MySQL JOIN문

JOIN절은 나 자신, 혹은 두개 이상의 테이블의 행을 결합하여 조회하는데 사용한다.

본문에서 다룰 내용은 INNER, LEFT/RIGHT, CROSS, SELF 총 5개다.

설명하기에 앞서 W3Schools에서 제공하는 Demo Database에서 테이블 3개를 준비했다.

 

● Customers

 - 고객명단 테이블

 - CustomerID == PK

● Orders

 - 주문내역 테이블

 - OrderID == PK, CustomerID == PK

● Shippers

- 화주(송하인) 테이블

 

INNER JOIN

( W3Schools 인용 )

INNER JOIN이란 서로 다른 테이블의 데이터를 공통된 용도로 사용되는 Column으로 관계를 형성하여 교집합 데이터를 한 레코드로 반환한다. 

 

ex) 고객의 이름과 해당 고객의 주문내역(번호)이 필요하다.

고객의 이름은 Customers테이블, 주문내역(번호)은 Orders테이블에 있다. 두 데이터를 한 레코드로 반환하기 위해선 두 테이블간의 공통Column을 찾아 관계를 형성해야 한다.

위 예제의 Customers의 CustomerID와 Orders의 CustomerID는 동일한 용도로서 사용하고 있는 Column이므로 이를 이용해 두 테이블을 JOIN한다.

( W3Schools Demo Database 인용 )

SELECT Orders.OrderID,
               Customers.CustomerID
FROM (
Customers INNER JOIN Orders                  -> 테이블 명시의 순서는 중요하지 않다.
ON Customers.CustomerID = Orders.CustomerID
   -> 두 테이블의 공통 Column인 CustomerID를 ON 뒤에 비교연산자로서 명시한다.
)

괄호를 친 부분이 FROM절에 명시될 하나의 레코드다.

쉽게 말해 Customers와 Orders 두 테이블을 ON뒤에 명시된 'CustomerID가 같다'라는 조건에 따라 INNER JOIN을 하게 되고 나온 결과물에서 OrderID와 CustomerID만 반환받고 있는 것이다.

 

INNER JOIN은 생략이 가능하다.

SELECT Orders.OrderID
               Customers.CustomerID
FROM (Customer,
            Orders
WHERE Customers.CustomerID = Orders.OrderID)

','로 테이블을 구분지어 INNER JOIN이 생략이 가능하며 이때는 ON절이 아닌 WHERE절을 사용한다.

 

3개 이상의 테이블도 JOIN이 가능하다.

( W3Schools Demo Database 인용 )

SELECT Orders.OrderID, 
               Customers.CustomerName,
               Shippers.ShipperName
FROM
(
( Orders INNER JOIN Shippers
                             ON Orders.ShipperID = Shippers.ShipperID )
INNER JOIN Customers
               ON Orders.CustomerID = Customers.CustomerID
)

파란색으로 표시된 부분의 문장을 먼저 실행한 후(Orders와 Shippers를 ShipperID로 INNER JOIN)의 레코드를 Customers와 INNER JOIN하고 있다.

 

 

LEFT JOIN

( W3Schools 인용 )

FROM절에 위치한 테이블(table1)에 JOIN뒤에 명시된 테이블(table2)이 JOIN된다.

table1에 해당하는 모든 레코드를 반환하며 ON절의 조건에 따라 table2의 데이터가 table1의 데이터와 일치한다면 함께 반환한다. ( table1의 모든 데이터 및 table2에서는 조건에 맞는 데이터 반환 )

 

ex) 모든 고객의 명단과 해당 고객들의 주문번호들을 조회하고자 한다.

고객의 이름은 Customers, 주문번호는 Orders테이블에 있다. 위 문장에서 필요한 데이터를 한 레코드로 얻기 위해선 JOIN을 해야 한다. '모든'고객의 명단과 이에 따른 주문번호를 함께 나열하고자 하고 있으므로 Customers는 table1, Orders는 table2가 되어야 한다.

( W3Schools Demo Database 인용 )

SELECT Customers.CustomerName,
               Orders.OrderID
FROM Customers LEFT JOIN Orders       -> Customers(table1)에 Orders(table2)가 JOIN된다.
                                           ON Customers.CustomerID = Orders.CustomerID

FROM뒤에 Customers가 명시되어 있으므로 Customers의 모든 데이터는 반환을 한다. 이후 ON 뒤에 명시된 조건에 따라 Orders의 데이터 또한 함께 출력되게 된다.

ON 조건에 부합하지 않는 데이터를 찾고자 한다면 Orders테이블에 CustomerID가 없는 경우일 것이다. '이는 주문내역이 없다.'와 동일하므로 WHERE절을 사용하여 별도 조회를 할 수 있다.

SELECT Customers.CustomerID,
               Orders.OrderID
FROM Customers LEFT JOIN Orders
                                           ON Customers.CustomerID = Orders.CustomerID
WHERER OrderID IS NULL

 

RIGHT JOIN

( W3Schools 인용 )

 - LEFT JOIN과 거의 동일하며 FROM뒤에 명시된 테이블이 table1이었다면 JOIN뒤에 명시된 테이블이 table1이 된다.

( W3Schools Demo Database 인용 )

SELECT Customers.CustomerName,
               Orders.OrderID
FROM Customers RIGHT JOIN Orders     -> Orders테이블(table1)에 Customers테이블(table2)가 JOIN된다.
                                             ON Customers.CustomerID = Orders.CustomerID

 

CROSS JOIN

 - 두 테이블의 공통된 데이터 존재 유/무 상관 없이 Cartesian product(카르테시안 곱연산)로 레코드를 반환한다. 

( JavaTPoint.com 인용 )

 

* Categories테이블

( W3Schools Demo Database 인용 )

* Shippers테이블

( W3Schools Demo Database 인용 )

 

위 두 테이블간의 연관관계는 없다. CROSS JOIN을 통해 결과를 확인한다.

 

* Categories CROSS JOIN Shippers

( W3Schools Demo Database 인용 )

Categories테이블의 데이터는 8개, Shippers테이블의 데이터는 3개였다.

위 두개의 테이블을 CROSS JOIN한 결과 위에서 설명한 바와 같이 Cartesian product을 통해 8 x 3 = 24개의 데이터를 반환한다.

Categories테이블의 각 row마다 Shippers테이블의 데이터가 매칭되어 레코드를 반환한 것이다.

만약 위 두 테이블간의 공통Column이 존재한다면 WHERE절을 통해 INNER JOIN과 동일하게 사용이 가능하다.

● CROSS JOIN
SELECT Customers.CustomerName,
               Orders.OrderID
FROM Customers CROSS JOIN Orders
WHERE Customers.CustomerID = Orders.CustomerID

● INNER JOIN
SELECT Customers.CustomerName,
               Orders.OrderID
FROM Customers INNER JOIN Orders
                                             ON Customers.CustomerID = Orders.CustomerID

위 두 SQL은 동일한 결과를 반환한다. CROSS JOIN은 경우의 수를 체크하기 위한 경우에 보통 사용하나 빈도가 매우 낮고 사용할 경우가 거의 없다고 한다.

 

 

SELF JOIN

- 여러 테이블을 JOIN하는것이 아닌 한 테이블이 자체 JOIN한다.

 

ex) 고객명단(Customers)에서 같은 도시(City)에 살고 있는 사람들을 서로 짝지어 한 레코드로 반환받는다.

( W3Schools Demo Database 인용 )

SELECT A.CustomerName AS Name1,
               B.CustomerName AS Name2,
               A.City  -> A, B 두 테이블 모두 City Column을 가지고 있다. 어떤 테이블의 City를 반환받을지에 대해 참조연산(.)으로 명시를 하지 않으면 Ambiguous오류가 발생한다.
FROM Customers A,
            Customers B
WHERE A.CustomerID <> B.CustomerID AND
              A.City = B.city
ORDER BY City


SELECT A.CustomerName AS Name1
               B.CustomerName AS Name2
FROM Customers A INNER JOIN Customers B
WHERE A.CustomerID <> B.CustomerID AND
              A.City = B.city

WHERE절에서 사용하고 있는 '<>'는 Not Equal(!=)과 동일하다. 

Syntax는 위와 같으며 다른 JOIN들과는 다르게 JOIN을 명시하지 않는다. FROM절에 동일한 테이블을 다른 별칭으로서 나열한다.

 

개인적인 생각으로는 위에서 설명한것처럼 FROM에 2개 이상의 테이블이 명시된다면 기본적으로 INNER JOIN을 하게 된다. 나와 나 자신의 INNER JOIN을 WHERE절의 조건에 따라 실행했다고 봐도 될 것 같다.