[MySQL] JOIN 절 예제 ②

---

JOIN 절 예제 ②

 

① INNER JOIN

한 테이블의 각 행과 다른 테이블의 모든 행을 일치시키며 두 테이블의 열이 포함된 행을 조회

 

예제 1)

# 제품코드, 제품명, 제품라인설명 조회
SELECT
	p.productCode
	, p.productName
	, pl.textDescription
FROM 
	products p 
JOIN 
	productlines pl USING (productLine); 
	# ON p.productLine = pl.productLine 과 같음

 

예제 2)

# 주문번호, 주문상태, 주문번호별 총매출액 조회
SELECT
	o.orderNumber
	, o.status
	, sum(od.quantityOrdered * od.priceEach) "총매출액"
FROM
	orders o 
JOIN 
	orderdetails od USING (orderNumber)
GROUP BY
	orderNumber;

 

예제 3)

# 주문번호, 주문일, 주문라인번호, 제품명, 주문개수, 개당가격 조회
SELECT
	o.orderNumber
	, o.orderDate
	, od.orderLineNumber
	, p.productName
	, od.quantityOrdered
	, od.priceEach
FROM 
	orders o 
JOIN 
	orderdetails od USING (orderNumber)
JOIN
	products p USING (productCode)
ORDER BY
	o.orderNumber, od.orderLineNumber;

 

예제 4)

# 주문번호, 주문일, 고객명, 주문라인번호, 제품명, 주문개수, 개당가격 조회
SELECT
	o.orderNumber
	, o.orderDate
	, c.customerName
	, od.orderLineNumber
	, p.productName
	, od.quantityOrdered
	, od.priceEach
FROM 
	orders o
JOIN
	orderdetails od USING (orderNumber)
JOIN
	products p USING (productCode)
JOIN
	customers c USING (customerNumber)
ORDER BY
	o.orderNumber, od.orderLineNumber;

 

예제 5)

# 코드가 'S10_1678'인 제품의 판매가격이 권장소비자가보다 낮은 행들 조인
SELECT
	od.orderNumber
	, p.productName
	, p.msrp 
	, od.priceEach
FROM
	products p
JOIN
	orderdetails od
	ON p.productCode = od.productCode
	AND p.msrp > od.priceEach
WHERE
	p.productCode = 'S10_1678';

 

② LEFT JOIN

예제 1)

# 주문이 없는 고객을 포함하여 모든 고객 반환
SELECT
	c.customerNumber
	, c.customerName
	, o.orderNumber
	, o.orderDate
	, o.status
FROM
	customers c
LEFT JOIN
	orders o USING (customerNumber);
    
# 고객이 주문이 없는 경우 orderNumber, orderDate, status 열의 값은 NULL로 채움!

customerNumber|customerName                      |orderNumber|orderDate |status    |
--------------+----------------------------------+-----------+----------+----------+
           167|Herkku Gifts                      |      10188|2003-11-18|Shipped   |
           167|Herkku Gifts                      |      10289|2004-09-03|Shipped   |
           168|American Souvenirs Inc            |       NULL|NULL      |NULL      |
           169|Porto Imports Co.                 |       NULL|NULL      |NULL      |
           171|Daedalus Designs Imports          |      10180|2003-11-11|Shipped   |
           171|Daedalus Designs Imports          |      10224|2004-02-21|Shipped   |
           172|La Corne D'abondance, Co.         |      10114|2003-04-01|Shipped   |

 

예제 2)

# 주문이 없는 고객 조회
SELECT
	c.customerNumber
	, c.customerName
	, o.orderNumber
	, o.orderDate
	, o.status
FROM
	customers c
LEFT JOIN
	orders o USING (customerNumber)
WHERE
	o.orderNumber IS NULL;	

# 주문이 없으면 NULL 값을 채우기 때문!

 

예제 3)

SELECT
	e.lastName
	, e.firstName
	, c.customerName
	, p.checkNumber
	, p.amount
FROM 
	employees e	
LEFT JOIN
	customers c 
	ON e.employeeNumber = c.salesRepEmployeeNumber	# 1
LEFT JOIN
	payments p 
	ON p.customerNumber = c.customerNumber	# 2
ORDER BY
	c.customerName, p.checkNumber;
    
# 1 → 모든 직원 정보와 직원이 담당하는 고객 정보를 가져옴 (직원이 고객을 담당하지 않으면 NULL 반환)
# 2 → 고객의 지불액을 가져옴 (고객이 지불금을 내지 않았으면 NULL 반환)

lastName |firstName|customerName                      |checkNumber|amount   |
---------+---------+----------------------------------+-----------+---------+
Murphy   |Diane    |NULL                              |NULL       |     NULL|
Patterson|Mary     |NULL                              |NULL       |     NULL|
Firrelli |Jeff     |NULL                              |NULL       |     NULL|
Patterson|William  |NULL                              |NULL       |     NULL|
Bondur   |Gerard   |NULL                              |NULL       |     NULL|
Bow      |Anthony  |NULL                              |NULL       |     NULL|
King     |Tom      |NULL                              |NULL       |     NULL|
Kato     |Yoshimi  |NULL                              |NULL       |     NULL|
Hernandez|Gerard   |Alpha Cognac                      |AF40894    | 33818.34|
Hernandez|Gerard   |Alpha Cognac                      |HR224331   | 12432.32|
Hernandez|Gerard   |Alpha Cognac                      |KI744716   | 14232.70|
Tseng    |Foon Yue |American Souvenirs Inc            |NULL       |     NULL|
Castillo |Pamela   |Amica Models & Co.                |IJ399820   | 33924.24|
Castillo |Pamela   |Amica Models & Co.                |NE404084   | 48298.99|

 

예제 4)

# 주문 번호 10123의 주문 및 주문상세 정보만 반환
SELECT 
	o.orderNumber
	, o.customerNumber
	, od.productCode
FROM 
	orders o 
LEFT JOIN 
	orderdetails od USING (orderNumber)
WHERE
	orderNumber = 10123;

orderNumber|customerNumber|productCode|
-----------+--------------+-----------+
      10123|           103|S18_1589   |
      10123|           103|S18_2870   |
      10123|           103|S18_3685   |
      10123|           103|S24_1628   |

 

# 모든 주문을 반환하지만 10123 주문에만 주문상세 정보 연결
SELECT 
	o.orderNumber
	, o.customerNumber
	, od.productCode
FROM 
	orders o 
LEFT JOIN 
	orderdetails od 
	ON o.orderNumber = od.orderNumber
	AND o.orderNumber = 10123;

orderNumber|customerNumber|productCode|
-----------+--------------+-----------+
      10123|           103|S18_1589   |
      10123|           103|S18_2870   |
      10123|           103|S18_3685   |
      10123|           103|S24_1628   |
      10298|           103|NULL       |
      10345|           103|NULL       |
      10124|           112|NULL       |
      10278|           112|NULL       |

 

③ RIGHT JOIN

예제 1)

SELECT
	e.employeeNumber
	, c.customerNumber
FROM 
	customers c
RIGHT JOIN 
	employees e
	ON c.salesRepEmployeeNumber = e.employeeNumber
ORDER BY	
	e.employeeNumber;
    
# 고객 테이블의 salesRepEmployeeNumber 컬럼과 일치 여부 상관없이 '직원 테이블의 모든 행을 반환'
# 고객 테이블의 salesRepEmployeeNumber 컬럼과 일치하는 행이 없으면 '고객 테이블의 모든 컬럼은 NULL 반환'

employeeNumber|customerNumber|
--------------+--------------+
          1002|          NULL|
          1056|          NULL|
          1076|          NULL|
          1088|          NULL|
          1102|          NULL|
          1143|          NULL|
          1165|           124|
          1165|           129|
          1165|           161|

 

예제 2)

# 고객을 담당하지 않는 직원들 조회
SELECT
	e.employeeNumber
	, c.customerNumber
FROM 
	customers c
RIGHT JOIN 
	employees e
	ON c.salesRepEmployeeNumber = e.employeeNumber
WHERE
	c.customerNumber IS NULL
ORDER BY	
	e.employeeNumber;
    
# 고객테이블의 담당자 컬럼과 일치하지 않으면 고객 테이블의 값들을 NULL로 채우기 때문!

employeeNumber|customerNumber|
--------------+--------------+
          1002|          NULL|
          1056|          NULL|
          1076|          NULL|
          1088|          NULL|
          1102|          NULL|
          1143|          NULL|
          1619|          NULL|
          1625|          NULL|

 

④ SELF JOIN

예제 1)

SELECT
	e.employeeNumber
	, concat(e.lastName, ' ', e.firstName) "manager"
	, e2.employeeNumber
	, concat(e2.lastName, ' ', e2.firstName) "reporter"
FROM
	employees e
JOIN 
	employees e2
	ON e.employeeNumber = e2.reportsTo
ORDER BY 
	e.employeeNumber;

# 직원 테이블은 직원 정보와 조직구조 정보도 가지고 있음
# reportsTo 컬럼은 직원의 관리자를 의미함

employeeNumber|manager          |employeeNumber|reporter         |
--------------+-----------------+--------------+-----------------+
          1002|Murphy Diane     |          1056|Patterson Mary   |
          1002|Murphy Diane     |          1076|Firrelli Jeff    |
          1056|Patterson Mary   |          1088|Patterson William|
          1056|Patterson Mary   |          1102|Bondur Gerard    |
          1056|Patterson Mary   |          1143|Bow Anthony      |
          1056|Patterson Mary   |          1621|Nishi Mami       |
          1088|Patterson William|          1611|Fixter Andy      |
          1088|Patterson William|          1612|Marsh Peter      |
          1088|Patterson William|          1619|King Tom         |
          1102|Bondur Gerard    |          1337|Bondur Loui      |
          1102|Bondur Gerard    |          1370|Hernandez Gerard |
          1102|Bondur Gerard    |          1401|Castillo Pamela  |