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

테이블 두개를 조인하여 비교한 후 빈칸 포함 나타내 주는 쿼리 질문입니다.

by 팔오 [2013.08.06 08:38:34]

12345.jpg (42,132Bytes)

Table 두개가 있습니다.
두 개의 테이블에는 같은 값들이 들어가 있으나
한 테이블에만 있는 값이 있습니다.

저는 이러한 값들을 모두 Select해서 화면에 나타내 주고 싶습니다.

화면에는 두 테이블의 값들을 각각 뿌려 주고
두 테이블에 모두 있는 값들은 모두 나타내어 주고
한 테이블에만 있는 값은 있는 테이블 값에만 나타내어 주고 없는 테이블 값에는 빈칸으로 화면에 나타내고 싶습니다.

Row_number () over partition ... 이걸 사용해야 할까요?
아니면 rownum을 이용해서 해야 하는걸까요?

여러가지 방법을 해 보다가 안되서 이렇게 도움을 요청합니다 ㅠㅠ

제 설명이 애매해서 파일을 첨부하여 올렸습니다.

by 신이만든짝퉁 [2013.08.06 08:42:51]

full outer join 을 사용해보세요~

by 신이만든짝퉁 [2013.08.06 09:14:23]

with a as (
select 'a' name from dual union all
select 'b' from dual union all
select 'c' from dual union all
select 'e' from dual union all
select 'f' from dual union all
select 'g' from dual union all
select 'h' from dual
),
b as (
select 'a' name from dual union all
select 'b' from dual union all
select 'c' from dual union all
select 'd' from dual union all
select 'e' from dual union all
select 'g' from dual union all
select 'i' from dual
)
select a.name name1
   , b.name name2
 from a
   , b
   , (select * from a union select * from b) c
 where a.name(+)=c.name
  and c.name=b.name(+) 
order by c.name  
;

이렇게 한번 해보세요~

by 팔오 [2013.08.06 09:31:06]

좋은 답변 감사합니다~

by 미카엘 [2013.08.06 09:35:50]

with t as (
select 'A' form_name from dual union all
select 'B' form_name from dual union all
select 'C' form_name from dual union all
select 'E' form_name from dual union all
select 'F' form_name from dual union all
select 'G' form_name from dual union all
select 'H' form_name from dual),
t2 as (
select 'A' form_name from dual union all
select 'B' form_name from dual union all
select 'C' form_name from dual union all
select 'D' form_name from dual union all
select 'E' form_name from dual union all
select 'G' form_name from dual union all
select 'I' form_name from dual)
select c.form_name,
d.form_name
from t c full outer join t2 d
on c.form_name = d.form_name
order by nvl(d.form_name, c.form_name)

by 팔오 [2013.08.06 09:46:32]

답변 감사합니다~

by 팔오 [2013.08.06 09:46:57]

WITH TEST AS
(
SELECT 'A' form_name, 0 application_id, 0 form_id FROM DUAL UNION ALL
SELECT 'B' form_name, 0 application_id, 1 form_id FROM DUAL UNION ALL
SELECT 'C' form_name, 1 application_id, 2 form_id FROM DUAL UNION ALL
SELECT 'E' form_name, 3 application_id, 4 form_id FROM DUAL UNION ALL
SELECT 'F' form_name, 3 application_id, 5 form_id FROM DUAL UNION ALL
SELECT 'G' form_name, 4 application_id, 6 form_id FROM DUAL UNION ALL
SELECT 'H' form_name, 4 application_id, 7 form_id FROM DUAL
), TEST1 AS
(
SELECT 'A' form_name, 0 application_id, 0 form_id FROM DUAL UNION ALL
SELECT 'B' form_name, 0 application_id, 1 form_id FROM DUAL UNION ALL
SELECT 'C' form_name, 1 application_id, 2 form_id FROM DUAL UNION ALL
SELECT 'D' form_name, 2 application_id, 3 form_id FROM DUAL UNION ALL
SELECT 'E' form_name, 3 application_id, 4 form_id FROM DUAL UNION ALL
SELECT 'G' form_name, 4 application_id, 6 form_id FROM DUAL UNION ALL
SELECT 'I' form_name, 4 application_id, 8 form_id FROM DUAL
)
SELECT C.FORM_NAME
, DECODE(SUM(C.FORM_GUBUN1),1,C.FORM_NAME,NULL)
, DECODE(SUM(C.FORM_GUBUN2),1,C.FORM_NAME,NULL)
FROM (
    SELECT A.FORM_NAME FORM_NAME
, 1    FORM_GUBUN1
, 0    FORM_GUBUN2
FROM TEST A
UNION ALL
    SELECT B.FORM_NAME FORM_NAME
, 0    FORM_GUBUN1
, 1    FORM_GUBUN2
FROM TEST1 B) C
GROUP BY C.FORM_NAME
ORDER BY C.FORM_NAME

;

저는 이런식으로 해결했습니다~

by 마농 [2013.08.06 10:51:19]

SELECT c.form_name
     , MIN(form_name_a) form_name_a
     , MIN(form_name_b) form_name_b
  FROM (SELECT form_name
             , form_name form_name_a
             , ''        form_name_b
          FROM test a
         UNION ALL
        SELECT form_name
             , ''        form_name_a
             , form_name form_name_b
          FROM test1 b
        ) c
 GROUP BY c.form_name
 ORDER BY c.form_name
;

by 팔오 [2013.08.06 14:51:10]

좀 더 쿼리를 줄이는 방법이 있군요~
감사합니다~

댓글등록

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

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

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

데이터베이스 질문과 답변