blocks|key|1379273|text|可以，请参阅Wikipedia上的示例。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1379274|SELECT+employee.*,+department.*
FROM+++employee+
+++++++LEFT+JOIN+department+
++++++++++ON+employee.DepartmentID+=+department.DepartmentID
UNION+ALL
SELECT+employee.*,+department.*
FROM+++department
+++++++LEFT+JOIN+employee
++++++++++ON+employee.DepartmentID+=+department.DepartmentID
WHERE++employee.DepartmentID+IS+NULL|code-block|syntax|javascript|1379275|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Join_%2528SQL%2529#Full_outer_join^0|6|9|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Yes, see the example on <a href="http://en.wikipedia.org/wiki/Join_%28SQL%29#Full_outer_join" rel="noreferrer">Wikipedia</a>.

<pre><code>SELECT employee.*, department.*
FROM employee 
 LEFT JOIN department 
 ON employee.DepartmentID = department.DepartmentID
UNION ALL
SELECT employee.*, department.*
FROM department
 LEFT JOIN employee
 ON employee.DepartmentID = department.DepartmentID
WHERE employee.DepartmentID IS NULL
</code></pre>

blocks|key|1379300|text|我将迟来提出我的2美分。考虑下面两个简单的表people1和people2：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1379301|+++id+++name+age
0+++1++++teo++59
1+++2+++niko++57
2+++3++maria++54+


++++id+++name+weight
0+++1++++teo++++186
1+++2++maria++++125
2+++3++++evi++++108|code-block|syntax|javascript|1379302|首先，我们创建一个临时视图v_all，在这里我们使用UNION连接两个相对的左连接，如下所示：|1379303|CREATE+TEMP+VIEW+v_all+AS
++++++++++++++SELECT+p1.name+AS+name1,+p1.age,
++++++++++++++++++++p2.name+AS+name2,+p2.weight
++++++++++++++FROM+people1+p1
++++++++++++++LEFT+JOIN+people2+AS+p2+
++++++++++++++USING+(name)
++++++++++++++UNION
++++++++++++++SELECT+p1.name+AS+name1,+p1.age,
++++++++++++++++++p2.name+AS+name2,+p2.weight
++++++++++++++FROM+people2+AS+p2
++++++++++++++LEFT+JOIN+people1+AS+p1
++++++++++++++USING+(name);|1379304|但是，我们最终得到两个name列，name1和name2，它们可能具有空值或相等值。我们想要的是在单个列名中组合name1和name2。我们可以使用一个案例查询来实现这一点，如下所示：|1379305|SELECT+age,weight,
++++++++++++++++CASE
++++++++++++++++++WHEN+name1+IS+NULL
++++++++++++++++++++THEN+name2
++++++++++++++++++WHEN+name2+IS+NULL
++++++++++++++++++++THEN+name1
++++++++++++++++++WHEN+name1=name2
++++++++++++++++++++THEN+name1
++++++++++++++++END+name
++++++++++++++FROM+v_all|1379306|我们最终会得到：|1379307|++++++name+weight+++age
0++++evi++++108++None
1++maria++++125++++54
2+++niko+++None++++57
3++++teo++++186++++59|1379308|当然，您可以在一个查询中组合这两个查询，而不必创建临时视图。我避免这样做，是为了强调只有2个左连接和一个并集的不足，这是我到目前为止所看到的建议。|1379309|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

I will belatedly pitch in my 2 cents. Consider the 2 simple tables people1 and people2 below:
<pre><code> id name age
0 1 teo 59
1 2 niko 57
2 3 maria 54 


 id name weight
0 1 teo 186
1 2 maria 125
2 3 evi 108
</code></pre>
First, we create a temporaty view, v_all, where we join with UNION the two opposite LEFT JOINS as below:
<pre><code>CREATE TEMP VIEW v_all AS
 SELECT p1.name AS name1, p1.age,
 p2.name AS name2, p2.weight
 FROM people1 p1
 LEFT JOIN people2 AS p2 
 USING (name)
 UNION
 SELECT p1.name AS name1, p1.age,
 p2.name AS name2, p2.weight
 FROM people2 AS p2
 LEFT JOIN people1 AS p1
 USING (name);
</code></pre>
However, we end up with 2 name columns,name1 and name2, which may have a null value or equal values. What we want is to combine name1 and name2 in a single column name. We can do that with a CASE query as below:
<pre><code>SELECT age,weight,
 CASE
 WHEN name1 IS NULL
 THEN name2
 WHEN name2 IS NULL
 THEN name1
 WHEN name1=name2
 THEN name1
 END name
 FROM v_all
</code></pre>
And we finally end up with:
<pre><code> name weight age
0 evi 108 None
1 maria 125 54
2 niko None 57
3 teo 186 59
</code></pre>
Of course you could combine the two in a single query, without having to create a temp view. I avoided doing so, in order to highlight the insufficiency of just 2 left joins and a union, which is what i have seen so far recommended.

SQLite only has INNER and LEFT JOIN.

Is there a way to do a FULL OUTER JOIN with SQLite?

FULL OUTER JOIN with SQLite

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

SQLite只有INNER和LEFT JOIN。有没有一种方法可以用SQLite进行完全的外连接？

问与SQLite的完全外部联接
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问与SQLite的完全外部联接EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问与SQLite的完全外部联接
EN