对网络的快速搜索显示，模拟FULL OUTER JOIN("FOJ")的主题已在Stack Overflow和其他地方多次讨论过，但是答案似乎都仅限于只有两个表的情况.我怀疑是因为三个或更多桌子上的FOJ是

A quick search of the web revealed that the subject of simulating a FULL OUTER JOIN ("FOJ") has been discussed many times on Stack Overflow and elsewhere, but the answers all seem to limit themselves to the case with only two tables. I suspected it was because a FOJ on three or more tables was

1. 不是很常见的情况，
2. 可能非常混乱.

还是，我想我去试试，这就是我想出的:

Still, I thought I'd give it a go, and here's what I came up with:

在两个表上模拟FOJ时最常提到的技术是使所有三种可能的情况都实现UNION:

The technique most commonly mentioned for simulating a FOJ on two tables is to UNION ALL the three possible cases:

In_Table2  In_Table1
---------  ---------
false      true
true       false
true       true

或，用true = 1和false = 0

or, substituting true=1 and false=0

In_Table2  In_Table1
---------  ---------
0          1
1          0
1          1

它看起来像是一个带有两个可能的非零值的两位整数，提取这些值的SQL格式为

That looks like a two-bit integer with three possible non-zero values, and the SQL to extract those would be of the form

Table2 RIGHT JOIN Table1 WHERE Table2.something IS NULL
UNION ALL
Table2 LEFT JOIN Table1 WHERE Table1.something IS NULL
UNION ALL
Table2 INNER JOIN Table1

具有三个表的情况的状态表因此看起来像是具有七个可能的非零值的三位整数

The state table for the case with three tables would therefore look like a three-bit integer with seven possible non-zero values

In_Table3  In_Table2  In_Table1
---------  ---------  ---------
0          0          1
0          1          0
0          1          1
1          0          0
1          0          1
1          1          0
1          1          1

我想对一个整数值进行联接，所以我只为七个行中的每一行依次分配了它们

I wanted to do the join on an integer value, so I just assigned them sequentially for each of the seven rows

Table3  Table2  Table1
------  ------  ------
                1
        2       
        3       3
4               
5               5
6       6       
7       7       7

所以我的测试表是

[表1]

n  txt 
-  ----
1  t1_1
3  t1_3
5  t1_5
7  t1_7

[Table2]

n  txt 
-  ----
2  t2_2
3  t2_3
6  t2_6
7  t2_7

[Table3]

n  txt 
-  ----
4  t3_4
5  t3_5
6  t3_6
7  t3_7

我在Access中创建了一个名为[foj12]的保存查询，以在[Table1]和[Table2]之间执行FOJ

I created a saved query in Access named [foj12] to perform the FOJ between [Table1] and [Table2]

    SELECT t1.n AS t1_n, t1.txt AS t1_txt, t2.n AS t2_n, t2.txt AS t2_txt
    FROM
        Table1 t1
        INNER JOIN
        Table2 t2
            ON t1.n = t2.n
UNION ALL
    SELECT t1.n AS t1_n, t1.txt AS t1_txt, t2.n AS t2_n, t2.txt AS t2_txt
    FROM
        Table1 t1
        LEFT JOIN
        Table2 t2
            ON t1.n = t2.n
    WHERE t2.n IS NULL
UNION ALL 
    SELECT t1.n AS t1_n, t1.txt AS t1_txt, t2.n AS t2_n, t2.txt AS t2_txt
    FROM
        Table1 t1
        RIGHT JOIN
        Table2 t2
            ON t1.n = t2.n
    WHERE t1.n IS NULL

返回

t1_n  t1_txt  t2_n  t2_txt
----  ------  ----  ------
   1  t1_1                
                 2  t2_2  
   3  t1_3       3  t2_3  
   5  t1_5                
                 6  t2_6  
   7  t1_7       7  t2_7  

然后，我开始处理涉及[Table3]的案例.它们如下所示，并且对应于下面的SQL语句中的非联合"查询.

Then I started working through the cases involving [Table3]. They were as follows, and correspond to the "unioned" queries in the SQL statement below.

1. Table3与Table1和Table2都匹配(n = 7)
2. Table3与Table1匹配，但与Table2不匹配(n = 5)
3. Table3与Table2匹配，但与Table1不匹配(n = 6)
4. 表3中的行与表1或表2中的任何行都不匹配(n = 4)
5. FOJ(表1，表2)中的行与表3(n = 1,2，3)没有共同点

    SELECT f.t1_n, f.t1_txt, f.t2_n, f.t2_txt, t3.n AS t3_n, t3.txt AS t3_txt
    FROM
        Table3 t3
        INNER JOIN
        foj12 f
            ON t3.n = f.t1_n AND t3.n = f.t2_n
UNION ALL
    SELECT f.t1_n, f.t1_txt, f.t2_n, f.t2_txt, t3.n AS t3_n, t3.txt AS t3_txt
    FROM
        Table3 t3
        INNER JOIN
        foj12 f
            ON t3.n = f.t1_n
    WHERE f.t2_n IS NULL
UNION ALL
    SELECT f.t1_n, f.t1_txt, f.t2_n, f.t2_txt, t3.n AS t3_n, t3.txt AS t3_txt
    FROM
        Table3 t3
        INNER JOIN
        foj12 f
            ON t3.n = f.t2_n
    WHERE f.t1_n IS NULL
UNION ALL
    SELECT NULL, NULL, NULL, NULL, t3.n AS t3_n, t3.txt AS t3_txt
    FROM
        Table3 t3
    WHERE t3.n NOT IN (SELECT t1_n FROM foj12 WHERE t1_n IS NOT NULL)
        AND t3.n NOT IN (SELECT t2_n FROM foj12 WHERE t2_n IS NOT NULL)
UNION ALL
    SELECT f.t1_n, f.t1_txt, f.t2_n, f.t2_txt, NULL, NULL
    FROM foj12 f
    WHERE 
        (f.t1_n NOT IN (SELECT n FROM Table3) AND f.t2_n NOT IN (SELECT n FROM Table3))
        OR
        (f.t1_n NOT IN (SELECT n FROM Table3) AND f.t2_n IS NULL)
        OR
        (f.t1_n IS NULL AND f.t2_n NOT IN (SELECT n FROM Table3))
ORDER BY 5, 3, 1

那个小美女回来了

t1_n  t1_txt  t2_n  t2_txt  t3_n  t3_txt
----  ------  ----  ------  ----  ------
   1  t1_1                              
                 2  t2_2                
   3  t1_3       3  t2_3                
                               4  t3_4  
   5  t1_5                     5  t3_5  
                 6  t2_6       6  t3_6  
   7  t1_7       7  t2_7       7  t3_7  

(不用说我对添加第四个表不感兴趣！:)

(Needless to say I'm not interested in adding a fourth table! :)

欢迎评论.