꿈꾸는 개발자, DBA 커뮤니티 구루비

두 개의 테이블 합치기

by Gusto [SQLServer] mssql [2015.10.19 10:47:21]

두 개의 테이블을 합쳐서 하나의 테이블로 조회하려고 합니다.

자료는 다음과 같은 형태로 들어가있습니다.

1. 각 테이블의 ID, VISIT 값은 같거나 다를 수 있다.

2. 테이블에서 동일한 컬럼명을 가진 변수는 ID와 VISIT이며, 그 외의 컬럼명은 테이블 자료 특성에 따라 다르다.

아래 예제 테이블을 하나로 합치려고 합니다. JOIN으로 할 경우 조건절을 잘못했는데 ID 또는 VISIT이 누락되거나 하는 문제가 발생하네요... 어떤 방식으로 접근해야 할까요?

WITH tbl1(id, visit, vars_1, vars_2) as
(
	SELECT 'test_1', 'D1', '1_vars_1_1', '1_vars_2_1' UNION ALL
	SELECT 'test_1', 'D2-1', '1_vars_1_2_1', '1_vars_2_2_1' UNION ALL
	SELECT 'test_1', 'D8', '1_vars_1_8', '1_vars_2_8' UNION ALL
	SELECT 'test_2', 'D1', '2_vars_1_1', '2_vars_2_1' UNION ALL
	SELECT 'test_2', 'D2-1', '2_vars_1_2_1', '2_vars_2_2_1' UNION ALL
	SELECT 'test_2', 'D8', '2_vars_1_8', '2_vars_2_8' UNION ALL
	SELECT 'test_4', 'D1', '4_vars_1_1', '4_vars_2_1' UNION ALL
	SELECT 'test_4', 'D2-1', '4_vars_1_2_1', '4_vars_2_2_1' UNION ALL
	SELECT 'test_4', 'D8', '4_vars_1_8', '4_vars_2_8'
), tbl2(id, visit, vars_3, vars_4) as 
(
	SELECT 'test_1', 'D1', '1_vars_3_1', '1_vars_4_1' UNION ALL
	SELECT 'test_1', 'D2-1', '1_vars_3_2_1', '1_vars_4_2_1' UNION ALL
	SELECT 'test_1', 'D3', '1_vars_3_3', '1_vars_4_3' UNION ALL
	SELECT 'test_3', 'D1', '3_vars_3_1', '3_vars_4_1' UNION ALL
	SELECT 'test_3', 'D2-1', '3_vars_3_2_1', '3_vars_4_2_1' UNION ALL
	SELECT 'test_3', 'D8', '3_vars_3_8', '3_vars_4_8'
)

by 우리집아찌 [2015.10.19 10:55:52]

full outer join 말씀하시는건가요??

select 구문도올려주세요

by Gusto [2015.10.19 11:01:31]

SELECT *
FROM tbl1
FULL OUTER JOIN tbl2
ON tbl1.id = tbl2.id

다음과 같이 FULL OUTER JOIN을 하면 id와 visit 컬럼이 두 개씩 출력되어 ID, VISIT, vars_1, vars_2, ID, VISIT, vars_3, vars_4 이렇게 나오는데 이 부분을 ID, VISIT, vars_1, vars_2, vars_3, vars_4 로 나오도록 하려고 합니다.

by Gusto [2015.10.19 11:15:54]

몇 가지 조인 방법을 써보기도 해봤는데, 제가 SQL에 대해서 아직 정확한 이해가 없는건지 난감하네요.

SELECT * 
FROM 
	( 
		SELECT id
		FROM tbl1
		WHERE id is not null
			UNION 
		SELECT id
		FROM tbl2
		WHERE id is not null
	) as main
,	(
		SELECT visit
		FROM tbl1
		WHERE visit is not null
			UNION
		SELECT visit
		FROM tbl2 
		WHERE visit is not null
	) as sub
ORDER BY main.id, sub.visit

by 창조의날개 [2015.10.19 11:25:29]



SELECT NVL(tbl1.ID, tbl2.ID) ID
     , NVL(tbl1.VISIT, tbl2.VISIT) VISIT
     , vars_1, vars_2, vars_3, vars_4
FROM tbl1
FULL OUTER JOIN tbl2
ON tbl1.id = tbl2.id
AND tbl1.VISIT = tbl2.VISIT

by Gusto [2015.10.19 11:38:14]

한 ID에서 VISIT이 중복되어 출력되네요...

by 창조의날개 [2015.10.19 13:44:19]

WHERE 조건에 VISIT를 하나더 추가 해야 하네요..

by Gusto [2015.10.19 13:46:16]

제가 너무 성의없이 코드를 봤네요. 조건절을 한 번만 확인했으면 해결했었을텐데... 번거롭게 해드려 죄송합니다. 답변 고맙습니다!

by Gusto [2015.10.19 11:34:36]

아.. 너무 빠르게 선택했네요.

창조의 날개님 코드를 MSSQL 형식으로 바꾸어서 해보면 ID : test_1 에서 동일한 Visit이 여러개 나오네요.

되게 신기하게 해결되는 것 같으면서 살짝 안 맞네요...

by atumlee [2015.10.19 12:15:01]

WITH
    W_TBL1(ID, VISIT, VARS_1, VARS_2) AS
        (
        SELECT 'test_1', 'D1', '1_vars_1_1', '1_vars_2_1'
        UNION ALL SELECT 'test_1', 'D2-1', '1_vars_1_2_1', '1_vars_2_2_1'
        UNION ALL SELECT 'test_1', 'D8', '1_vars_1_8', '1_vars_2_8'
        UNION ALL SELECT 'test_2', 'D1', '2_vars_1_1', '2_vars_2_1'
        UNION ALL SELECT 'test_2', 'D2-1', '2_vars_1_2_1', '2_vars_2_2_1'
        UNION ALL SELECT 'test_2', 'D8', '2_vars_1_8', '2_vars_2_8'
        UNION ALL SELECT 'test_4', 'D1', '4_vars_1_1', '4_vars_2_1'
        UNION ALL SELECT 'test_4', 'D2-1', '4_vars_1_2_1', '4_vars_2_2_1'
        UNION ALL SELECT 'test_4', 'D8', '4_vars_1_8', '4_vars_2_8'
        )
  , W_TBL2(ID, VISIT, VARS_3, VARS_4) AS
        (
        SELECT 'test_1', 'D1', '1_vars_3_1', '1_vars_4_1'
        UNION ALL SELECT 'test_1', 'D2-1', '1_vars_3_2_1', '1_vars_4_2_1'
        UNION ALL SELECT 'test_1', 'D3', '1_vars_3_3', '1_vars_4_3'
        UNION ALL SELECT 'test_3', 'D1', '3_vars_3_1', '3_vars_4_1'
        UNION ALL SELECT 'test_3', 'D2-1', '3_vars_3_2_1', '3_vars_4_2_1'
        UNION ALL SELECT 'test_3', 'D8', '3_vars_3_8', '3_vars_4_8'
        )
SELECT  ISNULL(FA.ID,FB.ID) AS ID, ISNULL(FA.VISIT,FB.VISIT) AS VISIT
      , FA.VARS_1, FA.VARS_2, FB.VARS_3, FB.VARS_4
FROM    W_TBL1 FA
        FULL OUTER JOIN W_TBL2 FB
            ON      FB.ID       = FA.ID
            AND     FB.VISIT    = FA.VISIT
ORDER BY 1, 2

by Gusto [2015.10.19 13:16:37]

답변 고맙습니다!

by Gusto [2015.10.19 13:43:09]

이미 원하는 결과물이 출력되기는 하지만 위에 제가 올려둔 코드로 짜려면 어떤 조건이 추가되어야 할까요? 혹시 답변 가능하시면 부탁드립니다. 애초에 접근이 잘못되었을까요?

SELECT * 
FROM 
	( 
		SELECT id
		FROM tbl1
		WHERE id is not null
			UNION 
		SELECT id
		FROM tbl2
		WHERE id is not null
	) as main
,	(
		SELECT visit
		FROM tbl1
		WHERE visit is not null
			UNION
		SELECT visit
		FROM tbl2 
		WHERE visit is not null
	) as sub
ORDER BY main.id, sub.visit

by 창조의날개 [2015.10.19 13:58:02]

UNION으로 합치려면 아래처럼 하시면 됩니다.

ANSI로 만들었으니 MSSQL에서도 돌아가겠네요..

그래도 UNION보다는 위에 것이 성능상 좋습니다..


SELECT *
FROM (
      SELECT tbl1.id, tbl1.visit, vars_1, vars_2, vars_3, vars_4 
      FROM tbl1 LEFT OUTER JOIN tbl2 ON tbl1.id=tbl2.id and tbl1.visit = tbl2.visit
      UNION
      SELECT tbl2.id, tbl2.visit, vars_1, vars_2, vars_3, vars_4 
      FROM tbl1 RIGHT OUTER JOIN tbl2 ON tbl1.id=tbl2.id and tbl1.visit = tbl2.visit
)
ORDER BY id, visit
;

by 창조의날개 [2015.10.19 14:01:58]

UNION ALL로 한다면 아래처럼


SELECT *
FROM (
      SELECT tbl1.id, tbl1.visit, vars_1, vars_2, vars_3, vars_4 
      FROM tbl1 JOIN tbl2 ON tbl1.id=tbl2.id and tbl1.visit = tbl2.visit
      UNION ALL
      SELECT tbl1.id, tbl1.visit, vars_1, vars_2, vars_3, vars_4 
      FROM tbl1 LEFT OUTER JOIN tbl2 ON tbl1.id=tbl2.id and tbl1.visit = tbl2.visit
      WHERE tbl2.id IS NULL
      UNION ALL
      SELECT tbl2.id, tbl2.visit, vars_1, vars_2, vars_3, vars_4 
      FROM tbl1 RIGHT OUTER JOIN tbl2 ON tbl1.id=tbl2.id and tbl1.visit = tbl2.visit
      WHERE tbl1.id IS NULL
)
ORDER BY id, visit
;

by Gusto [2015.10.19 16:01:59]

답변 고맙습니다. 좀 더 공부해야겠네요ㅠㅠ

by 마농 [2015.10.19 16:46:22]

SELECT a.id, a.visit
     , a.vars_1, a.vars_2
     , b.vars_3, b.vars_4
  FROM tbl1 a
  LEFT OUTER JOIN tbl2 b
    ON a.id    = b.id
   AND a.visit = b.visit
 UNION ALL
SELECT b.id, b.visit
     , a.vars_1, a.vars_2
     , b.vars_3, b.vars_4
  FROM tbl1 a
 RIGHT OUTER JOIN tbl2 b
    ON a.id    = b.id
   AND a.visit = b.visit
 WHERE a.id IS NULL
 ORDER BY id, visit
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변