MySQL에서 FULL OUTER JOIN을 수행하는 방법은 무엇입니까?

MySQL에서 FULL OUTER JOIN을 수행하는 방법은 무엇입니까?

---

MySQL에서 전체 외부 조인을 수행하고 싶습니다. 이것이 가능한가? MySQL에서 완전 외부 조인을 지원합니까?

---

MySQL에는 FULL JOINS가 없지만 에뮬레이트 할 수 있습니다 .

이 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 연산이 중복 행을 생성하지 않는 특수한 경우에 작동합니다. 위의 쿼리 UNION는 쿼리 패턴에 의해 도입 된 중복 행을 제거하기 위해 set 연산자 에 의존합니다 . 두 번째 쿼리에 대해 조인 방지 패턴을 사용하여 중복 행이 발생하지 않도록 한 다음 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`;

a 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 쿼리 최적화 프로그램은 원래 쿼리를 다음과 같이 변환합니다.

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 쿼리 최적화 프로그램은 술어가 null-rejected 인 경우 정기적으로 확인합니다 . 이제 RIGHT JOIN을 수행했지만 t1의 열에 WHERE 조건자를 사용하면 null 거부 시나리오 가 발생할 위험이 있습니다.

예를 들어, 다음 쿼리-

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-rejected 됩니다.

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 

---

다음을 수행 할 수 있습니다.

(SELECT 
    *
FROM
    table1 t1
        LEFT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t2.id IS NULL)
UNION ALL
 (SELECT 
    *
FROM
    table1 t1
        RIGHT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t1.id IS NULL);

---

SELECT
    a.name,
    b.title
FROM
    author AS a
LEFT JOIN
    book AS b
    ON a.id = b.author_id
UNION
SELECT
    a.name,
    b.title
FROM
    author AS a
RIGHT JOIN
    book AS b
    ON a.id = b.author_id

---

크로스 조인 솔루션 에 대해 어떻게 말했 습니까?

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 
ON 1=1;

---

또한 가능하지만 select에서 동일한 필드 이름을 언급해야합니다.

SELECT t1.name, t2.name FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT t1.name, t2.name FROM t2
LEFT JOIN t1 ON t1.id = t2.id

---

리스폰스를 수정 해 작품은 모든 행을 포함합니다 (Pavle Lekic의 리스폰스 기준)

    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    WHERE b.`key` is null
    )
    UNION ALL
    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    where  a.`key` = b.`key`
    )
    UNION ALL
    (
    SELECT b.* FROM tablea a
    right JOIN tableb b ON b.`key` = a.key
    WHERE a.`key` is null
    );

---

대답:

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

다음과 같이 다시 만들 수 있습니다.

 SELECT t1.*, t2.* 
 FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
 LEFT JOIN t1 ON t1.id = tmp.id
 LEFT JOIN t2 ON t2.id = tmp.id;

UNION 또는 UNION ALL 대답을 사용하면 기본 테이블에 중복 된 항목이있는 경우가 포함되지 않습니다.

설명:

UNION 또는 UNION ALL이 커버 할 수없는 엣지 케이스가 있습니다. FULL OUTER JOIN을 지원하지 않기 때문에 mysql에서 테스트 할 수 없지만 지원하는 데이터베이스에서는이를 설명 할 수 있습니다.

 WITH cte_t1 AS
 (
       SELECT 1 AS id1
       UNION ALL SELECT 2
       UNION ALL SELECT 5
       UNION ALL SELECT 6
       UNION ALL SELECT 6
 ),
cte_t2 AS
(
      SELECT 3 AS id2
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 6
)
SELECT  *  FROM  cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;

This gives us this answer:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

UNION 솔루션 :

SELECT  * FROM  cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
UNION    
SELECT  * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

오답을 제공합니다.

 id1  id2
NULL  3
NULL  4
1  NULL
2  NULL
5  5
6  6

UNION ALL 솔루션 :

SELECT  * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2
UNION ALL
SELECT  * FROM  cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

또한 부정확합니다.

id1  id2
1  NULL
2  NULL
5  5
6  6
6  6
6  6
6  6
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

이 쿼리는 다음과 같습니다.

SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp 
LEFT JOIN t1 ON t1.id = tmp.id 
LEFT JOIN t2 ON t2.id = tmp.id;

다음을 제공합니다.

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

순서는 다르지만 그렇지 않으면 정답과 일치합니다.

---

표준 SQL 말한다 full join on입니다 inner join on행 union all널 (null) 확장 타의 추종을 불허하는 왼쪽 테이블 행은 union all우측 테이블 행이 널 (null)로 연장했다. 즉 inner join on행의 union all행 left join on이 아닌 inner join on union all행 right join on아니지만 inner join on.

즉 left join on행의 union all right join on행 NOT IN inner join on. 당신이 알고있는 경우 또는 inner join on결과가 "다음 특정 권리 테이블 컬럼에 널 (null)을 가질 수 right join on없는 행을 inner join on"의 행입니다 right join on와 on연장 조건 and이 열은 is null.

즉, 유사하게 right join on union all적절한 left join on행.

에서 "내부 조인"과 "외부 조인"의 차이점은 무엇입니까? :

(SQL Standard 2006 SQL / Foundation 7.7 구문 규칙 1, 일반 규칙 1 b, 3 c & d, 5 b.)

참고 URL : https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql