[sql] MySQL에서 전체 외부 가입을 수행하는 방법은 무엇입니까?

MySQL에서 완전 외부 조인을하고 싶습니다. 이것이 가능한가? MySQL은 Full Outer Join을 지원합니까?

답변

MySQL에는 FULL JOIN이 없지만 에뮬레이션 할 수 있습니다 .

이 SO 질문 에서 코드 샘플을 작성 하면 다음이 있습니다.

두 개의 테이블 t1, t2로 :

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

위 쿼리는 FULL OUTER JOIN 연산이 중복 행을 생성하지 않는 특수한 경우에 작동합니다. 위 쿼리는 UNIONset 연산자에 따라 쿼리 패턴에 의해 도입 된 중복 행을 제거합니다. 두 번째 쿼리에 반 조인 패턴을 사용하여 중복 행이 도입되는 것을 피한 다음 UNION ALL 집합 연산자를 사용하여 두 집합을 결합 할 수 있습니다. FULL OUTER JOIN이 중복 행을 리턴하는보다 일반적인 경우에는 다음을 수행 할 수 있습니다.

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL

답변

Pablo Santa Cruz 가 한 대답 은 정확합니다. 그러나 누군가이 페이지를 우연히 발견하고 더 자세한 설명을 원할 경우 여기에 자세한 설명이 있습니다.

테이블 예

다음 테이블이 있다고 가정하십시오.

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

내부 조인

내부 조인은 다음과 같습니다.

SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

다음과 같이 두 테이블에 모두 나타나는 레코드 만 가져 오십시오.

1 Tim  1 Tim

내부 조인은 명시 적으로 양방향이기 때문에 방향이 없습니다 (왼쪽 또는 오른쪽). 양쪽에 일치해야합니다.

외부 조인

반면 외부 조인은 다른 테이블에서 일치하지 않는 레코드를 찾기위한 것입니다. 따라서, 조인의 어느 쪽 이 누락 된 레코드를 가질 수 있는지 지정해야합니다.

LEFT JOIN과 RIGHT JOIN에 대한 나타내는 표현이된다 LEFT OUTER JOIN및 RIGHT OUTER JOIN; 아래의 전체 이름을 사용하여 외부 조인과 내부 조인의 개념을 강화합니다.

왼쪽 외부 조인

왼쪽 외부 조인은 다음과 같습니다.

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

… 다음과 같이 오른쪽 테이블에서 일치하는지 여부에 관계없이 왼쪽 테이블에서 모든 레코드를 가져옵니다.

1 Tim   1    Tim
2 Marta NULL NULL

오른쪽 외부 조인

다음과 같은 오른쪽 외부 조인

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

… 왼쪽 테이블과 일치하는지 여부에 관계없이 오른쪽 테이블에서 모든 레코드를 가져올 수 있습니다.

1    Tim   1  Tim
NULL NULL  3  Katarina

완전 외부 조인

완전 외부 조인은 다른 테이블과 일치하는지 여부에 관계없이 두 테이블의 모든 레코드를 제공하며 일치하지 않는 양쪽에 NULL이 있습니다. 결과는 다음과 같습니다.

1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina

그러나 Pablo Santa Cruz가 지적했듯이 MySQL은 이것을 지원하지 않습니다. 다음과 같이 왼쪽 조인과 오른쪽 조인의 UNION을 수행하여 에뮬레이션 할 수 있습니다.

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

UNION“이 두 쿼리를 모두 실행 한 다음 결과를 서로 쌓아 올리십시오”라는 의미로 생각할 수 있습니다 . 일부 행은 첫 번째 쿼리에서 나오고 일부는 두 번째 쿼리에서 나옵니다.

UNIONMySQL에서 a 는 정확한 중복을 제거합니다. Tim은 두 쿼리 모두에 표시되지만 결과 UNION는 그를 한 번만 나열합니다. 저의 데이터베이스 전문가는이 동작에 의존해서는 안된다고 생각합니다. 따라서 더 명확하게 WHERE하기 위해 두 번째 쿼리에 절을 추가 할 수 있습니다 .

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;

당신이 다른 한편, 만약 싶어 어떤 이유로 중복을보고, 당신은 사용할 수 있습니다 UNION ALL.

답변

union쿼리를 사용하면 중복이 full outer join제거되며 중복을 제거하지 않는 동작과 다릅니다 .

[Table: t1]                            [Table: t2]
value                                  value
-------                                -------
1                                      1
2                                      2
4                                      2
4                                      5

이것은 다음의 예상 결과입니다 full outer join.

value | value
------+-------
1     | 1
2     | 2
2     | 2
Null  | 5
4     | Null
4     | Null

이는 사용의 결과 left와 right Join와 union:

value | value
------+-------
Null  | 5
1     | 1
2     | 2
4     | Null

[SQL Fiddle]

내 제안 쿼리는 다음과 같습니다

select
    t1.value, t2.value
from t1
left outer join t2
  on t1.value = t2.value
union all      -- Using `union all` instead of `union`
select
    t1.value, t2.value
from t2
left outer join t1
  on t1.value = t2.value
where
    t1.value IS NULL

예상 결과와 동일한 위 쿼리 결과 :

value | value
------+-------
1     | 1
2     | 2
2     | 2
4     | NULL
4     | NULL
NULL  | 5

[SQL Fiddle]

@Steve Chambers : ^{[의견에서 감사합니다!]}
참고 : 이는 효율성과 같은 결과를 생성하는 데 가장 적합한 솔루션 일 수 있습니다 FULL OUTER JOIN. 이 블로그 게시물 은 또한 방법 2에서 인용 한 내용을 잘 설명합니다. “이것은 중복 행을 올바르게 처리하고 행하지 말아야 할 것은 포함하지 않습니다. UNION ALL평범한 대신 사용해야 UNION하므로 유지하려는 중복을 제거합니다. 중복을 정렬하거나 제거 할 필요가 없으므로 큰 결과 집합에서 훨씬 더 효율적일 수 있습니다. “

full outer join시각화 및 수학 에서 제공되는 다른 솔루션을 추가하기로 결정 했지만 위의 방법이 아니라 더 읽기 쉽습니다.

완전 외부 조인은 (t1 ∪ t2)모두 포함 t1또는 포함 t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: 모두 포함 되지 않은 모두 t1및 t2포함 t1되지 않은 t2모든 t2것을 의미합니다 t1.

-- (t1 ∩ t2): all in both t1 and t2
select t1.value, t2.value
from t1 join t2 on t1.value = t2.value
union all  -- And plus 
-- all in t1 that not exists in t2
select t1.value, null
from t1
where not exists( select 1 from t2 where t2.value = t1.value)
union all  -- and plus
-- all in t2 that not exists in t1
select null, t2.value
from t2
where not exists( select 1 from t1 where t2.value = t1.value)

[SQL Fiddle]

답변

MySql에는 FULL-OUTER-JOIN 구문이 없습니다. 다음과 같이 LEFT JOIN과 RIGHT JOIN을 모두 수행하여 에뮬레이션해야합니다.

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

그러나 MySql에는 RIGHT JOIN 구문이 없습니다. MySql의 외부 조인 단순화 에 따르면 오른쪽 조인은 쿼리 의 FROMand ON절에서 t1과 t2를 전환하여 동등한 왼쪽 조인으로 변환 됩니다. 따라서 MySql Query Optimizer는 원래 쿼리를 다음과 같이 변환합니다.

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id

이제 같이 원래 쿼리를 작성에 전혀 해를 끼치 지 당신이있는 WHERE 절과 같은 조건이있는 경우 말하지만, 없다 에 가입하기 전에 – 온 조건 또는 AND 조건 ONA는 절을, 가입시 – 술어 다음, 당신을 악마를보고 싶을 수도 있습니다. 자세한 내용입니다.

MySql 쿼리 옵티마이 저는 술어가 널 거부 인지 여부를 정기적으로 점검합니다 .
이제 RIGHT JOIN을 수행했지만 t1의 열에 WHERE 술어가 있으면 널 거부 시나리오 가 발생할 위험이 있습니다.

예를 들어, 다음 쿼리는-

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'

Query Optimizer가 다음으로 번역됩니다.

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.col1 = 'someValue'
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id
WHERE t1.col1 = 'someValue'

따라서 테이블 순서가 변경되었지만 술어는 여전히 t1에 적용되지만 t1은 이제 ‘ON’절에 있습니다. t1.col1이 NOT NULL
열로 정의되면 이 쿼리는 널이 거부 됩니다.

null 거부 된 모든 외부 조인 (왼쪽, 오른쪽, 전체)은 MySql에 의해 내부 조인으로 변환됩니다.

따라서 예상 한 결과는 MySql이 반환하는 결과와 완전히 다를 수 있습니다. MySql의 RIGHT JOIN에 버그가 있다고 생각할 수도 있지만 그렇지 않습니다. MySql 쿼리 최적화 프로그램이 작동하는 방식입니다. 따라서 담당자는 쿼리를 구성 할 때 이러한 뉘앙스에주의를 기울여야합니다.

답변

SQLite에서는 다음을 수행해야합니다.

SELECT *
FROM leftTable lt
LEFT JOIN rightTable rt ON lt.id = rt.lrid
UNION
SELECT lt.*, rl.*  -- To match column set
FROM rightTable rt
LEFT JOIN  leftTable lt ON lt.id = rt.lrid

답변

위의 답변 중 어느 것도 중복 된 값이있을 때 의미를 따르지 않기 때문에 실제로 올바른 것은 없습니다.

(이 복제본 에서와 같은) 쿼리의 경우 :

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;

올바른 내용은 다음과 같습니다.

SELECT t1.*, t2.*
FROM (SELECT name FROM t1 UNION  -- This is intentionally UNION to remove duplicates
      SELECT name FROM t2
     ) n LEFT JOIN
     t1
     ON t1.name = n.name LEFT JOIN
     t2
     ON t2.name = n.name;

이 NULL값 으로 작업해야하는 경우 (필요할 수도 있음) 대신 NULL-safe 비교 연산자 를 사용하십시오 .<=>=

답변

명확성을 위해 shA.t의 쿼리를 수정했습니다.

-- t1 left join t2
SELECT t1.value, t2.value
FROM t1 LEFT JOIN t2 ON t1.value = t2.value

    UNION ALL -- include duplicates

-- t1 right exclude join t2 (records found only in t2)
SELECT t1.value, t2.value
FROM t1 RIGHT JOIN t2 ON t1.value = t2.value
WHERE t2.value IS NULL