ms-access - 使用 Access 在两个以上的表上模拟 FULL OUTER JOIN

Question

我经历了惨痛的教训，Access 不允许您执行完整的外部联接，并且在阅读如何模拟外部联接时，我已经开始了解如何做到这一点，但在尝试将其应用于多个领域时遇到问题只有两张 table 。

是否像执行以下操作一样简单？

SELECT * FROM table1
LEFT JOIN table2 ON table1.field = table2.field
LEFT JOIN table3 ON table1.field = table3.field
UNION
SELECT * FROM table1
RIGHT JOIN table2 ON table1.field = table2.field
RIGHT JOIN table3 ON table1.field = table3.field

Answer 1

快速搜索网络发现，模拟 FULL OUTER JOIN(“FOJ”)的主题已在 Stack Overflow 和其他地方多次讨论，但答案似乎都仅限于只有两个表的情况。我怀疑这是因为三张或更多 table 上的 FOJ

1. 这种情况并不常见，并且
2. 可能相当困惑。

尽管如此，我还是想尝试一下，这就是我的想法:

在两个表上模拟 FOJ 最常提到的技术是将所有三种可能的情况合并:

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

或者，替换 true=1 和 false=0

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

这看起来像一个具有三个可能的非零值的两位整数，提取这些值的 SQL 的形式为

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

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

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

我想对一个整数值进行连接，所以我只是为七行中的每一行按顺序分配它们

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

[表2]

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

[表3]

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

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

    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  

然后我开始处理涉及[表3]的案例。它们如下，对应于下面 SQL 语句中的“联合”查询。

1. 表 3 与表 1 和表 2 均匹配 (n=7)
2. 表 3 与表 1 匹配，但与表 2 不匹配 (n=5)
3. 表 3 与表 2 匹配，但与表 1 不匹配 (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  

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

欢迎评论。