[MySQL] Using ON 비교 및 차이

안녕하세요.

MySQL에서 JOIN 쿼리를 작성할 때 사용하는 Using과 On에 대해 알아보겠습니다.

 

 MySQL Using vs ON

▣ Using과 ON 차이

테이블간 JOIN할 때 필드(컬럼) 이름이 같을 경우에는 Using을 사용하고 다를 경우에는 ON을 사용함.

- ON은 필드(컬럼) 이름이 같은 경우에도 상관 없이 사용 가능함.

 

: 예시 데이터 members & orders

members

orders

CREATE TABLE members
    (`member_id` int, `employee_yn` varchar(1))
;
    
INSERT INTO members
    (`member_id`, `employee_yn`)
VALUES
    (299655, 'n'),
    (183825, 'n'),
    (50364, 'y'),
    (201430, 'n'),
    (152253, 'n'),
    (406049, 'n'),
    (261449, 'n'),
    (392587, 'y'),
    (397801, 'n'),
    (190581, 'y'),
    (124205, 'n'),
    (408045, 'n'),
    (229046, 'y'),
    (37524, 'n'),
    (30834, 'y'),
    (269704, 'y'),
    (328999, 'n'),
    (341556, 'n'),
    (173005, 'y'),
    (115934, 'y'),
    (1792, 'n')
;


CREATE TABLE orders
    (`order_code` int, `order_time` datetime, `pay_money` int, `pay_yn` varchar(1), `member_id` int)
;
    
INSERT INTO orders
    (`order_code`, `order_time`, `pay_money`, `pay_yn`, `member_id`)
VALUES
    (662622, '2020-05-17 04:34:49', 62400, 'n', 43581),
    (387836, '2018-04-13 10:20:57', 4500, 'y', 70955),
    (345477, '2017-12-16 03:30:43', 34200, 'y', 70955),
    (531013, '2019-05-16 19:22:28', 56300, 'y', 70955),
    (442547, '2018-09-13 04:24:26', 78900, 'y', 299655),
    (395865, '2018-05-05 20:49:58', 91150, 'y', 299655),
    (548572, '2019-07-04 20:31:21', 25500, 'y', 299655),
    (238425, '2017-02-21 02:50:18', 28150, 'y', 201430),
    (287842, '2017-07-08 20:53:55', 63800, 'y', 201430),
    (578845, '2019-09-27 10:37:28', 68000, 'y', 201430),
    (67073, '2015-11-02 22:54:46', 50000, 'y', 201430),
    (504529, '2019-03-03 16:39:44', 56300, 'n', 201430),
    (297771, '2017-08-05 17:08:15', 79900, 'n', 201430),
    (50867, '2015-09-19 00:32:24', 4500, 'n', 201430),
    (99287, '2016-01-31 19:54:34', 21800, 'y', 1792),
    (500922, '2019-02-21 22:54:11', 45600, 'y', 1792),
    (314778, '2017-09-22 03:09:28', 45000, 'n', 1792),
    (630066, '2020-02-17 05:23:51', 19900, 'y', 1792),
    (121837, '2016-04-03 08:09:06', 18600, 'n', 1792),
    (534253, '2019-05-25 22:45:57', 63700, 'y', 1792),
    (477655, '2018-12-19 00:37:37', 40790, 'y', 1792),
    (568877, '2019-08-30 13:29:52', 69250, 'y', 400390)
;

- 과거 포스팅했던 실전 코딩테스트 예제 쿼리를 USING과 ON으로 아래 작성해보았습니다.

2020.11.07 - [Programming/SQL] - [MySQL] 코딩테스트 연습 예제 2

 

: Using 활용

select a.member_id, b.first_time, b.last_time, b.sum_pay, b.order_cnt
from (select * from members where employee_yn = 'n') a
join (select member_id, min(order_time) first_time, max(order_time) last_time, 
      count(member_id) order_cnt, sum(pay_money) sum_pay
      from orders
      where pay_yn = 'y'
      group by member_id ) b
Using(member_id)

 

: ON 활용

select a.member_id, b.first_time, b.last_time, b.sum_pay, b.order_cnt
from (select * from members where employee_yn = 'n') a
join (select member_id, min(order_time) first_time, max(order_time) last_time, 
      count(member_id) order_cnt, sum(pay_money) sum_pay
      from orders
      where pay_yn = 'y'
      group by member_id ) b
on a.member_id = b.member_id

 

: 출력 결과

- JOIN시 Using과 ON의 결과가 동일함.